please include equations

Accounting 285 - Spring 2021 Individual Excel Spreadsheet Project 40 points DUE Thursday, April 1st @ 11:59 PM via Canvas Iyou 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: 1" Qtr 2nd Qtr 3d Qtr 4 Qtr 1"Qtr 2nd Qtr Boxes of donuts 9,500 9,000 11,000 12,500 10,200 9,800 Boxos 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 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 Liabilition and it Possible Your Score Have correct answers that properly calculate & match the following check figures: N Cash collections in the 1st quarter = $135,700 Desired units of finished goods ending inventory for the year = 154 NN Raw material purchases in the 4th quarter = $42,813 N Ending cash balance for the year 2022 = $133,499 N Net inome for the year 2022 = $64,040 2 Total assets at the end of the year 2020 = $232,157 Goal seek selling price per unit is $11.11 10 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) 01 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 Total Score 40 Individual Excel Spreadsheet - Budget Case Inputs Main Street Donuts 2022 (projected) 1 Otr 2nd otr 3 tr 9.500 9.000 11,000 $ 10.00 4th Qtr 2023 projected) 1M Qur 10.200 9,800 24 qtr 12,500 Sales: Boxes of donuts Selling price per box 7.000 5,000 6.400 7.600) 5,200 6,600 Boxes of little bites Selling price per box $ 7.00 Collections: Quarter of sale Quarter after sale 90% 10% 1% of next.quarter's sales 145 boxos Finished Goods Inventory Ending FG inventory requirement Ending FG inventory units (December 31, 2021) 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 Pald 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: 3 Labor required per box Labor cost per hour 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 Manfacturing Overhead: 2 Variable 3 Fixed 4 Noncash fixed included in above S 6 Selling & Admin Expenses: -7 Variable 8 Fixed 19 Noncash fixed included in above) 30 31 Cash: 2 Minimum cash balance 13 44 Other 45 Kitchen equipment purchase 16 Kitchen equipment purchase 47 Dividends payment A $20.000 $ 3,000 in June (2 Quarter $800 in July (3 Quarter S1000 Declared overy December (Paid every January 1st Quarter) F G E A B D C 2nd Qtr 3rd Qtr 4 Qtr Projected Sales Budget 2022 1" Qtr 9,500 9,000 11,000 10.00 $ 10.00 $ 10.00 $ 5,000 6,400 7,000 7.00 $ 7.00 $ 7.00 $ 130,000 $ 134,800 $ 159,000 $ $ TOTAL 12,500 42,000 10.00 $ 420,000.00 7,600 26,000 7.00 $ 182,000.00 178,200 $ 602,000 $ $ 1 2 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 10 11 12 13 Current quarter 14 Prior quarter 15 Total Cash Collections 16 17 Expected Cash Collections Budget 2022 1" Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL Accounting 285 - Spring 2021 Individual Excel Spreadsheet Project 40 points DUE Thursday, April 1st @ 11:59 PM via Canvas Iyou 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: 1" Qtr 2nd Qtr 3d Qtr 4 Qtr 1"Qtr 2nd Qtr Boxes of donuts 9,500 9,000 11,000 12,500 10,200 9,800 Boxos 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 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 Liabilition and it Possible Your Score Have correct answers that properly calculate & match the following check figures: N Cash collections in the 1st quarter = $135,700 Desired units of finished goods ending inventory for the year = 154 NN Raw material purchases in the 4th quarter = $42,813 N Ending cash balance for the year 2022 = $133,499 N Net inome for the year 2022 = $64,040 2 Total assets at the end of the year 2020 = $232,157 Goal seek selling price per unit is $11.11 10 All cells are formula driven (you lose 2 points each time there is a number entered in the worksheets) 01 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 Total Score 40 Individual Excel Spreadsheet - Budget Case Inputs Main Street Donuts 2022 (projected) 1 Otr 2nd otr 3 tr 9.500 9.000 11,000 $ 10.00 4th Qtr 2023 projected) 1M Qur 10.200 9,800 24 qtr 12,500 Sales: Boxes of donuts Selling price per box 7.000 5,000 6.400 7.600) 5,200 6,600 Boxes of little bites Selling price per box $ 7.00 Collections: Quarter of sale Quarter after sale 90% 10% 1% of next.quarter's sales 145 boxos Finished Goods Inventory Ending FG inventory requirement Ending FG inventory units (December 31, 2021) 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 Pald 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: 3 Labor required per box Labor cost per hour 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 Manfacturing Overhead: 2 Variable 3 Fixed 4 Noncash fixed included in above S 6 Selling & Admin Expenses: -7 Variable 8 Fixed 19 Noncash fixed included in above) 30 31 Cash: 2 Minimum cash balance 13 44 Other 45 Kitchen equipment purchase 16 Kitchen equipment purchase 47 Dividends payment A $20.000 $ 3,000 in June (2 Quarter $800 in July (3 Quarter S1000 Declared overy December (Paid every January 1st Quarter) F G E A B D C 2nd Qtr 3rd Qtr 4 Qtr Projected Sales Budget 2022 1" Qtr 9,500 9,000 11,000 10.00 $ 10.00 $ 10.00 $ 5,000 6,400 7,000 7.00 $ 7.00 $ 7.00 $ 130,000 $ 134,800 $ 159,000 $ $ TOTAL 12,500 42,000 10.00 $ 420,000.00 7,600 26,000 7.00 $ 182,000.00 178,200 $ 602,000 $ $ 1 2 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 10 11 12 13 Current quarter 14 Prior quarter 15 Total Cash Collections 16 17 Expected Cash Collections Budget 2022 1" Qtr 2nd Qtr 3rd Qtr 4th Qtr TOTAL