5:48 ACCT 285 - Excel Project... 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 Shoot 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 proced 2023 pred Sales: Our Otro corrow Honuts 0500 OB11 BC 12500100 Boss 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 pattern for their 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! for a Bocorro BOGOR BSC 3.000 1.000 1.950 Blomster 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 outfiow. 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. Main Street Donuts Balance Sheet As of December 31, 2001 he 68,000 Accounts Ca Accounts receive ROTT SET 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 outfiow. 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. Main Street Donuts Balance Sheet As of December 31, 2021 Labies and as 68.000 Account Accounts receivable $18.700 Noble Raw matervertory $1.507 Wertpable Fished goods inventory 5 728 Dividend peale Properypendum, ut 500.000 Commons 500 300 Fredag etc Totalt 1.73 Total Liabilities and equibles company shows the following beginning balance sheet as of December 31, 2021: $ 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 S. 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 compley ge 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 tabl 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-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 aliowed that you manually type a number into the coll 4. Compare your file against the project rubric below You should be able to figure out your grade before you even submit your project but cominciunali against the rubric. 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 coll 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 Postou Have correct answers that properly calculate & match the following check figures: Cash collections in the 1" quarter 5135.700 Desired units of finished goods ending inventory for the year 154 Raw material purchases in the curr 542.813 Ending cash balance for the year 2022-5133.499 Net income for the year 2022-564040 Tot sets at the end of the year 2020 =5232.157 Cow senkeling price per unit is $11.11 All cells are formula driven your chime 10 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 wil Change selling price of the bile 9,00 and et income should become $116,040 Change cash collections to 50% in quarter of Sale and 50% in ned quarter and the ending cash balance for the year on the cash budgets $62.219 Change the number of donut bones sold in the quarter to 19.000 and the balance she should bance with both Towels and Tour 5 Labdies & Equity equaling $262.457 5 Total score 40 9:24 ACCT 285 - Spring 2021 -... NAME INPUTS Beg Balance Sheet Sales & Collection Product ROL. 60 9:25 ACCT 285 - Spring 2021-... NAME INPUTS Beg. Balance Sheet Sales & Collections 2009 0000 Assets eau Ancora Howy Fished govery per plant and current Main Street lance Sheet As of December 31, 2021 TATO Artsy 18.00 No pay 15 instyle 720 Dividende 180.000 Common T. | Total Libes and equities Total asset 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 outfiow. 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. Main Street Donuts Balance Sheet As of December 31, 2021 Labies and as 68.000 Account Accounts receivable $18.700 Noble Raw matervertory $1.507 Wertpable Fished goods inventory 5 728 Dividend peale Properypendum, ut 500.000 Commons 500 300 Fredag etc Totalt 1.73 Total Liabilities and equibles company shows the following beginning balance sheet as of December 31, 2021: $ 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 S. 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 compley 9:26 ACCT 285 - Spring 2021 -... NAME INPUTS Beg Balance Sheet Sales & Collections Prodi con 6 Ubodne Norge 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 coll 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 Postou Have correct answers that properly calculate & match the following check figures: Cash collections in the 1" quarter 5135.700 Desired units of finished goods ending inventory for the year 154 Raw material purchases in the curr 542.813 Ending cash balance for the year 2022-5133.499 Net income for the year 2022-564040 Tot sets at the end of the year 2020 =5232.157 Cow senkeling price per unit is $11.11 All cells are formula driven your chime 10 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 wil Change selling price of the bile 9,00 and et income should become $116,040 Change cash collections to 50% in quarter of Sale and 50% in ned quarter and the ending cash balance for the year on the cash budgets $62.219 Change the number of donut bones sold in the quarter to 19.000 and the balance she should bance with both Towels and Tour 5 Labdies & Equity equaling $262.457 5 Total score 40 9:25 ACCT 285 - Spring 2021-... NAME INPUTS Beg. Balance Sheet Sales & Collections Teler BO Les UM TO Total Cath Colections 9:26 ACCT 285 - Spring 2021 -... NAME INPUTS Beg. Balance Sheet Sales & Collections Production RUOLO ITAL 9:26 ACCT 285 - Spring 2021-... NAME INPUTS Beg. Balance Sheet Sales & Col 00001 Main Street Donuts 0000 Budgeted Income Statement 0000 For the Year Ended December 31, 2022 Sales Variable expenses Variable COGS Selling & administrative Contribution margin Fleed expenses: Manufacturing overhead Selling & administrative Net income 9:27 ACCT 285 - Spring 2021 -... NAME INPUTS Beg. Balance Sheet Sales & Collections Production 9:26 ACCT 285 - Spring 2021 -... NAME INPUTS Beg Balance Sheet Sales & Collections Production ROL LE Show 5:48 ACCT 285 - Excel Project... 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 Shoot 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 proced 2023 pred Sales: Our Otro corrow Honuts 0500 OB11 BC 12500100 Boss 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 pattern for their 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! for a Bocorro BOGOR BSC 3.000 1.000 1.950 Blomster 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 outfiow. 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. Main Street Donuts Balance Sheet As of December 31, 2001 he 68,000 Accounts Ca Accounts receive ROTT SET 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 outfiow. 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. Main Street Donuts Balance Sheet As of December 31, 2021 Labies and as 68.000 Account Accounts receivable $18.700 Noble Raw matervertory $1.507 Wertpable Fished goods inventory 5 728 Dividend peale Properypendum, ut 500.000 Commons 500 300 Fredag etc Totalt 1.73 Total Liabilities and equibles company shows the following beginning balance sheet as of December 31, 2021: $ 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 S. 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 compley ge 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 tabl 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-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 aliowed that you manually type a number into the coll 4. Compare your file against the project rubric below You should be able to figure out your grade before you even submit your project but cominciunali against the rubric. 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 coll 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 Postou Have correct answers that properly calculate & match the following check figures: Cash collections in the 1" quarter 5135.700 Desired units of finished goods ending inventory for the year 154 Raw material purchases in the curr 542.813 Ending cash balance for the year 2022-5133.499 Net income for the year 2022-564040 Tot sets at the end of the year 2020 =5232.157 Cow senkeling price per unit is $11.11 All cells are formula driven your chime 10 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 wil Change selling price of the bile 9,00 and et income should become $116,040 Change cash collections to 50% in quarter of Sale and 50% in ned quarter and the ending cash balance for the year on the cash budgets $62.219 Change the number of donut bones sold in the quarter to 19.000 and the balance she should bance with both Towels and Tour 5 Labdies & Equity equaling $262.457 5 Total score 40 9:24 ACCT 285 - Spring 2021 -... NAME INPUTS Beg Balance Sheet Sales & Collection Product ROL. 60 9:25 ACCT 285 - Spring 2021-... NAME INPUTS Beg. Balance Sheet Sales & Collections 2009 0000 Assets eau Ancora Howy Fished govery per plant and current Main Street lance Sheet As of December 31, 2021 TATO Artsy 18.00 No pay 15 instyle 720 Dividende 180.000 Common T. | Total Libes and equities Total asset 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 outfiow. 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. Main Street Donuts Balance Sheet As of December 31, 2021 Labies and as 68.000 Account Accounts receivable $18.700 Noble Raw matervertory $1.507 Wertpable Fished goods inventory 5 728 Dividend peale Properypendum, ut 500.000 Commons 500 300 Fredag etc Totalt 1.73 Total Liabilities and equibles company shows the following beginning balance sheet as of December 31, 2021: $ 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 S. 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 compley 9:26 ACCT 285 - Spring 2021 -... NAME INPUTS Beg Balance Sheet Sales & Collections Prodi con 6 Ubodne Norge 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 coll 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 Postou Have correct answers that properly calculate & match the following check figures: Cash collections in the 1" quarter 5135.700 Desired units of finished goods ending inventory for the year 154 Raw material purchases in the curr 542.813 Ending cash balance for the year 2022-5133.499 Net income for the year 2022-564040 Tot sets at the end of the year 2020 =5232.157 Cow senkeling price per unit is $11.11 All cells are formula driven your chime 10 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 wil Change selling price of the bile 9,00 and et income should become $116,040 Change cash collections to 50% in quarter of Sale and 50% in ned quarter and the ending cash balance for the year on the cash budgets $62.219 Change the number of donut bones sold in the quarter to 19.000 and the balance she should bance with both Towels and Tour 5 Labdies & Equity equaling $262.457 5 Total score 40 9:25 ACCT 285 - Spring 2021-... NAME INPUTS Beg. Balance Sheet Sales & Collections Teler BO Les UM TO Total Cath Colections 9:26 ACCT 285 - Spring 2021 -... NAME INPUTS Beg. Balance Sheet Sales & Collections Production RUOLO ITAL 9:26 ACCT 285 - Spring 2021-... NAME INPUTS Beg. Balance Sheet Sales & Col 00001 Main Street Donuts 0000 Budgeted Income Statement 0000 For the Year Ended December 31, 2022 Sales Variable expenses Variable COGS Selling & administrative Contribution margin Fleed expenses: Manufacturing overhead Selling & administrative Net income 9:27 ACCT 285 - Spring 2021 -... NAME INPUTS Beg. Balance Sheet Sales & Collections Production 9:26 ACCT 285 - Spring 2021 -... NAME INPUTS Beg Balance Sheet Sales & Collections Production ROL LE Show