9e0bde87-793b-45f1-9a95-26d4f96c12ec.xls Chapter 8: Applying Excel Data Budgeted unit sales • Selling price per unit • Accounts receivable, beginning balance • • • Sales collected in the quarter sales are made Sales collected in the quarter after sales are made • Desired ending finished goods inventory is . • Finished goods inventory, beginning . • • • Raw materials required to produce one unit • Desired ending inventory of raw materials is Raw materials inventory, beginning Raw material costs • Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance Year 2 Quarter 1 2 3 4 40,000 60,000 100,000 50,000 $8 per unit $65,000 75% 25% 30% of the budgeted unit sales of the next qua 12,000 units 5 pounds 10% of the next quarter's production needs 23,000 pounds $0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules Construct the sales budget Budgeted unit sales Selling price per unit Total sales Construct the schedule of expected cash collections Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Year 2 Quarter 1 2 3 4 ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 1 2 3 4 ? ? ? ? ? ? ? ? ? ? Fourth-quarter sales Total cash collections ? ? Construct the production budget Year 2 Quarter 1 2 3 4 Budgeted unit sales ? ? ? ? Add desired finished goods inventory ? ? ? ? Total needs ? ? ? ? Less beginning inventory ? ? ? Required production ? ? ? 22 ? ? Construct the raw materials purchases budget Year 2 Quarter 1 2 3 4 Required production (units) ? Raw materials required to produce one unit Production needs (pounds) ? 222. ? ? ? ? ? ? ? ? ? ? Add desired ending inventory of raw materials (pounds) Total needs (pounds) ? ? ? ? ? ? ? ? Less beginning inventory of raw materials (pounds) ? ? ? ? Raw materials to be purchased ? ? ? ? Cost of raw materials per pound ? ? ? ? Cost of raw materials to be purchased Page 1 ? ? ? ?
9e0bde87-793b-45f1-9a95-26d4f96c12ec.xls Chapter 8: Applying Excel Data Budgeted unit sales • Selling price per unit • Accounts receivable, beginning balance • • • Sales collected in the quarter sales are made Sales collected in the quarter after sales are made • Desired ending finished goods inventory is . • Finished goods inventory, beginning . • • • Raw materials required to produce one unit • Desired ending inventory of raw materials is Raw materials inventory, beginning Raw material costs • Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance Year 2 Quarter 1 2 3 4 40,000 60,000 100,000 50,000 $8 per unit $65,000 75% 25% 30% of the budgeted unit sales of the next qua 12,000 units 5 pounds 10% of the next quarter's production needs 23,000 pounds $0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules Construct the sales budget Budgeted unit sales Selling price per unit Total sales Construct the schedule of expected cash collections Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Year 2 Quarter 1 2 3 4 ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 1 2 3 4 ? ? ? ? ? ? ? ? ? ? Fourth-quarter sales Total cash collections ? ? Construct the production budget Year 2 Quarter 1 2 3 4 Budgeted unit sales ? ? ? ? Add desired finished goods inventory ? ? ? ? Total needs ? ? ? ? Less beginning inventory ? ? ? Required production ? ? ? 22 ? ? Construct the raw materials purchases budget Year 2 Quarter 1 2 3 4 Required production (units) ? Raw materials required to produce one unit Production needs (pounds) ? 222. ? ? ? ? ? ? ? ? ? ? Add desired ending inventory of raw materials (pounds) Total needs (pounds) ? ? ? ? ? ? ? ? Less beginning inventory of raw materials (pounds) ? ? ? ? Raw materials to be purchased ? ? ? ? Cost of raw materials per pound ? ? ? ? Cost of raw materials to be purchased Page 1 ? ? ? ?
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
Related questions
Question
I do not understand my excel assignment.
I am supposed to check the worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. The required production for the year should be 274,000 units. The cost of raw materials to be purchased for the year should be $1,106,800, whereas the total cash disbursements for the year should be $1,095,980.
Transcribed Image Text:9e0bde87-793b-45f1-9a95-26d4f96c12ec.xls
Chapter 8: Applying Excel
Data
Budgeted unit sales
• Selling price per unit
•
Accounts receivable, beginning balance
•
•
• Sales collected in the quarter sales are made
Sales collected in the quarter after sales are made
•
Desired ending finished goods inventory is
.
• Finished goods inventory, beginning
.
•
•
•
Raw materials required to produce one unit
• Desired ending inventory of raw materials is
Raw materials inventory, beginning
Raw material costs
• Raw materials purchases are paid
and
•
Accounts payable for raw materials, beginning balance
Year 2 Quarter
1
2
3
4
40,000
60,000
100,000
50,000
$8 per unit
$65,000
75%
25%
30% of the budgeted unit sales of the next qua
12,000 units
5 pounds
10% of the next quarter's production needs
23,000 pounds
$0.80 per pound
60% in the quarter the purchases are made
40% in the quarter following purchase
$81,500
Enter a formula into each of the cells marked with a ? below
Review Problem: Budget Schedules
Construct the sales budget
Budgeted unit sales
Selling price per unit
Total sales
Construct the schedule of expected cash collections
Accounts receivable, beginning balance
First-quarter sales
Second-quarter sales
Third-quarter sales
Year 2 Quarter
1
2
3
4
?
?
?
?
?
?
?
?
?
?
?
?
Year 2 Quarter
1
2
3
4
?
?
?
?
?
?
?
?
?
?
Fourth-quarter sales
Total cash collections
?
?
Construct the production budget
Year 2 Quarter
1
2
3
4
Budgeted unit sales
?
?
?
?
Add desired finished goods inventory
?
?
?
?
Total needs
?
?
?
?
Less beginning inventory
?
?
?
Required production
?
?
?
22
?
?
Construct the raw materials purchases budget
Year 2 Quarter
1
2
3
4
Required production (units)
?
Raw materials required to produce one unit
Production needs (pounds)
?
222.
?
?
?
?
?
?
?
?
?
?
Add desired ending inventory of raw materials (pounds)
Total needs (pounds)
?
?
?
?
?
?
?
?
Less beginning inventory of raw materials (pounds)
?
?
?
?
Raw materials to be purchased
?
?
?
?
Cost of raw materials per pound
?
?
?
?
Cost of raw materials to be purchased
Page 1
?
?
?
?
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Recommended textbooks for you
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education