Chapter 8 Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted Unit Sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 Accounts receivable, beginning balance $65,000 Sales collected in the quarter sales are made 75% Sales collected in the quarter after sales are made 25% Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter Finished goods inventory, beginning 12,000 units Raw materials required to produce one unit 5 pounds Desired ending inventory of raw materials is 10% of the next quarter's production needs Raw materials inventory, beginning 23,000 pounds Raw materials cost $0.80 per pound Raw materials purchases are paid 60% in the quarter the purchases are made and 40% in the quarter following purchase Accounts payable for raw materials, beginning balance $81,500 Enter the formula into each of the cells marked with a ? below. Review Problem: Budget Schedules Construct the sales budget Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales Saling price per unit Total sales Construct the schedule of expected cash collections Year 2 Quarter 1 2 3 4 Year Beginning balance accounts receivable First-quarter sales ? ? ? Second-quarter sales ? ? ? Third-quarter sales ? ? ? Fourth-quarter sales ? ? Total cash collections ? ? ? ? ? Construct the production budget Year 2 Quarter Year 3 Quarter 1 2 3 4 Year 1 2 Budgeted unit sales ? ? ? ? ? ? ? Add desired ending finished goods inventory ? ? ? ? ? ? Total needs ? ? ? ? ? ? Less beginning finished goods inventory ? ? ? ? ? ? Required production in units ? ? ? ? ? ?

Chapter 8 Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted Unit Sales 40,000 60,000 100,000 50,000 70,000 80,000 Selling price per unit $8 Accounts receivable, beginning balance $65,000 Sales collected in the quarter sales are made 75% Sales collected in the quarter after sales are made 25% Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter Finished goods inventory, beginning 12,000 units Raw materials required to produce one unit 5 pounds Desired ending inventory of raw materials is 10% of the next quarter's production needs Raw materials inventory, beginning 23,000 pounds Raw materials cost $0.80 per pound Raw materials purchases are paid 60% in the quarter the purchases are made and 40% in the quarter following purchase Accounts payable for raw materials, beginning balance $81,500 Enter the formula into each of the cells marked with a ? below. Review Problem: Budget Schedules Construct the sales budget Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales Saling price per unit Total sales Construct the schedule of expected cash collections Year 2 Quarter 1 2 3 4 Year Beginning balance accounts receivable First-quarter sales ? ? ? Second-quarter sales ? ? ? Third-quarter sales ? ? ? Fourth-quarter sales ? ? Total cash collections ? ? ? ? ? Construct the production budget Year 2 Quarter Year 3 Quarter 1 2 3 4 Year 1 2 Budgeted unit sales ? ? ? ? ? ? ? Add desired ending finished goods inventory ? ? ? ? ? ? Total needs ? ? ? ? ? ? Less beginning finished goods inventory ? ? ? ? ? ? Required production in units ? ? ? ? ? ?

Financial & Managerial Accounting

14th Edition

ISBN:9781337119207

Author:Carl Warren, James M. Reeve, Jonathan Duchac

Publisher:Carl Warren, James M. Reeve, Jonathan Duchac

Chapter21: Budgeting

Section: Chapter Questions

Problem 21.1BPR: Forecast sales volume and sales budget Sentinel systems Inc. prepared the following sales budget for...

See similar textbooks

Related questions

Concept explainers

Question

Chapter 8 Applying Excel

Data	Year 2 Quarter	Year 3 Quarter
	1	2	3	4	1	2
Budgeted Unit Sales	40,000	60,000	100,000	50,000	70,000	80,000

Selling price per unit	$8
Accounts receivable, beginning balance	$65,000
Sales collected in the quarter sales are made	75%
Sales collected in the quarter after sales are made	25%
Desired ending finished goods inventory is	30%	of the budgeted unit sales of the next quarter
Finished goods inventory, beginning	12,000	units
Raw materials required to produce one unit	5	pounds
Desired ending inventory of raw materials is	10%	of the next quarter's production needs
Raw materials inventory, beginning	23,000	pounds
Raw materials cost	$0.80	per pound
Raw materials purchases are paid	60%	in the quarter the purchases are made
and	40%	in the quarter following purchase
Accounts payable for raw materials, beginning balance	$81,500

Enter the formula into each of the cells marked with a ? below.
Review Problem: Budget Schedules

Construct the sales budget	Year 2 Quarter	Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales
Saling price per unit
Total sales

Construct the schedule of expected cash collections	Year 2 Quarter
	1	2	3	4	Year
Beginning balance accounts receivable
First-quarter sales	?	?			?
Second-quarter sales		?	?		?
Third-quarter sales			?	?	?
Fourth-quarter sales				?	?
Total cash collections	?	?	?	?	?

Construct the production budget	Year 2 Quarter	Year 3 Quarter
	1	2	3	4	Year	1	2
Budgeted unit sales	?	?	?	?	?	?	?
Add desired ending finished goods inventory	?	?	?	?	?	?
Total needs	?	?	?	?	?	?
Less beginning finished goods inventory	?	?	?	?	?	?
Required production in units	?	?	?	?	?	?

Construct the raw materials purchases budget	Year 2 Quarter	Year 3 Quarter
	1	2	3	4	Year	1
Required production (units)	?	?	?	?	?	?
Raw materials required to produce one unit (pounds)	?	?	?	?	?	?
Production needs (pounds)	?	?	?	?	?	?
Add desired units of ending raw materials inventory (pounds)	?	?	?	?	?
Total needs (pounds)	?	?	?	?	?
Less beginning inventory of raw materials (pounds)	?	?	?	?	?
Raw materials to be produced (pounds)	?	?	?	?	?
Cost of raw materials per pound	?	?	?	?	?
Cost of raw materials to be purchased	?	?	?	?	?

Construct the schedule of expected cash payments	Year 2 Quarter
	1	2	3	4	Year
Beginning balance accounts payable	?				?
First-quarter purchases	?	?			?
Second-quarter purchases		?	?		?
Third-quarter purchases			?	?	?
Fourth-quarter purchases				?	?
Total cash disbursements	?	?	?	?	?

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 2 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.