Chapter_9_Applying_Excel.xls
Chapter 9: Applying Excel
Data
1 2 3 4 1
Budgeted unit sales 40,000 60,000 100,000 50,000 70,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 inventory of raw materials is 10% of the next quarter’s production needs
• Raw materials inventory, beginning 23,000 pounds
• Raw material costs
$0.80 per pound
• Raw materials purchases are paid 60% in the quarter the purchases are made
and 40% in the quarter following purchase
Enter a formula into each of the cells marked with a ? below
Review Problem: Budget Schedules
Construct the sales budget
1 2 3 4 1
Budgeted unit sales ? ? ? ? ?
Selling price per unit ? ? ? ? ?
Total sales ? ? ? ? ?
Construct the schedule of expected cash collections
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
1 2 3 4 Year
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
1 2 3 4 Year
Required production (units) ? ? ? ? ?
Raw materials required to produce one unit (pounds) ? ? ? ? ?
Production needs (pounds) ? ? ? ? ?
Total needs (pounds) ? ? ? ? ?
Less beginning inventory of raw materials (pounds) ? ? ? ? ?
Raw materials to be purchased (pounds) ? ? ? ? ?
Cost of raw materials per pound ? ? ? ? ?
Cost of raw materials to be purchased ? ? ? ? ?
Year 2 Quarter
Year 2 Quarter
Year 3
Year 2 Quarter
Year 3
Year 2 Quarter
Year 2 Quarter
Page 1
Page 3
Chapter_9_Applying_Excel.xls
Chapter 9: Applying Excel
Data
1 2 3 4 1
Budgeted unit sales 40,000 60,000 100,000 50,000 70,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 inventory of raw materials is 10% of the next quarter’s production needs
• Raw materials inventory, beginning 23,000 pounds
• Raw material costs
$0.80 per pound
• Raw materials purchases are paid 60% in the quarter the purchases are made
and 40% in the quarter following purchase
Enter a formula into each of the cells marked with a ? below
Review Problem: Budget Schedules
Construct the sales budget
1 2 3 4 1
Budgeted unit sales 40,000 60,000 100,000 50,000 70,000
Selling price per unit $8 $8 $8 $8 $8
Total sales $320,000 $480,000 $800,000 $400,000 $560,000
Construct the schedule of expected cash collections
1 2 3 4 Year
Beginning balance accounts receivable 65,000$ 65,000$
First-quarter sales 240,000 80,000$ 320,000
Second-quarter sales 360,000 120,000$ 480,000
Third-quarter sales 600,000 200,000$ 800,000
Fourth-quarter sales 300,000 300,000
Total cash collections 305,000$ 440,000$ 720,000$ 500,000$ 1,965,000$
Construct the production budget
1 2 3 4 Year
Budgeted unit sales 40,000 60,000 100,000 50,000 250,000
Add desired ending finished goods inventory 18,000 30,000 15,000 21,000 21,000
Total needs 58,000 90,000 115,000 71,000 271,000
Less beginning finished goods inventory 12,000 18,000 30,000 15,000 12,000
Required production in units 46,000 72,000 85,000 56,000 259,000
Construct the raw materials purchases budget
1 2 3 4 Year
Required production (units) 46,000 72,000 85,000 56,000 259,000
Raw materials required to produce one unit (pounds) 5 5 5 5 5
Production needs (pounds) 230,000 360,000 425,000 280,000 1,295,000
Total needs (pounds) 266,000 402,500 453,000 316,500 1,331,500
Less beginning inventory of raw materials (pounds) 23,000 36,000 42,500 28,000 23,000
Raw materials to be purchased (pounds) 243,000 366,500 410,500 288,500 1,308,500
Cost of raw materials per pound $0.80 $0.80 $0.80 $0.80 $0.80
Cost of raw materials to be purchased $194,400 $293,200 $328,400 $230,800 $1,046,800
Year 2 Quarter
Year 2 Quarter
Year 3
Year 2 Quarter
Year 3
Year 2 Quarter
Year 2 Quarter
Page 4
Chapter_9_Applying_Excel.xls
2
80,000
2
80,000
$8
$640,000
1 2
70,000 80,000
24,000
94,000
21,000
73,000
Year 3 Quarter
1
73,000
5
365,000
Quarter
Quarter
Year 3 Quarter
Page 6
Chapter_9_Applying_Excel.xls
1 2 3 4
Beginning balance accounts payable =B19
First-quarter purchases =B57*B17 =B57*B18
Second-quarter purchases =C57*B17 =C57*B18
Third-quarter purchases =D57*B17 =D57*B18
Fourth-quarter purchases =E57*B17
Total cash disbursements =SUM(B61:B65) =SUM(C61:C65) =SUM(D61:D65) =SUM(E61:E65)
Year 2 Quarter
Chapter_9_Applying_Excel.xls
1 2
70000 80000
1 2
=F5 =G5
=$B$7 =$B$7
=F26*F27 =G26*G27
Year
=SUM(B32:E32)
=SUM(B33:E33)
=SUM(B34:E34)
=SUM(B35:E35)
=SUM(B36:E36)
=SUM(B37:E37)
Year 1 2
=SUM(B41:E41) =F5 =G5
=E42 =H41*$B$11
=F41+F42 =G41+G42
=B44 =E42
=F43-F44 =G43-G44
Year 3 Quarter
Year 1
=F45 =G45
=$B$13 =$B$13
=F49*F50 =G49*G50
=E52
=F51+F52
=B54
=F53-F54
=$B$16
=F55*F56
Year 3 Quarter
Year 3 Quarter
Year 3 Quarter
Page 9
Chapter_9_Applying_Excel.xls
Year
=SUM(B61:E61)
=SUM(B62:E62)
=SUM(B63:E63)
=SUM(B64:E64)
=SUM(B65:E65)
=SUM(F61:F65)