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 companys 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:Main Street Donuts does quite a bit of business with local corporations. Therefore, they dont collect cash for all of their sales immediately. The companys 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 quarters 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 quarters 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 companys payment pattern for their dough ingredients is: 85% of the quarters 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.Sales:1stQtr2ndQtr3rdQtr4thQtr1stQtr2ndQtrBoxesofdonuts9,5009,00011,00012,50010,2009,800Boxesoflittlebites5,0006,4007,0007,6005,2006,6002022(projected)2023(projected)

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:Requirements1. 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 dont 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.Cash$68,000Accountspayable$6,300Accountsreceivable$18,700Notespayable$-Rawmaterialsinventory$1,567Interestpayable$-Finishedgoodsinventory$728Dividendpayable$1,000Property,plantandequipment,net$80,000Commonstock$100,000Retainedearnings$61,695Totalassets$168,995TotalLiabilitiesandequities$168,995BalanceSheetAsofDecember31,2021AssetsLiabilitiesandEquitiesMainStreetDonuts

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 budgetTo value: $95,000By changing cell: click on the $10.00 sales price cell in the inputs tabClick 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*****

2022 (projected) 2nd Qtr 3rd Qtr 4th Qtr Sales: Boxes of donuts Selling price per box 2nd Qtr 2023 (projected) 1 Qtr 10.2001 9,800 1* Qtr 9,500 10.00 9,000 11,000 12,500 $ 6,400 7,000 7,600 5.2001 6,600 Boxes of little bites Selling price per box 5,000 7.00 $ Collections: Quarter of sale Quarter after sale 90% 10% Finished Goods Inventory: Ending FG inventory requirement Ending FG inventory units (December 31, 2021) 1% of next quarter's sales 145 boxes Raw Materials Inventory: Raw materials (dough) required per box Raw materials cost per ounce Ending RM inventory requirement Ending RM inventory units (December 31, 2021) Paid in quarter of purchase Paid in following quarter 24 ounces $0.09 per ounce 5% of next quarter's production needs 17,411 ounces 85% 15% Direct labor: Labor required per box Labor cost per hour 0.08 hours 17.00 per hour $ Manfacturing Overhead: Variable Fixed Noncash fixed (included in above) $ $ $ 1.50 per box 12,000 per quarter 900 per quarter Selling & Admin Expenses: Variable Fixed Noncash fixed (included in above) $ $ S 1.95 per box 4,000 per quarter 700 per quarter Cash: Minimum cash balance $ $ 20,000 Other: Kitchen equipment purchase Kitchen equipment purchase Dividends payment $ $ S 3,000 in June (210 Quarter) 1,800 in July (3 Quarter) 1,000 Declared every December (Paid every January - 1st Quarter) NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement 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 $ 1,000 100,000 61,695 168,995 Total assets Projected Sales Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL 42,000 $ Boxes of donuts sold Unit price per box Boxes of little bites sold Unit price per box 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 $ 12,500 10.00 7,600 7.00 178,200 26,000 $ $ Expected Cash Collections Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL 121,320 $ 143,100 $ 160,380 Current quarter Prior quarter Total Cash Collections $ $ $ 117,000 $ 18,700 135,700 Production Budget 2022 2023 1st Qtr 2nd Qtr 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Unit sales (boxes - both donuts & little bites) Plus: desired FG ending inventory Total units required (boxes) Less: FG beginning inventory Units produced (boxes) Raw Materials Budget 2022 2023 1st Qtr 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Units produced (boxes) RM ounces required per box Total RM ounces used for production Plus: desired RM ending inventory Total ounces required Less: RM beginning inventory Raw material purchases (ounces) Cost per ounce Raw material purchases (cost) Direct Labor Budget 2022 3rd Qtr 1st Qtr 2nd Qtr 4th otr TOTAL Units produced (boxes) Labor hours per unit Total labor hours required Rate per labor hour Direct labor cost Manufacturing Overhead Budget 2022 3rd Qtr 1 Qtr 2nd Qtr 4th Qtr TOTAL Units produced (boxes) Variable overhead rate per unit Total variable overhead cost Fixed overhead Total overhead cost Noncash overhead expenses Overhead cash disbursements Product Cost Per Unit Direct materials cost per box Direct labor cost per box Variable overhead cost per box Total product cost per box (using variable costing) Selling & Administrative Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Unit sales (boxes - both donuts & little bites Variable selling & administrative cost per unit Total variable S&A Fixed S&A Total S&A Noncash S&A expenses S&A disbursements Cash Budget 2022 3rd Qtr 1st Qtr 2nd Qtr 4th Qtr TOTAL 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 Main Street Donuts Budgeted Income Statement For the Year Ended December 31, 2022 Sales Variable expenses: Variable COGS Selling & administrative Contribution margin Fixed expenses: Manufacturing overhead Selling & administrative Net income A B D 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 Accounts payable Notes payable Interest payable Dividend payable Common stock Retained earnings Total Liabilities and equities 1 Total assets GOAL SEEK selling price per unit 2022 (projected) 2nd Qtr 3rd Qtr 4th Qtr Sales: Boxes of donuts Selling price per box 2nd Qtr 2023 (projected) 1 Qtr 10.2001 9,800 1* Qtr 9,500 10.00 9,000 11,000 12,500 $ 6,400 7,000 7,600 5.2001 6,600 Boxes of little bites Selling price per box 5,000 7.00 $ Collections: Quarter of sale Quarter after sale 90% 10% Finished Goods Inventory: Ending FG inventory requirement Ending FG inventory units (December 31, 2021) 1% of next quarter's sales 145 boxes Raw Materials Inventory: Raw materials (dough) required per box Raw materials cost per ounce Ending RM inventory requirement Ending RM inventory units (December 31, 2021) Paid in quarter of purchase Paid in following quarter 24 ounces $0.09 per ounce 5% of next quarter's production needs 17,411 ounces 85% 15% Direct labor: Labor required per box Labor cost per hour 0.08 hours 17.00 per hour $ Manfacturing Overhead: Variable Fixed Noncash fixed (included in above) $ $ $ 1.50 per box 12,000 per quarter 900 per quarter Selling & Admin Expenses: Variable Fixed Noncash fixed (included in above) $ $ S 1.95 per box 4,000 per quarter 700 per quarter Cash: Minimum cash balance $ $ 20,000 Other: Kitchen equipment purchase Kitchen equipment purchase Dividends payment $ $ S 3,000 in June (210 Quarter) 1,800 in July (3 Quarter) 1,000 Declared every December (Paid every January - 1st Quarter) NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement 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 $ 1,000 100,000 61,695 168,995 Total assets Projected Sales Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL 42,000 $ Boxes of donuts sold Unit price per box Boxes of little bites sold Unit price per box 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 $ 12,500 10.00 7,600 7.00 178,200 26,000 $ $ Expected Cash Collections Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL 121,320 $ 143,100 $ 160,380 Current quarter Prior quarter Total Cash Collections $ $ $ 117,000 $ 18,700 135,700 Production Budget 2022 2023 1st Qtr 2nd Qtr 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Unit sales (boxes - both donuts & little bites) Plus: desired FG ending inventory Total units required (boxes) Less: FG beginning inventory Units produced (boxes) Raw Materials Budget 2022 2023 1st Qtr 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Units produced (boxes) RM ounces required per box Total RM ounces used for production Plus: desired RM ending inventory Total ounces required Less: RM beginning inventory Raw material purchases (ounces) Cost per ounce Raw material purchases (cost) Direct Labor Budget 2022 3rd Qtr 1st Qtr 2nd Qtr 4th otr TOTAL Units produced (boxes) Labor hours per unit Total labor hours required Rate per labor hour Direct labor cost Manufacturing Overhead Budget 2022 3rd Qtr 1 Qtr 2nd Qtr 4th Qtr TOTAL Units produced (boxes) Variable overhead rate per unit Total variable overhead cost Fixed overhead Total overhead cost Noncash overhead expenses Overhead cash disbursements Product Cost Per Unit Direct materials cost per box Direct labor cost per box Variable overhead cost per box Total product cost per box (using variable costing) Selling & Administrative Budget 2022 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Unit sales (boxes - both donuts & little bites Variable selling & administrative cost per unit Total variable S&A Fixed S&A Total S&A Noncash S&A expenses S&A disbursements Cash Budget 2022 3rd Qtr 1st Qtr 2nd Qtr 4th Qtr TOTAL 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 Main Street Donuts Budgeted Income Statement For the Year Ended December 31, 2022 Sales Variable expenses: Variable COGS Selling & administrative Contribution margin Fixed expenses: Manufacturing overhead Selling & administrative Net income A B D 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 Accounts payable Notes payable Interest payable Dividend payable Common stock Retained earnings Total Liabilities and equities 1 Total assets GOAL SEEK selling price per unit