Figure 9.4 Production Budget for Jerry's Ice Cream Jerry's Ice Cream Production Budget Year Ending December 31 Quarter 2 3 4 Year Sales in units* 40,000 48,000 60,000 52,000 200,000 Add desired ending finished good inventory* * 4,800 6,000 5,200 4,400 4,400 Total finished goods inventory needed 44,800 54,000 65,200 56,400 204,400 Deduct beginning finished goods inventory** (4,000) (4,800) (6,000) (5,200) (4,000) + Units to be produced 40,800 49,200 59,200 51,200 200,400Figure 9.3 Sales Budget for Jerry's Ice Cream Jerry's Ice Cream Sales Budget Year Ending December 31 Quarter 2 3 4 Year Sales in units (1 unit = 1 gallon) 40,000 48,000 60,000 52,000 200,000 Sales price per unit X $6 X $6 X $6 X $6 X $6 Sales revenue $240,000 $288,000 $360,000 $312,000 $1,200,000Figure 9.5 Direct Materials Purchases Budget for Jerry's Ice Cream Jerry's Ice Cream Direct Materials Purchases Budget Year Ending December 31 Quarter 2 3 4 Year Units to be produced* 40,800 49,200 59,200 51,200 200,400 Materials required per unit (pounds) x 2 x 2 x 2 x 2 x 2 Materials needed in production 81,600 98,400 118,400 102,400 400,800 Add desired ending inventory** 19,680 23,680 20,480 20,000 20,000 Materials needed in inventory 101,280 122,080 138,880 122,400 420,800 Deduct beginning inventory* ** (16,320) (19,680) (23,680) (20,480) (16,320) Direct materials to be purchased (pounds) 84,960 102,400 115,200 101,920 404,480 Cost of materials per pound X $1 X $1 X $1 X $1 X $1 Cost of materials to be purchased $84,960 $102,400 $1 15,200 $101,920 $404,480 Direct materials cost per unit**#* $ 2.00Figure 9.6 Direct Labor Budget for Jerry's Ice Cream Jerry's Ice Cream Direct Labor Budget Year Ending December 31 Quarter 1 2 3 4 Year Units to be produced* 40,800 49,200 59,200 51,200 200,400 Direct labor hours per unit x 0.10 x 0.10 X 0.10 x 0.10 x 0.10 Total direct labor hours needed in production 4,080 4,920 5,920 5,120 20,040 Labor rate per hour X $13 X $13 X $13 X $13 X $13 Total direct labor cost $ 53,040 $ 63,960 $ 76,960 $ 66,560 $260,520 Direct labor cost per unit* * 1.30Figure 9.7 Manufacturing Overhead Budget for Jerry's Ice Cream Jerry's Ice Cream Manufacturing Overhead Budget Year Ending December 31 Quarter 2 3 Year Units to be produced* 40,800 49,200 59,200 51,200 200,400 Variable overhead costs Indirect materials ($0.15 per unit) $ 6,120 $ 7,380 $ 8,880 $ 7,680 $ 30,060 Indirect labor ($0.10 per unit) 4,080 4,920 5,920 5,120 20,040 Other ($0.25 per unit) 10,200 12,300 14,800 12,800 50, 100 Total variable overhead costs $ 20,400 $ 24,600 $ 29,600 $ 25,600 $ 100,200 Fixed overhead costs Salaries 15,000 15,000 15,000 15,000 60,000 Rent 10,000 10,000 10,000 10,000 40,000 Depreciation 10,070 10,070 10,070 10,070 40,280 Total fixed overhead costs $ 35,070 $ 35,070 $ 35,070 $ 35,070 $140,280 Total overhead costs 55,470 59,670 64,670 60,670 240,480 Deduct depreciation (10,070) (10,070) (10,070) (10,070) (40,280) Cash payments for overhead $ 45,400 $ 49,600 $ 54,600 $ 50,600 $ 200,200 Manufacturing overhead per unit** $ 1.20Your assignment is to: 1. Replicate each of the budgetsancial statements presented in Review Problems 9.3, 9.4, 9.5, 9.6 and 9.7 for Carol's Cookies in Google Sheets and submit your work. -)Tit|e your worksheet \"Your Name Carols Cookies\". 2. Please put all schedules in one tab, running vertically up and down the page. Also keep each schedule distinct, clearly labeled, and separated from the others with white space on your worksheet. Ensure that your formatting looks nice and that you are ready to present your results to the class if called on! 3. In addition, you must gather the key data behind the schedules in a 'Data' block at the top of your worksheet. You must use formulas to link all the tables together and tie them back to your data block. The target is a single worksheet that will correctly recalculate itself from top to bottom if we change some of the assumptions in your data block. -) SUBMIT YOUR WORKSHEET WITH ALL THE ORIGINAL FIGURES FROM THE TEXT. HOWEVER, TEST your spreadsheet before submitting it! 1. Change the annual growth rate for sales from 25% to 20%. all the subsequent tables through to the budgeted balance sheet should correctly reflect the changes, not only the sales budget! (BUT, see the 'final note' below) Note that this is not a trivial exercise! You will have to understand the interconnections between the schedules quite well to make this work. 2. Change the selling price from $8.00 to $7.90, observe the changes all the way through to your balance sheet. Do the changes ow through all your schedules? 3. Change the desired inventory level in the production budget from 10% to 15%, observe the changes. Do the changes flow through all your schedules