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: 1tQtr 2nd Qtr 3rd Qtr 4th Qtr 1"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,4001 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 $ 80,000 Common stock $ 100,000 Retained earnings $ 61,695 Total assets $ 168,995 Total Liabilities and equities $ 168,995 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. 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-lf 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 1- 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. 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: 1tQtr 2nd Qtr 3rd Qtr 4th Qtr 1"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,4001 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 $ 80,000 Common stock $ 100,000 Retained earnings $ 61,695 Total assets $ 168,995 Total Liabilities and equities $ 168,995 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. 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-lf 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 1- 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