Question

The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget:

	Year 2 Quarter					Year 3 Quarter
Data	1	2	3	4		1	2
Budgeted unit sales	45,000	65,000	105,000	60,000		90,000	100,000
Selling price per unit	$7

8 9 10 11 12 13 14 15 16 17 18 19 Chapter 8: Applying Excel Data Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 45,000 65,000 105,000 60,000 90,000 100,000 • Selling price per unit $7 per unit • 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 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 • Accounts payable for raw materials, beginning balance $81,500

1. What are the total expected cash collections for the year under this revised budget?

2. What is the total required production for the year under this revised budget?

3. What is the total cost of raw materials to be purchased for the year under this revised budget?

4. What are the total expected cash disbursements for raw materials for the year under this revised budget?

Answer 1

please find below table useful to compute desired results: -

A в с Q1 45000 102 Q2 65000 Year 285000 G Q1Yr-2 90000 H Q2Yr-2 100000 1 SALES BUDGET 2 Budgeted Sales units 3 Selling price per unit 4 Total Sales Q3 04 105000 60000 77 =D2 D3 =E2 E3 =B2 B3 =C2*C3 D2"D3 =F2*F3 =G2*G3 =H2*H3 5 102 03 Q2Yr-2 =H2 6 EXPECTED CASH COLLECTIONS 01 Q3 104 Year 7 Accounts receivable beginning 65000 =B7+C7+D7+E7 8 Quarter-1 Sales =B4*0.75 =B4*0.25 =B8+C8+D8+E8 9 Quarter-2 Sales =C4*0.75 =C4*0.25 =B9+C9+D9+E9 10 Quarter-3 sales =D4*0.75 =D4*0.25 =B10+C10+D10+E10 11 Quarter-4 sales =E4*0.75 =B11+C11+D11+E11 12 Total Cash Collections =SUMB7-B11) =SUM(C7:C11) =SUMD7-D11) =SUM(E7-E11) =SUM(F7-F11) 13 14 PRODUCTION BUDGET Q1 Q2 04 Year 15 Budgeted Sales Units =B2 =C2 =D2 =E2 =F2 16 Add: Desired Ending Finished inventory =C15*0.3 =D15*0.3 =E15*0.3 =F16 G15*0.3 17 Total Needs =B15+B16 =C15+C16 =D15+D16 =E15+E16 =F15+F16 18 Less: Beginning Finished Inventory 12000 =B16 =C16 =D16 =B18 19 Required Production in units =B17-B18 =C17-C18 =D17-D18 =E17-E18 =F17-F18 20 21 RAW MATERIAL PURCHASE BUDGET Q1 Q2 Year 22 Budgeted Production units =B19 =C19 =D19 =F19 23 Raw material required per unit 24 Total raw material needs =B22*B23 =C22*C23 =D22*D23 =E22*E23 =F22*F23 25 Add: Desired Ending Inventory =C24*0.1 =D24*0.1 =E24*0.1 =G24*0.1 =E25 26 Total needs =B24+B25 =C24+C25 =D24+D25 =E24+E25 =F24+F25 27 Less: Beginning Inventory 23000 =B25 =C25 =D25 =B27 28 Purchase Units =B26-B27 =C26-C27 =D26-D27 =E26-E27 =F26-F27 Q1Yr-2 =G2 =H15*0.3 =G15+G16 =F16 =G17-G18 03 04 Q1Yr-2 =G19 =E19 =G22*G23

B Ε C 0.8 =C28*C29 08 =B28*B29 D 0.8 =D28*D29 10.8 0.8 =F28*F29 =E28 E29 02 А 29 Cost price per unit 30 Budgeted Purchase in S 31 32 EXPECTED CASH PAYMENTS 33 Beginning Accounts payable 34 Quarter-1 Purchase 35 Quarter-2 purchases 36 Quarter-3 purchase 37 Quarter-4 purchase 38 Total Cash disbursement Q1 Q3 Year 81500 =B33+C33+D33+E33 =B30*0.6 =B30*0.4 =B34+C34+D34+E34 =C30*0.6 =C30*0.4 =B35+C35+D35+E35 =D30*0.6 =D30*0.4 =B36+C36+D36+E36 =E30*0.6 =B37+C37+D37+E37 =SUM(B33:B37) =SUM(C33:C37) =SUM(D33-D37) =SUM(E33:E37) =SUM(F33:F37)

Ending results would appear as follows: -

1 A B C D E F G 1 SALES BUDGET Q1 Q2 Q3 Q4 Year Q1Yr-2 Q2Yr-2 2 Budgeted Sales units 45,000 65,000 105,000 60.000 285,000 90.000 100.000 3 Selling price per unit 70 71 72 71 72 4 Total Sales 315,000 455,000 735,000 420,000 1.995,000 630,000 700,000 5 6 EXPECTED CASH COLLECTIONS Q1 Q2 Q3 Q4 Year 7 Accounts receivable beginning 65.000 65,000 8 Quarter-1 Sales 236,250 78750 315.000 9 Quarter-2 Sales 341250 113750 455,000 10 Quarter-3 sales 551,250 183,750 735,000 11 Quarter-4 sales 315,000 315,000 12 Total Cash Collections 301.250 420,000 665,000 498.750 1.885.000 13 14 PRODUCTION BUDGET Q1 Q2 Q3 Q4 Year Q1Yr-2 Q2Yr-2 15 Budgeted Sales Units 45,000 65,000 105,000 60.000 285.000 90.000 100.000 16 Add: Desired Ending Finished inventory 19,500 31,500 18.000 27.000 27.000 30.000 17 Total Needs 64,500 96,500 123,000 87.000 312.000 120,000 18 Less: Beginning Finished Inventory 12,000 19,500 31,500 18.000 12.000 27.000 19 Required Production in units 52,500 77.000 91,500 69.000 300.000 93.000 20 21 RAW MATERIAL PURCHASE BUDGET Q1 Q2 Q3 Q4 Year Q1Yr-2 22 Budgeted Production units 52,500 77,000 91,500 69.000 300.000 93,000 23 Raw material required per unit 24 Total raw material needs 262,500 385,000| 457,500 345,000 1,500,000| 465,000 25 Add: Desired Ending Inventory 38,500 45,750 34,500 46,500 46,500 26 Total needs 301.000 430.750 492,000 391,500 1,546,500 27 Less: Beginning Inventory 23,000 38.500 45.750 34,500 23,000 28 Purchase Units 278,000 392,250 446.250 357,000 1,523,500 51

G B C D E F 0.8 0.8 0.8 0.8 0.8 222,400313,800 357.000 285,600 1,218,800|| А 29 Cost price per unit 30 Budgeted Purchase in $ 31 32 EXPECTED CASH PAYMENTS 33 Beginning Accounts payable 34 Quarter-1 Purchase 35 Quarter-2 purchases 36 Quarter-3 purchase 37 Quarter-4 purchase 38 Total Cash disbursement Q1 Q2 Q3 Q4 Year 81,500 81,500 1334401 88960 222.400 188280 125520 313.800 214200 142,800 357,000 171,360 171,360 214,940 277.240 339.720 314.160 1.146,060