You are expected to complete this assignment individually. Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Main Street Donuts is a small, local business that sells donuts and little bites (donut holes). The information below pertains to the company's budgeting process for the upcoming year, 2022. Budgeting Info: All of this information is already placed into the INPUTS & Beginning Balance Sheet tabs for you to work from. + Main Street Donuts sells only two products: boxes of a dozen donuts & boxes of 25 little bites. Each box is referred to as a finished good unit. Although the company makes and sells two different types of boxes, each box of donuts and little bites will be treated the same excluding the selling price. + Each box of donuts sells for $10.00. Each box of little bites sells for $7.00. + Budgeted sales in units are as follows: 2022 (projected) 2023 (projected) Sales: 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 154 Qtr 2nd Qtr Boxes of donuts 9,500 9,000 11,000 12,500 10,200 9,800 Boxes of little bites 5,000 6,400 7,000 7,600 5,200 6,600 + Main Street Donuts does quite a bit of business with local corporations. Therefore, they don't collect cash for all of their sales immediately. The company's collection pattern is: . 90% of sales are collected in the quarter of sale 10% are collected in the quarter following the sale + The company desires to have finished goods inventory (boxes) on hand at the end of each quarter equal to 1% of the following quarter's budgeted sales in units. On December 31, 2021, the company has 145 boxes on hand. + 24 ounces of dough are required for each box produced. The company desires to have raw materials on hand at the end of each quarter equal to 5% of the following quarter's production needs. On December, 31, 2021, the company has 17,411 ounces of dough on hand. + The dough used to produce the donuts and little bites costs $0.09 per ounce. The company's payment pattern for their dough ingredients is: 85% of the quarter's purchases are paid for in the quarter of purchase 15% are paid for in the quarter following the purchase + Each box requires .08 hours (4.8 minutes) of labor time to make and the hourly employees are paid $17/hour. Wages are paid in the quarter incurred. Variable manufacturing overhead is $1.50 per box. Fixed manufacturing overhead is $12,000 per quarter including $900 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the quarter incurred. Variable selling and administrative expenses are $1.95 per box sold. + Fixed selling and administrative expense is $4.000 per quarter including $700 in depreciation that is not a current cash outflow. + All cash disbursements for selling and administrative costs are paid in the quarter incurred. + Main Street Donuts likes to keep a minimum cash balance on hand of $20,000. Main Street Donuts plans to purchase a few new pieces of equipment in 2022. Once piece will be purchased in June 2022 for $3,000, and the other piece will be purchased in July 2022 for $1,800. Main Street Donuts declares a $1,000 dividend each year in December and pays it out the following January The company uses variable costing. + The company shows the following beginning balance sheet as of December 31, 2021: Main Street Donuts Balance Sheet As of December 31, 2021 Assets Liabilities and Equities Cash $ 68,000 Accounts payable $ 6,300 Accounts receivable $ 18,700 Notes payable $ Raw materials inventory $ 1,567 Interest payable $ Finished goods inventory $ 728 Dividend payable $ 1,000 Property, plant and equipment, net s 80,000 Common stock $ 100,000 Retained earnings 61,695 Total assets $168.995 Total Liabilities and equities $168.995 A A A A A A Requirements 1. Enter your name within the NAME tab. 2. Prepare a master budget for the year ended December 31, 2022 that includes the following: Projected Sales Budget, Expected Cash Collections Budget, Production Budget, Raw Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 10 schedules). Each schedule should be completed as it appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an INPUTS tab you MUST use. Complete the shaded areas of the template only. All of your spreadsheets must be formula driven from the input tab! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. You should NOT type a number the entire time you are working on this project. Any typed numbers will cost you 2 points per instance. There is a purpose to this requirement that we hope you understand. The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value. Instead, when you change it on the inputs tab, it will automatically change every cell which used that value within your entire budget. This becomes even more useful as your spreadsheets become more complex 3. Complete the Goal Seek tab. Main Street Donuts would like to make a charitable contribution of $75,000 at the end of March to help support individuals and families in the local area who have been negatively impacted by COVID-19. However, the company must maintain a minimum cash balance of $20,000. They think that customers may be willing to pay a small premium for their donut boxes if it is known that a portion of the cost will be applied toward this cause. Use Goal Seek to see what the selling price of donut boxes would need to be to end March with an ending cash balance of $95.000 ($75,000 that will be donated + the $20,000 minimum ending cash balance). HINT: To find goal seek, click on the Data tab and under Data Tools click on the What if Analysis. When you type your info into the template this provides you want to do the following: Set cell: click on the ending cash balance cell for the 1st quarter in the cash budget To value: $95.000 By changing cell: click on the $10.00 sales price cell in the inputs tab Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal. If you have formula driven everything correctly, you should be able to click back to the inputs tab and see the new sales price listed in replacement of $10.00 (answer is provided in the rubric at the end of this document as a check figure) Manually change the sales price back to $10.00 on the inputs tab and then click over to the Goal Seek tab and enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually type a number into the cell. 4. Compare your file against the project rubric below. You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric. 5. Upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version. Good luck. Have fun. I hope you enjoy this project! ***** Rubric below ***** Evaluation Rubric that will be used to assess your project Possible Your Score 2 Have correct answers that properly calculate & match the following check figures: Cash collections in the 1st quarter = $135,700 Desired units of finished goods ending inventory for the year = 154 Raw material purchases in the 4th quarter = $42,813 Ending cash balance for the year 2022 = $133,499 Net income for the year 2022 = $64,040 Total assets at the end of the year 2020 = $232,157 Goal seek selling price per unit is $11.11 2 2. 2. 2. 2 2 2 3 10 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) Your worksheet passes the following tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value on the inputs tab before moving on to the next test). On your input tab I will: Change the selling price of little bites to $9.00 and net income should become $116,040. Change cash collections to 50% in quarter of sale and 50% in next quarter and the ending cash balance for the year on the cash budget is $62,219. Change the number of donut boxes sold in the 2nd quarter to 19,000 and the balance sheet should balance with both Total Assets and Total Liabilities & Equity equaling $262,457. 5 5 5 5 Total Score 40 1r* Qtr 9,500 $ 10.00 2- Qur 9,000 314 Qer 11,000 4 Qer 12,500 1rQtr 10,200 2- Otr 9,800 6,400 7,000 5,000 7.00 7,600 5,200 6,600 $ 90% 10% 1% of next quarter's sales 145 boxes 24 ounces $0.09 per ounce 5% of next quarter's production needs 17,411 ounces 85% 15% 4 Sales: 5 Boxes of donuts 6 Selling price per box 7 8 Boxes of little bites 9 Selling price per box 10 11. Collections: 12 Quarter of sale 13 Quarter after sale 14 15 Finished Goods Inventory: 16 Ending FG inventory requirement 17 Ending FG inventory units (December 31, 2021) 18 79 Raw Materials Inventory: 20 Raw materials (dough) required per box 21 Raw materials cost per ounce 22 Ending RM inventory requirement 23 Ending RM inventory units (December 31, 2021) 24 Paid in quarter of purchase 25 Paid in following quarter 26 27 Direct labor: 28 Labor required per box 29 Labor cost per hour 30 31 Manfacturing Overhead: 32 Variable 33 Fixed 34 Noncash fixed (included in above) 35 36 Selling & Admin Expenses: 37 Variable 38 Fixed 39 Noncash fixed (included in above) 40 41 Cash: 42 Minimum cash balance 13 74 Other: 45 Kitchen equipment purchase 46 Kitchen equipment purchase 17 Dividends payment 18 0.08 hours 17.00 per hour $ $ $ $ 1.50 per box 12,000 per quarter 900 per quarter $ $ $ 1.95 per box 4,000 per quarter 700 per quarter $ 20,000 $ $ $ 3,000 in June (2m Quarter) 1,800 in July (34 Quarter) 1,000 Declared every December (Paid every January - 1st Quarter) 6,300 Assets Cash Accounts receivable Raw materials inventory Finished goods inventory Property, plant and equipment, net Main Street Donuts Balance Sheet As of December 31, 2021 Liabilities and Equities $ 68,000 Accounts payable $ 18,700 Notes payable $ 1,567 Interest payable $ 728 Dividend payable $ 80,000 Common stock Retained earnings $ 168,995 Total Liabilities and equities $ A A A A A A 1,000 100,000 61,695 168,995 Total assets A B E F G H. 1 2 D Projected Sales Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th $ 3 4. Boxes of donuts sold 5 Unit price per box 6 Boxes of little bites sold 7 Unit price per box 8 Total Sales 9,500 10.00 $ 5,000 7.00 $ 130,000 $ 9,000 10.00 $ 6,400 7.00 $ 134,800 $ 11,000 10.00 $ 7,000 7.00 $ 159,000 $ Qtr TOTAL 12,500 42,000 10.00 $ 10.00 7,600 26,000 7.00 $ 7.00 178,200 $ 602,000 $ $ 9 10 11 2nd Qtr 4th Qtr Expected Cash Collections Budget 2022 1st Qtr 3rd Qtr $ 117,000.0 $ 121,320$ 143,100 $ $ 18,700 $ 13,000 $ 13,480$ $ 135,700 $ 134,320 $ 156,580 $ 12 13 Current quarter 14 Prior quarter 15 Total Cash Collections 1,920 160,380 $ 15,900 $ 176,280$ TOTAL 541,800 $ 61,080 602,880 16 17 18 19 20 21 22 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Accessibility: Investigate A D 1 2 4th Qtr 2nd Qtr Production Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 14,500 15,400 18,000 154 180 201 14,654 15,580 18,201 -$145.00 -154 -180 14,509 15,426 18,021 20,100 154 20,254 -201 20,053 TOTAL 68,000 154 68,154 -145 68,009 2023 15 Qtr 15,400 16,400 164 15,564 -154 15,410 3 4 Unit sales (boxes - both donuts & little bites) 5 Plus: desired FG ending inventory 6 Total units required (boxes) 7 Less: FG beginning inventory 8 Units produced (boxes) 9 10 11 12 13 14 15 16 17 18 19 20 21 NAME INPUTS Beg. Balance Sheet Accessibility: Investigate Sales & Collections Production RM, DL, & OH S&A A D E F G H B Raw Materials Budget 2022 2023 2nd Qtr 3rd Qtr 1st Qtr 15,410 24 369,840 1st Qtr 14,509 24 348,216 771 348,987 (17,411) 331,576 0.09 $ 29,842 $ 15,426 24 370,224 901 371,125 (771) 370,354 0.09 $ 33,332 $ 18,021 24 432,504 1,003 433,507 (901) 432,606 0.09 $ 38,935 $ 4th Qtr 20,053 24 481,272 771 482,043 (1,003) 481,040 0.09 $ 43,294 $ TOTAL 68,009 24 1,632,216 771 1,635,662 (17,411) 1,615,576 0.09 145,402 $ $ B 9 Units produced (boxes) 5 RM ounces required per box 5 Total RM ounces used for production - Plus: desired RM ending inventory 3 Total ounces required Less: RM beginning inventory o Raw material purchases (ounces) 1 Cost per ounce 2 Raw material purchases (cost) 3 4 5 6 7 Units produced (boxes) 8 Labor hours per unit 9 Total labor hours required o Rate per labor hour 1 Direct labor cost 2 NAME INPUTS Beg. Balance Sheet Direct Labor Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 14,509 0.08 1,161 17.00 $ 19,732 $ 15,426 0.08 1,234 17.00 $ 20,979 $ 18,021 0.08 1,442 17.00 $ 24,509 $ 20,053 0.08 1,604 17.00 $ 27,272 $ TOTAL 68,009 0.08 5,441 17.00 92,492 $ $ Sales & Collections Production RM, DL, & OH S&A + Units produced (boxes) Labor hours per unit Total labor hours required Rate per labor hour Direct labor cost B 14,509 0.08 1,161 17.00 $ 19,732 $ 15,426 0.08 1,234 17.00 $ 20,979 $ D 18,021 0.08 1,442 17.00 $ 24,509 $ E 20,053 0.08 1,604 17.00 $ 27,272 $ 68,009 0.08 5,441 17.00 92,492 $ $ Manufacturing Overhead Budget 4th Qtr Units produced (boxes) Variable overhead rate per unit Total variable overhead cost Fixed overhead Total overhead cost Noncash overhead expenses Overhead cash disbursements 909"M $ $ $ $ $ $ 1st Qtr 2nd Qtr 14,509 15,426 0.12 $ 0.12 $ 1,741 $ 1,851 $ 12,000 $ 12,000 $ 13,741 $ 13,851 $ 900 $ 900 $ 12,841 $ 12,951 $ 2022 3rd Qtr 18,021 0.12 $ 2,163 $ 12,000 $ 14,163 $ 900$ 13,263 $ 20,053 0.12 $ 2,406 $ 12,000 $ 14,406 $ 900 $ 13,506 $ TOTAL 68,009 0.12 8,161 48,000 56,161 3,600 52,561 Product Cost Per Unit Direct materials cost per box $ 2 Direct labor cost per box $ 1 Variable overhead cost per box $ 1.50 Total product cost per box (using variable costina) $ 5.02 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A 2nd Qtr 4th A D E 1 Selling & Administrative Budget 2 2022 3 1st Qtr 3rd Qtr Qtr TOTAL 4 Unit sales (boxes - both donuts & little bites) 14,500 15,400 18,000 20,100 68,000 5 Variable selling & administrative cost per unit $ 1.95 $ 1.95 $ 1.95 $ 1.95 $ 1.95 6 Total variable S&A $ 28,275 $30,030 $35,100 $39,195 $ 132,600 7 Fixed S&A $ 4,000 $ 4,000 $ 4,000 $ 4,000 $ 16,000 8 Total S&A $ 32,275 $34,030 $39,100 $43,195 $ 148,600 9 Noncash S&A expenses $ (700) $ (700) $ (700) $ (700) $ (2,800) 10 S&A disbursements $ 31,575 $33,330 $38,400 $42,495 $ 145,800 11 12 13 14 15 16 D G Cash Budget 2022 3rd Qtr $ $ $ 1st Qtr 68,000 $ 135,700 $ 203,700 $ 2nd Qtr 75,775 $ 134,320 $ 210,095 $ 87,026 $ 156,580 $ 243,606 $ 4th Qtr 107,541 176,280 283,821 $ $ $ TOTAL 338,342 602,880 941,222 Beginning cash balance Cash collections Available cash balance Cash disbursements: Direct material purchased in prior quarter Direct material purchased in current quarter Direct labor Overhead Selling & administrative Equipment purchases Dividends Total cash disbursements - Balance before financing Minimum cash balance Excess (deficiency) - Ending cash balance $ $ $ $ 4,476 $ 28,332 $ 20,979 $ 12,951 $ 33,330 $ 3,000 $ 5,000 $ 33,094 $ 24,509 $ 13,263 $ 38,400 $ 1,800 17,411 $ 25,366 $ 19,732 $ 12,841 $ 31,575$ $ 1,000 107,925 $ 95,775 $ 20,000 $ 75,775 $ 75,775 $ 5,840 $ 36,800 $ 27,272 $ 13,506 $ 42,495 $ $ $ 125,913 $ 157,908 $ 20,000 $ 137,908 $ 137,908 32,727 123,592 92,492 52,561 145,800 4,800 1,000 452,972 488,250 20,000 468,250 $ $ $ $ $ $ 103,069 107,026 20,000 87,026 87,026 $ $ $ $ $ 116,065 $ 127,541 $ 20,000 $ 107,541 $ 107,541 $ Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Shee ... Accessibility Investigate B12 D E F G 602,000 A B 1 Main Street Donuts 2 Budgeted Income Statement 3 For the Year Ended December 31, 2022 4. Sales 5 Variable expenses: 6 Variable COGS $ 247,520 7 Selling & administrative 132,600 8 Contribution margin 9 Fixed expenses: 10 Manufacturing overhead $ 48,000 11 Selling & administrative 2,800 12 Net income 13 380,120 221,880 50,800 380,120 14. 15 16 17 18 19 20 21 22 Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Shee ... 6,300 Assets Cash Accounts receivable Raw materials inventory Finished goods inventory Property, plant and equipment, net Main Street Donuts Budgeted Balance Sheet As of December 31, 2022 Liabilities and Equities $ 137,908 Accounts payable $ 6,800 Notes payable Interest payable $ Dividend payable Common stock $ Retained earnings Total Liabilities and equities GA GA GA GA GA 1,000 100,000 Total assets Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + 1 GOAL SEEK selling price per unit 2 3 4 6 6 7 8 9 You are expected to complete this assignment individually. Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Main Street Donuts is a small, local business that sells donuts and little bites (donut holes). The information below pertains to the company's budgeting process for the upcoming year, 2022. Budgeting Info: All of this information is already placed into the INPUTS & Beginning Balance Sheet tabs for you to work from. + Main Street Donuts sells only two products: boxes of a dozen donuts & boxes of 25 little bites. Each box is referred to as a finished good unit. Although the company makes and sells two different types of boxes, each box of donuts and little bites will be treated the same excluding the selling price. + Each box of donuts sells for $10.00. Each box of little bites sells for $7.00. + Budgeted sales in units are as follows: 2022 (projected) 2023 (projected) Sales: 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 154 Qtr 2nd Qtr Boxes of donuts 9,500 9,000 11,000 12,500 10,200 9,800 Boxes of little bites 5,000 6,400 7,000 7,600 5,200 6,600 + Main Street Donuts does quite a bit of business with local corporations. Therefore, they don't collect cash for all of their sales immediately. The company's collection pattern is: . 90% of sales are collected in the quarter of sale 10% are collected in the quarter following the sale + The company desires to have finished goods inventory (boxes) on hand at the end of each quarter equal to 1% of the following quarter's budgeted sales in units. On December 31, 2021, the company has 145 boxes on hand. + 24 ounces of dough are required for each box produced. The company desires to have raw materials on hand at the end of each quarter equal to 5% of the following quarter's production needs. On December, 31, 2021, the company has 17,411 ounces of dough on hand. + The dough used to produce the donuts and little bites costs $0.09 per ounce. The company's payment pattern for their dough ingredients is: 85% of the quarter's purchases are paid for in the quarter of purchase 15% are paid for in the quarter following the purchase + Each box requires .08 hours (4.8 minutes) of labor time to make and the hourly employees are paid $17/hour. Wages are paid in the quarter incurred. Variable manufacturing overhead is $1.50 per box. Fixed manufacturing overhead is $12,000 per quarter including $900 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the quarter incurred. Variable selling and administrative expenses are $1.95 per box sold. + Fixed selling and administrative expense is $4.000 per quarter including $700 in depreciation that is not a current cash outflow. + All cash disbursements for selling and administrative costs are paid in the quarter incurred. + Main Street Donuts likes to keep a minimum cash balance on hand of $20,000. Main Street Donuts plans to purchase a few new pieces of equipment in 2022. Once piece will be purchased in June 2022 for $3,000, and the other piece will be purchased in July 2022 for $1,800. Main Street Donuts declares a $1,000 dividend each year in December and pays it out the following January The company uses variable costing. + The company shows the following beginning balance sheet as of December 31, 2021: Main Street Donuts Balance Sheet As of December 31, 2021 Assets Liabilities and Equities Cash $ 68,000 Accounts payable $ 6,300 Accounts receivable $ 18,700 Notes payable $ Raw materials inventory $ 1,567 Interest payable $ Finished goods inventory $ 728 Dividend payable $ 1,000 Property, plant and equipment, net s 80,000 Common stock $ 100,000 Retained earnings 61,695 Total assets $168.995 Total Liabilities and equities $168.995 A A A A A A Requirements 1. Enter your name within the NAME tab. 2. Prepare a master budget for the year ended December 31, 2022 that includes the following: Projected Sales Budget, Expected Cash Collections Budget, Production Budget, Raw Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Budget, Cash Budget, Budgeted Income Statement, & Budgeted Balance Sheet (a total of 10 schedules). Each schedule should be completed as it appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an INPUTS tab you MUST use. Complete the shaded areas of the template only. All of your spreadsheets must be formula driven from the input tab! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. You should NOT type a number the entire time you are working on this project. Any typed numbers will cost you 2 points per instance. There is a purpose to this requirement that we hope you understand. The beauty (usefulness) of linking your spreadsheets to an inputs page is when something changes, such as the cost per unit of raw materials, you don't have to go through the entire budget to fix every cell that used that value. Instead, when you change it on the inputs tab, it will automatically change every cell which used that value within your entire budget. This becomes even more useful as your spreadsheets become more complex 3. Complete the Goal Seek tab. Main Street Donuts would like to make a charitable contribution of $75,000 at the end of March to help support individuals and families in the local area who have been negatively impacted by COVID-19. However, the company must maintain a minimum cash balance of $20,000. They think that customers may be willing to pay a small premium for their donut boxes if it is known that a portion of the cost will be applied toward this cause. Use Goal Seek to see what the selling price of donut boxes would need to be to end March with an ending cash balance of $95.000 ($75,000 that will be donated + the $20,000 minimum ending cash balance). HINT: To find goal seek, click on the Data tab and under Data Tools click on the What if Analysis. When you type your info into the template this provides you want to do the following: Set cell: click on the ending cash balance cell for the 1st quarter in the cash budget To value: $95.000 By changing cell: click on the $10.00 sales price cell in the inputs tab Click 'OK' and this function will work through the math within your file to figure out what the sales price would need to be to reach this goal. If you have formula driven everything correctly, you should be able to click back to the inputs tab and see the new sales price listed in replacement of $10.00 (answer is provided in the rubric at the end of this document as a check figure) Manually change the sales price back to $10.00 on the inputs tab and then click over to the Goal Seek tab and enter your answer in the cell provided. This is the only cell in the project in which it is allowed that you manually type a number into the cell. 4. Compare your file against the project rubric below. You should be able to figure out your grade before you even submit your project by comparing yourself against the rubric. 5. Upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version. Good luck. Have fun. I hope you enjoy this project! ***** Rubric below ***** Evaluation Rubric that will be used to assess your project Possible Your Score 2 Have correct answers that properly calculate & match the following check figures: Cash collections in the 1st quarter = $135,700 Desired units of finished goods ending inventory for the year = 154 Raw material purchases in the 4th quarter = $42,813 Ending cash balance for the year 2022 = $133,499 Net income for the year 2022 = $64,040 Total assets at the end of the year 2020 = $232,157 Goal seek selling price per unit is $11.11 2 2. 2. 2. 2 2 2 3 10 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) Your worksheet passes the following tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value on the inputs tab before moving on to the next test). On your input tab I will: Change the selling price of little bites to $9.00 and net income should become $116,040. Change cash collections to 50% in quarter of sale and 50% in next quarter and the ending cash balance for the year on the cash budget is $62,219. Change the number of donut boxes sold in the 2nd quarter to 19,000 and the balance sheet should balance with both Total Assets and Total Liabilities & Equity equaling $262,457. 5 5 5 5 Total Score 40 1r* Qtr 9,500 $ 10.00 2- Qur 9,000 314 Qer 11,000 4 Qer 12,500 1rQtr 10,200 2- Otr 9,800 6,400 7,000 5,000 7.00 7,600 5,200 6,600 $ 90% 10% 1% of next quarter's sales 145 boxes 24 ounces $0.09 per ounce 5% of next quarter's production needs 17,411 ounces 85% 15% 4 Sales: 5 Boxes of donuts 6 Selling price per box 7 8 Boxes of little bites 9 Selling price per box 10 11. Collections: 12 Quarter of sale 13 Quarter after sale 14 15 Finished Goods Inventory: 16 Ending FG inventory requirement 17 Ending FG inventory units (December 31, 2021) 18 79 Raw Materials Inventory: 20 Raw materials (dough) required per box 21 Raw materials cost per ounce 22 Ending RM inventory requirement 23 Ending RM inventory units (December 31, 2021) 24 Paid in quarter of purchase 25 Paid in following quarter 26 27 Direct labor: 28 Labor required per box 29 Labor cost per hour 30 31 Manfacturing Overhead: 32 Variable 33 Fixed 34 Noncash fixed (included in above) 35 36 Selling & Admin Expenses: 37 Variable 38 Fixed 39 Noncash fixed (included in above) 40 41 Cash: 42 Minimum cash balance 13 74 Other: 45 Kitchen equipment purchase 46 Kitchen equipment purchase 17 Dividends payment 18 0.08 hours 17.00 per hour $ $ $ $ 1.50 per box 12,000 per quarter 900 per quarter $ $ $ 1.95 per box 4,000 per quarter 700 per quarter $ 20,000 $ $ $ 3,000 in June (2m Quarter) 1,800 in July (34 Quarter) 1,000 Declared every December (Paid every January - 1st Quarter) 6,300 Assets Cash Accounts receivable Raw materials inventory Finished goods inventory Property, plant and equipment, net Main Street Donuts Balance Sheet As of December 31, 2021 Liabilities and Equities $ 68,000 Accounts payable $ 18,700 Notes payable $ 1,567 Interest payable $ 728 Dividend payable $ 80,000 Common stock Retained earnings $ 168,995 Total Liabilities and equities $ A A A A A A 1,000 100,000 61,695 168,995 Total assets A B E F G H. 1 2 D Projected Sales Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th $ 3 4. Boxes of donuts sold 5 Unit price per box 6 Boxes of little bites sold 7 Unit price per box 8 Total Sales 9,500 10.00 $ 5,000 7.00 $ 130,000 $ 9,000 10.00 $ 6,400 7.00 $ 134,800 $ 11,000 10.00 $ 7,000 7.00 $ 159,000 $ Qtr TOTAL 12,500 42,000 10.00 $ 10.00 7,600 26,000 7.00 $ 7.00 178,200 $ 602,000 $ $ 9 10 11 2nd Qtr 4th Qtr Expected Cash Collections Budget 2022 1st Qtr 3rd Qtr $ 117,000.0 $ 121,320$ 143,100 $ $ 18,700 $ 13,000 $ 13,480$ $ 135,700 $ 134,320 $ 156,580 $ 12 13 Current quarter 14 Prior quarter 15 Total Cash Collections 1,920 160,380 $ 15,900 $ 176,280$ TOTAL 541,800 $ 61,080 602,880 16 17 18 19 20 21 22 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Accessibility: Investigate A D 1 2 4th Qtr 2nd Qtr Production Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 14,500 15,400 18,000 154 180 201 14,654 15,580 18,201 -$145.00 -154 -180 14,509 15,426 18,021 20,100 154 20,254 -201 20,053 TOTAL 68,000 154 68,154 -145 68,009 2023 15 Qtr 15,400 16,400 164 15,564 -154 15,410 3 4 Unit sales (boxes - both donuts & little bites) 5 Plus: desired FG ending inventory 6 Total units required (boxes) 7 Less: FG beginning inventory 8 Units produced (boxes) 9 10 11 12 13 14 15 16 17 18 19 20 21 NAME INPUTS Beg. Balance Sheet Accessibility: Investigate Sales & Collections Production RM, DL, & OH S&A A D E F G H B Raw Materials Budget 2022 2023 2nd Qtr 3rd Qtr 1st Qtr 15,410 24 369,840 1st Qtr 14,509 24 348,216 771 348,987 (17,411) 331,576 0.09 $ 29,842 $ 15,426 24 370,224 901 371,125 (771) 370,354 0.09 $ 33,332 $ 18,021 24 432,504 1,003 433,507 (901) 432,606 0.09 $ 38,935 $ 4th Qtr 20,053 24 481,272 771 482,043 (1,003) 481,040 0.09 $ 43,294 $ TOTAL 68,009 24 1,632,216 771 1,635,662 (17,411) 1,615,576 0.09 145,402 $ $ B 9 Units produced (boxes) 5 RM ounces required per box 5 Total RM ounces used for production - Plus: desired RM ending inventory 3 Total ounces required Less: RM beginning inventory o Raw material purchases (ounces) 1 Cost per ounce 2 Raw material purchases (cost) 3 4 5 6 7 Units produced (boxes) 8 Labor hours per unit 9 Total labor hours required o Rate per labor hour 1 Direct labor cost 2 NAME INPUTS Beg. Balance Sheet Direct Labor Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 14,509 0.08 1,161 17.00 $ 19,732 $ 15,426 0.08 1,234 17.00 $ 20,979 $ 18,021 0.08 1,442 17.00 $ 24,509 $ 20,053 0.08 1,604 17.00 $ 27,272 $ TOTAL 68,009 0.08 5,441 17.00 92,492 $ $ Sales & Collections Production RM, DL, & OH S&A + Units produced (boxes) Labor hours per unit Total labor hours required Rate per labor hour Direct labor cost B 14,509 0.08 1,161 17.00 $ 19,732 $ 15,426 0.08 1,234 17.00 $ 20,979 $ D 18,021 0.08 1,442 17.00 $ 24,509 $ E 20,053 0.08 1,604 17.00 $ 27,272 $ 68,009 0.08 5,441 17.00 92,492 $ $ Manufacturing Overhead Budget 4th Qtr Units produced (boxes) Variable overhead rate per unit Total variable overhead cost Fixed overhead Total overhead cost Noncash overhead expenses Overhead cash disbursements 909"M $ $ $ $ $ $ 1st Qtr 2nd Qtr 14,509 15,426 0.12 $ 0.12 $ 1,741 $ 1,851 $ 12,000 $ 12,000 $ 13,741 $ 13,851 $ 900 $ 900 $ 12,841 $ 12,951 $ 2022 3rd Qtr 18,021 0.12 $ 2,163 $ 12,000 $ 14,163 $ 900$ 13,263 $ 20,053 0.12 $ 2,406 $ 12,000 $ 14,406 $ 900 $ 13,506 $ TOTAL 68,009 0.12 8,161 48,000 56,161 3,600 52,561 Product Cost Per Unit Direct materials cost per box $ 2 Direct labor cost per box $ 1 Variable overhead cost per box $ 1.50 Total product cost per box (using variable costina) $ 5.02 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A 2nd Qtr 4th A D E 1 Selling & Administrative Budget 2 2022 3 1st Qtr 3rd Qtr Qtr TOTAL 4 Unit sales (boxes - both donuts & little bites) 14,500 15,400 18,000 20,100 68,000 5 Variable selling & administrative cost per unit $ 1.95 $ 1.95 $ 1.95 $ 1.95 $ 1.95 6 Total variable S&A $ 28,275 $30,030 $35,100 $39,195 $ 132,600 7 Fixed S&A $ 4,000 $ 4,000 $ 4,000 $ 4,000 $ 16,000 8 Total S&A $ 32,275 $34,030 $39,100 $43,195 $ 148,600 9 Noncash S&A expenses $ (700) $ (700) $ (700) $ (700) $ (2,800) 10 S&A disbursements $ 31,575 $33,330 $38,400 $42,495 $ 145,800 11 12 13 14 15 16 D G Cash Budget 2022 3rd Qtr $ $ $ 1st Qtr 68,000 $ 135,700 $ 203,700 $ 2nd Qtr 75,775 $ 134,320 $ 210,095 $ 87,026 $ 156,580 $ 243,606 $ 4th Qtr 107,541 176,280 283,821 $ $ $ TOTAL 338,342 602,880 941,222 Beginning cash balance Cash collections Available cash balance Cash disbursements: Direct material purchased in prior quarter Direct material purchased in current quarter Direct labor Overhead Selling & administrative Equipment purchases Dividends Total cash disbursements - Balance before financing Minimum cash balance Excess (deficiency) - Ending cash balance $ $ $ $ 4,476 $ 28,332 $ 20,979 $ 12,951 $ 33,330 $ 3,000 $ 5,000 $ 33,094 $ 24,509 $ 13,263 $ 38,400 $ 1,800 17,411 $ 25,366 $ 19,732 $ 12,841 $ 31,575$ $ 1,000 107,925 $ 95,775 $ 20,000 $ 75,775 $ 75,775 $ 5,840 $ 36,800 $ 27,272 $ 13,506 $ 42,495 $ $ $ 125,913 $ 157,908 $ 20,000 $ 137,908 $ 137,908 32,727 123,592 92,492 52,561 145,800 4,800 1,000 452,972 488,250 20,000 468,250 $ $ $ $ $ $ 103,069 107,026 20,000 87,026 87,026 $ $ $ $ $ 116,065 $ 127,541 $ 20,000 $ 107,541 $ 107,541 $ Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Shee ... Accessibility Investigate B12 D E F G 602,000 A B 1 Main Street Donuts 2 Budgeted Income Statement 3 For the Year Ended December 31, 2022 4. Sales 5 Variable expenses: 6 Variable COGS $ 247,520 7 Selling & administrative 132,600 8 Contribution margin 9 Fixed expenses: 10 Manufacturing overhead $ 48,000 11 Selling & administrative 2,800 12 Net income 13 380,120 221,880 50,800 380,120 14. 15 16 17 18 19 20 21 22 Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Shee ... 6,300 Assets Cash Accounts receivable Raw materials inventory Finished goods inventory Property, plant and equipment, net Main Street Donuts Budgeted Balance Sheet As of December 31, 2022 Liabilities and Equities $ 137,908 Accounts payable $ 6,800 Notes payable Interest payable $ Dividend payable Common stock $ Retained earnings Total Liabilities and equities GA GA GA GA GA 1,000 100,000 Total assets Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + 1 GOAL SEEK selling price per unit 2 3 4 6 6 7 8 9
