On the Budgeting Assignment worksheet columns A through G contain budget assumptions, ar columns I through P contain budget tables that you are to fill in. Fill in each cell highlighted in yellow with a formula only. For example, formulas should contain cell references to the budget assumptions or an arithmetic operation such as a sum or multiplication. There should be no manually entered amounts in any cell in columns through P A D F G ASSUMPTIONS AREA Q1 2021 Q2 2021 Q3 2021 Q4 2021 Q1 2022 02 2022 4.500 6,500 9,800 5.250 7,100 8,000 $ 49.00 $ 49,00 5 51.00 $ 51.00 $ 53.00 $ 53 00 $ 40,000 80% 20% 1,800 40% 3 SALES 4 5 Sales units 6 Selling price 7 8 ACCOUNTS RECEIVABLE 9 Beginning accounts receivable balance 20 Sales cash collection, quarter of sale 71 Sales cash collection, quarter after sale 12 13 FINISHED GOODS INVENTORY 14 Beginning finished goods inventory units 15 Desired ending inventory % of next quarter sales units 16 17 RAW MATERIALS INVENTORY 18. Beginning raw materials inventory in pounds 19 Pounds required to produce each unit 20 Desired eding inventory of next quarter production needs 21 Cost of raw material per pound 2 23 ACCOUNTS PAYABLE 24 Beginning accounts payable balance 25 Raw materials cash payment quarter of purchase 26 Raw materials cash payment quarter after purchase 27 28 8864 5.5 20% 2.60 $ $ 28.500 70% 30% M N O BUDGET AREA Sales Budget Q1 2021 02 2021 Q3.2021 04.2021 Q1 2022 02 2022 Unit sales Selling price per unit Total sales 01.2021 02.2021 03 2021 042021 Year Schedule of Expected Cash Collections Accounts receivable beginning balance First quarter sales Second-quarter sales Third quarter sales Fourth quarter sales Total cash collections 012021 102 2021 03 2021 04.2021 Year Q1 2022 02.2022 Production Budget Budgeted unit sales Add desired finished goods inventory Total needs Less beginning finished goods inventory Required production (units) Raw Materials Purchases Budget Q1.2021 02120211 0312021 042021 Year 01 2022 Required production (units) Raw materials required to produce one unit Production needs (pounds) Add desired ending inventory of raw materials (pounds) Total needs (pounds) Less beginning inventory of raw materials (pounds) Raw matenals to be purchased Cost of raw materials per pound Cost of raw materials to be purchased Schedule of Expected Cash Payments 011 2021 22 2021 0312024 Year 022021 Accounts payable beginning balance First quarter purchase Second-quarter purchases Third quarter purchases Fourth quarter purchases Total cash disbursements
