Question

B D E F G 1 40,000 Year 2 Quarter 2 3 60,000 100,000 4 50,000 Year 3 Quarter 1 2 70,000 80,000 A 2 Master Budget: Excel Sheet 2 3 Data 4 5 Budgeted unit sales 6 7 . Selling price per unit 8 • Accounts receivable, beginning balance 9 • Sales collected in the quarter sales are made 10 - Sales collected in the quarter after sales are made 11 • Desired ending finished goods inventory is 12 . Finished goods inventory beginning 13 - Raw materials required to produce one unit 14. Desired ending inventory of raw materials is 15 - Raw materials inventory beginning 16 - Raw material costs 17 • Raw materials purchases are paid 18 and 19 . Accounts payable for raw materials, beginning balance 20 21 Enfer a formula into each of the cells marked with a 7 below $8 $65,000 75% 25% 30% of the budgeted unit sales of the next quarter 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

Answer 1

1. Expected Accounts Receivables a the end of the quarter -

Year 2 Quarter Year 3 1 2 3 4 1 $ 80,000 $120,000 $200,000 $100,000 $140,000 Expected Accounts Receivables a the end of the quarter

2. Construct the Production Budget -

Construct the Production Budget 1 4 Budgeted Unit Sales Desired Ending Finished Goods Inventory Total Needs Beginning Finished Goods Inventory Required Production in Units 40,000 18,000 58,000 12,000 46,000 Year 2 Quarter 2 3 60,000 100,000 30,000 15,000 90,000 115,000 18,000 30,000 72,000 85,000 50,000 21,000 71,000 15,000 56,000 Year 3 Quarter 1 70,000 24,000 94,000 21,000 73,000

3. Constuct the raw materials purchase budget -

Year 3 Quarter 1 73,000 5 365,000 Constuct the raw materials purchase budget Required Production (Unit) Raw Material Required to produce one unit Production Needs Desired Ending Inventory of Raw Material Total Needs Beginning Inventory of Raw Materials (Pounds) Raw Material to be purchased (Pounds) Cost of Raw Materials per Pound Cost of Raw Materials to be purchased Year 2 Quarter 1 2 3 4 46,000 72,000 85,000 56,000 5 5 5 5 230,000 360,000 425,000 280,000 36,000 42,500 28,000 36,500 266,000 402,500 453,000 316,500 23,000 36,000 42,500 28,000 243,000 366,500 410,500 288,500 $ 0.80 $ 0.80 $ 0.80 $ 0.80 $ 194,400 $ 293,200 $328,400 $ 230,800 36,500

4. Construct the Schedule of Expected Cash Payment -

Construct the Schedule of Expected Cash Payment Beginning Balance Accounts Payable First Quarter Purchase Second Quarter Purchase Third Quarter Purchase Fourth Quarter Purchase Year 2 Quarter Year 3 Quarter 1 2 3 4 1 $ 81,500 $116,640 $ 77,760 $175,920 $ 117,280 $ 197,040 $ 131,360 $ 138,480 $ 92,320 Total Cash Disbursement $198,140 $ 253,680 $314,320 $ 269,840 $ 92,320

5. Expected Accounts Payables a the end of the quarter -

Year 2 Quarter 1 2 3 4 $ 77,760 $117,280 $ 131,360 $ 92,320 Expected Accounts Payables a the end of the quarter

6. Working Notes -

Working Notes Year 2 Quarter 1 2 3 Year 3 Quarter 1 2 4 Budgeted Unit Sales Selling Price per Unit Total Sales 40,000 60,000 100,000 50,000 70,000 80,000 $ 8 $ 8 $ 8 $ 8 $ 8 $ 8 $ 320,000 $ 480,000 $ 800,000 $ 400,000 $ 560,000 $ 640,000 Credit Collection: In the quarter of sale In the next quarter 75% 25% $ 240,000 $360,000 $ 600,000 $ 300,000 $ 420,000 $ 480,000 $ 65,000 $80,000 $ 120,000 $ 200,000 $ 100,000 $ 140,000 Ending Finished Goods 30% 18,000 30,000 15,000 21,000 24,000 Beginning Finished Goods 12,000 18,000 30,000 15,000 21,000 24,000 Production 46,000 72,000 85,000 56,000 73,000 Required Raw Material 5 230,000 360,000 425,000 280,000 365,000 Ending Raw Material Required Begninng Raw Material 36,000 23,000 42,500 36,000 28,000 42,500 36,500 28,000 36,500 Raw Material Purchase Raw Material Cost per Unit 243,000 366,500 410,500 288,500 $ 0.80 $ 0.80 $ 0.80 $ 0.80 Cost of Raw Material to be purchased $ 194,400 $ 293,200 $ 328,400 $ 230,800 Credit Payment: In the same quarter In the quarter following purchase 60% 40% $ 116,640 $ 175,920 $ 197,040 $ 138,480 $ 81,500 $ 77,760 $ 117,280 $ 131,360 $ 92,320