Manufacturing overhead is 90,000.
G Excel File Edit View Insert Format Tools Data Window Window Help AutoSave OFF DO ACC 105 070 online Module 4 Project 1 -Saved to my Mac Home Insert Page Layout Formulas Data Review View 7% O Tue 10:45 PM Q QE Q- Search Sheet Share Insert AY - Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste U- BIU A % Merge & Conter .co > 0 .00 Conditional Format Formatting as Tabla Cell Styles Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates G23 fx A B D F F G H J 1 M N R 1 2 PROJECT 1: MASTER BUDGET 4 5 The real-world (this company is in the northwest suburbs of Chicago) scenario: 6 Your company assembles wire (light-metal closet Shelving units at a manufacturing faciity and 7 ships them to home builders and chain 'big bax" do-it-yourself stores. 9 It is October, 2021 and you the budget manager 10 just received an e-mail from the CFO, asking you to put together a 2022 budget 11 12 The projected because the year is not yet over) 2021 financial information is below. 13 There are three items for you to complete by filing in the applicable personalized information. 14 Then you need to prepare a 2022 budget by filling in the 2022 boxes tek and submitting to the CFO, 16 So how to get that done? 17 For a successful Master Budget you would seek information and input from your company's 18 Sales Manager 19 Production Manager 20 Administration Manager 21 This information (reflecting some real-world vagueness and uncertainty) is in the detail further below. 22 2021 Projected 2022 Budget $ 23 Sales 24 2,400,000 25 Production Costs: 26 Direct Material 630.000 27 Direct Labor 420.000 Ivou llin 28 Manufacturing Overhead 29 Project 1 Master Budget Ready + 100% 14704 E X PEW 23 G Excel File Edit View Insert Format Tools Data Window Window Help AutoSave OFF DO ACC 105 070 online Module 4 Project 1 -Saved to my Mac Home Insert Page Layout Formulas Data Review View 7% O Tue 10:45 PM Q E Q- Search Sheet Share Insert AY - Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste BIU A $ - % Merge & Conter > 0 .00 .co Conditional Format Formatting as Table Cell Styles Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 x fx A B c D F F H 1 J 1 M N P R 28 Manufacturing Overhead 29 (you fiin) 30 Other Expenses 31 Sales commissions 480.000 32 Office Mgmt. Salaries 325,000 33 Rent 156,000 9,000 34 Utities 35 Supplies 25.000 36 Al Other 60.000 37 38 Total costs and expenses (you fill in) 39 40 Net Income (you felin) 41 42 Budget input from key personnel: 44 SALES MANAGER 45 46 The Sales Manager is in charge of sales of Wire Shelving in linear feet (the key metric for this business). 47 She spicits and receive orders from home builders and chain stores 48 49 The following is projected sales data for (full year) 2021: 50 61 60,000 linear feel to the home builders market at a sales price of $10 per linear faol Project 1 Master Budget + + Ready E 100% 14704 23 A X PEW G Excel File Edit View AutoSave OFF Home Insert Page Layout Insert Format Tools Data Window Help ACC 105 070 online Module 4 Project 1 -Saved to my Mac Formulas Data Review View 7% O Tue 10:45 PM Q QE Q- Search Sheet Share Insert AY - Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste BI A Merge & Center $ - % > 0 .00 .co . Conditional Format Formatting as Table Cell Styles Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 x fx A D F F G B H 1 . 1 M N o P R 49 The following is projected sales data for (full year) 2021: 50 51 150,000 linear feet to the home buiders market at a sales price of $10 per inear foot. 52 60,000 linear feel to the chain store market at a sales price of $15 per linear foot. 53 54 In 2022. the home builders market is expected to improve ("alle") over 2021 55 56 However, COVID-19 had a favorable impact on the chain store market as more people 67 had more time to work on home improvement projects in 2020 and 2021. This is expected to 58 "ecline significantly in 2022 as people are tired of being at home and home improvement projects. 59 60 Sales prices (what is charged to the customers) per linear foot are planned to remain the same in 2022 61 62 Salespersons are compensated fully on commission (and the 2022 commission rate is the same as 2021) 63 G4 PRODUCTION MANAGER 65 66 The production manager is in charge of assembly of these units in the assembly 67 facility adjacent to the office, 68 89 Production at the company facility is based on the assembly of the shelving by the inear foot. 70 You assemble what you need to fill orders, since there is no space for unsold inventory. 71 72 The production manager is also responsible for procurement of the raw materials (metal). 73 You currently pay at a rate of $3 per Inear foot, but your supplier is planning a price increase 74 75 The production manager is also responsible for the assemblers that she directly supervises. 76 They are paid by production and $2 per lincar foot 77 78 The production manager earns an annual salary (which is the entire manufacturing overhead). 79 That salary (unique to each student in the class, evidently some are more generous than others!) 80s as follow Project 1 Master Budget + Ready 100% 14704 23 Pw G Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF DO ACC 105 070 online Module 4 Project 1 -Saved to my Mac Home Insert Page Layout Formulas Data Review View 7% O Tue 10:45 PM Q E Q- Search Sheet Share Insert AY - Delete A Format Sort & Filter Arial 10 - A- A- Wrap Text Number Paste BI U- A Merge & Center & - $ - % > 0 .00 .co . Cell Conditional Format Formatting as Table Styles Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates G23 H H 1 J 1 M N P R A B D F F 70 They are party couCor 3 per meal TUUL 77 78 The production manager carns an annual salary (which is the entire manufacturing overhead) 79 That salary (unique to each student in the class, evidently some are more generous than others! 80 is as follows: 81 Kassie: $53,000 22 John $55.000 83 Aldan $57 000 84 Mike: $50.000 85 Christian $62.000 86 Sakai: $84.000 Maggie: $86.000 88 Patty $ $58 000 89 WIH $70,000 90 Tori $72.000 91 Amber: $74.000 92 Viliam M $76.000 913 Marken $78 000 94 Jen $80.000 95 Hannah: $81.000 96 Natalia $82.000 97 Angela: $94.000 918 Awide: $95 000 99 Roberto: $86,000 100 Melissa $88.000 101 Marcus $89.000 102 Jonathan: $90.000 103 Sean: $92000 104 Jes. $95.000 105 108 However, in each of the salary scenarios above, the production manager wants a raise next year! 107 109 Reminder direct material and direct labor are both variable costs - An appropriate bucket wouki 109 be based on expected cost per inear foot produced Project 1 Master Budget + + Ready 100% 14704 16 23 A PE W G Excel File Edit View AutoSave OFF Home Insert Page Layout Insert Format Tools Data Window Help ACC 105 070 online Module 4 Project 1 -Saved to my Mac Formulas Data Review View 7% O Tue 10:45 PM Q QE Q- Search Sheet Share Insert Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste BI A A Merge & Center $ - % .co > 0 .00 Cell Conditional Format Formatting as Table Styles Check for Updates A E F H H 1 J M N P R Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 1 x fx B D G G The Two WCTT CE DONG. WIS Pranego w THISTICA 107 108 Reminder: direct material and direct labor are both variable costs - An appropriate budget would 109 be based on expected cost per inear foot produced. 110 111 112 ADMINISTRATION MANAGER 114 115 The administration manager is in charge of basically everything at your facility except sales 116 and production 117 118 A few of the issues she is working on include: 119 120 Negotiating with the landlord - your lease is up in December and she wants to increase the rent. 121 122 Hiring an administrative assistant to help implement the new accounts payable software. 123 124 Dealing with increase worker's comp insurance (in "Other") due to an 'accident'. 125 128 127 PROJECT 1 REQUIREMENTS: 128 129 Using either this Exonl document or separate format for example, a Word dacument, but NOT GoogleDocs or'.numbers') 130 prepare a 2022 Master Budeer by completing where applicable for 2021 and then fling in the boxes as applicable for 2022. 131 132 In addition provide a 200-300 word description 133 either within the same document or a separate document such as Word, or in "Write Submission" tab area, your choice!) 134 that specifies how you determined the 2022 Sales Budget for each of the two markets) and the assumptions, 135 as well as how you determined the numbers for all of the costs and expenses budgeted for 2022. 138 In other words, clearly show how you arrived at all of the numbers that you entered into the 2022 boxes, do NOT make 137 Instructor (or in the future your supervisor, company controler or CFO) go on an analytical safar to try to figure it out! 138 Frustrated CFOs who need further explanations on budget numbers is common at this time of year! 139 If using an Excel document, please make it in an easily printable (watch margin settings, etc.) format 140 Project 1 Master Budget + Ready 100% 14704 23 16 POW Insert Format Tools Data G Excel File Edit View AutoSave OFF Home Insert Page Layout Window Help ACC 105 070 online Module 4 Project 1 -Saved to my Mac Formulas Data Review View 7% O Tue 10:45 PM Q E Q- Search Sheet Share Insert AY Delete A Format Sort & Filter Arial 10 - A- A- Wrap Text Number Paste BI A Merge & Conter $ - % > 0 .00 .co . Conditional Format Formatting as Table Cell Styles Check for Updates F H. H . 1 M N R Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 XVfx A B c D F G 132 In addition, provide a 200-300 word description 132 (ther within the same document or a separte document such as Word, or in "Wie Submission tablarca your choice!) 134 that species how you detemined the 2022 Sales Budget Mor each of the two markets and the assumptions. 135 as well as how you determined the numbers for all of the casts and expenses budgeted for 2022. 136 In other words clearly show how you arted at all of the numbers that you entered into the 2022 boxes, do NOT make 137 instructor (or in the future, your supervisor, company controler or CFOI go on an analytical safari to try to figure it out! 129 (Frustrated CFOs who need further explanations on budget numbers is common at this time of yeart) 139 if using an Excel document, please make it in an easily printable watch margin settings, etc. fomat. 140 141 Submit as appropriate on Blackboard ("saving your documents ust in case s also recommended! 142 no later than Wednesday, March 24 at 11:59 PM NO extensions will be granted, a 0 score if missed 143 Do NOT email it! And be sure to not 'submit' unti truly ready to submit! 144 145 146 PROJECT 1 SCORING/GRADING: 147 149 In lieu of a detailed rubric, here is a scoring Overview 149 A completed budget with an appropriately detailed (200-300 word) description of the methodologies and the appropriate 150 elements of the master budget, the input from the key personnel; and course content, wel written, readable 151 the numbers are clearly supported (again not requinng an analytical safari to tigure out) and the math is corect 20 points 152 Minus 2 - 4 points for EACH instance of a math error and budget number not being appropriately supported or explained. 153 and items contrary to module content or key information given however, applicable interpretations are OK and expected!). 154 Minus 1 point for EACH IBBue with grammar, Speling, business writing etc Again, this should be CFO-submission worthy! 155 And once again, late submissions wil NOT be accepted (i.e., points). 156 157 Reminder: This is an individual project! Projects that appear to be much the same as other submissions wil be subject 159 to further review and very likely, a score 159 180 Scores and feedback will be posted by Monday, March 29 (and perhaps before that) 161 162 Finally, again, this is intended to be a real-world leaming experience, worthy of mentioning in an interview 163 ("Da you have hudgeting expericnon?" is a common question) and maybe just maybe it could be fun! 164 185 166 1 167 Project 1 Master Budget Ready + E 100% 14704 23 16 X1P IN G Excel File Edit View Insert Format Tools Data Window Window Help AutoSave OFF DO ACC 105 070 online Module 4 Project 1 -Saved to my Mac Home Insert Page Layout Formulas Data Review View 7% O Tue 10:45 PM Q QE Q- Search Sheet Share Insert AY - Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste U- BIU A % Merge & Conter .co > 0 .00 Conditional Format Formatting as Tabla Cell Styles Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates G23 fx A B D F F G H J 1 M N R 1 2 PROJECT 1: MASTER BUDGET 4 5 The real-world (this company is in the northwest suburbs of Chicago) scenario: 6 Your company assembles wire (light-metal closet Shelving units at a manufacturing faciity and 7 ships them to home builders and chain 'big bax" do-it-yourself stores. 9 It is October, 2021 and you the budget manager 10 just received an e-mail from the CFO, asking you to put together a 2022 budget 11 12 The projected because the year is not yet over) 2021 financial information is below. 13 There are three items for you to complete by filing in the applicable personalized information. 14 Then you need to prepare a 2022 budget by filling in the 2022 boxes tek and submitting to the CFO, 16 So how to get that done? 17 For a successful Master Budget you would seek information and input from your company's 18 Sales Manager 19 Production Manager 20 Administration Manager 21 This information (reflecting some real-world vagueness and uncertainty) is in the detail further below. 22 2021 Projected 2022 Budget $ 23 Sales 24 2,400,000 25 Production Costs: 26 Direct Material 630.000 27 Direct Labor 420.000 Ivou llin 28 Manufacturing Overhead 29 Project 1 Master Budget Ready + 100% 14704 E X PEW 23 G Excel File Edit View Insert Format Tools Data Window Window Help AutoSave OFF DO ACC 105 070 online Module 4 Project 1 -Saved to my Mac Home Insert Page Layout Formulas Data Review View 7% O Tue 10:45 PM Q E Q- Search Sheet Share Insert AY - Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste BIU A $ - % Merge & Conter > 0 .00 .co Conditional Format Formatting as Table Cell Styles Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 x fx A B c D F F H 1 J 1 M N P R 28 Manufacturing Overhead 29 (you fiin) 30 Other Expenses 31 Sales commissions 480.000 32 Office Mgmt. Salaries 325,000 33 Rent 156,000 9,000 34 Utities 35 Supplies 25.000 36 Al Other 60.000 37 38 Total costs and expenses (you fill in) 39 40 Net Income (you felin) 41 42 Budget input from key personnel: 44 SALES MANAGER 45 46 The Sales Manager is in charge of sales of Wire Shelving in linear feet (the key metric for this business). 47 She spicits and receive orders from home builders and chain stores 48 49 The following is projected sales data for (full year) 2021: 50 61 60,000 linear feel to the home builders market at a sales price of $10 per linear faol Project 1 Master Budget + + Ready E 100% 14704 23 A X PEW G Excel File Edit View AutoSave OFF Home Insert Page Layout Insert Format Tools Data Window Help ACC 105 070 online Module 4 Project 1 -Saved to my Mac Formulas Data Review View 7% O Tue 10:45 PM Q QE Q- Search Sheet Share Insert AY - Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste BI A Merge & Center $ - % > 0 .00 .co . Conditional Format Formatting as Table Cell Styles Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 x fx A D F F G B H 1 . 1 M N o P R 49 The following is projected sales data for (full year) 2021: 50 51 150,000 linear feet to the home buiders market at a sales price of $10 per inear foot. 52 60,000 linear feel to the chain store market at a sales price of $15 per linear foot. 53 54 In 2022. the home builders market is expected to improve ("alle") over 2021 55 56 However, COVID-19 had a favorable impact on the chain store market as more people 67 had more time to work on home improvement projects in 2020 and 2021. This is expected to 58 "ecline significantly in 2022 as people are tired of being at home and home improvement projects. 59 60 Sales prices (what is charged to the customers) per linear foot are planned to remain the same in 2022 61 62 Salespersons are compensated fully on commission (and the 2022 commission rate is the same as 2021) 63 G4 PRODUCTION MANAGER 65 66 The production manager is in charge of assembly of these units in the assembly 67 facility adjacent to the office, 68 89 Production at the company facility is based on the assembly of the shelving by the inear foot. 70 You assemble what you need to fill orders, since there is no space for unsold inventory. 71 72 The production manager is also responsible for procurement of the raw materials (metal). 73 You currently pay at a rate of $3 per Inear foot, but your supplier is planning a price increase 74 75 The production manager is also responsible for the assemblers that she directly supervises. 76 They are paid by production and $2 per lincar foot 77 78 The production manager earns an annual salary (which is the entire manufacturing overhead). 79 That salary (unique to each student in the class, evidently some are more generous than others!) 80s as follow Project 1 Master Budget + Ready 100% 14704 23 Pw G Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF DO ACC 105 070 online Module 4 Project 1 -Saved to my Mac Home Insert Page Layout Formulas Data Review View 7% O Tue 10:45 PM Q E Q- Search Sheet Share Insert AY - Delete A Format Sort & Filter Arial 10 - A- A- Wrap Text Number Paste BI U- A Merge & Center & - $ - % > 0 .00 .co . Cell Conditional Format Formatting as Table Styles Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates G23 H H 1 J 1 M N P R A B D F F 70 They are party couCor 3 per meal TUUL 77 78 The production manager carns an annual salary (which is the entire manufacturing overhead) 79 That salary (unique to each student in the class, evidently some are more generous than others! 80 is as follows: 81 Kassie: $53,000 22 John $55.000 83 Aldan $57 000 84 Mike: $50.000 85 Christian $62.000 86 Sakai: $84.000 Maggie: $86.000 88 Patty $ $58 000 89 WIH $70,000 90 Tori $72.000 91 Amber: $74.000 92 Viliam M $76.000 913 Marken $78 000 94 Jen $80.000 95 Hannah: $81.000 96 Natalia $82.000 97 Angela: $94.000 918 Awide: $95 000 99 Roberto: $86,000 100 Melissa $88.000 101 Marcus $89.000 102 Jonathan: $90.000 103 Sean: $92000 104 Jes. $95.000 105 108 However, in each of the salary scenarios above, the production manager wants a raise next year! 107 109 Reminder direct material and direct labor are both variable costs - An appropriate bucket wouki 109 be based on expected cost per inear foot produced Project 1 Master Budget + + Ready 100% 14704 16 23 A PE W G Excel File Edit View AutoSave OFF Home Insert Page Layout Insert Format Tools Data Window Help ACC 105 070 online Module 4 Project 1 -Saved to my Mac Formulas Data Review View 7% O Tue 10:45 PM Q QE Q- Search Sheet Share Insert Delete Sort & A Format Filter Arial 10 - A- A- Wrap Text Number Paste BI A A Merge & Center $ - % .co > 0 .00 Cell Conditional Format Formatting as Table Styles Check for Updates A E F H H 1 J M N P R Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 1 x fx B D G G The Two WCTT CE DONG. WIS Pranego w THISTICA 107 108 Reminder: direct material and direct labor are both variable costs - An appropriate budget would 109 be based on expected cost per inear foot produced. 110 111 112 ADMINISTRATION MANAGER 114 115 The administration manager is in charge of basically everything at your facility except sales 116 and production 117 118 A few of the issues she is working on include: 119 120 Negotiating with the landlord - your lease is up in December and she wants to increase the rent. 121 122 Hiring an administrative assistant to help implement the new accounts payable software. 123 124 Dealing with increase worker's comp insurance (in "Other") due to an 'accident'. 125 128 127 PROJECT 1 REQUIREMENTS: 128 129 Using either this Exonl document or separate format for example, a Word dacument, but NOT GoogleDocs or'.numbers') 130 prepare a 2022 Master Budeer by completing where applicable for 2021 and then fling in the boxes as applicable for 2022. 131 132 In addition provide a 200-300 word description 133 either within the same document or a separate document such as Word, or in "Write Submission" tab area, your choice!) 134 that specifies how you determined the 2022 Sales Budget for each of the two markets) and the assumptions, 135 as well as how you determined the numbers for all of the costs and expenses budgeted for 2022. 138 In other words, clearly show how you arrived at all of the numbers that you entered into the 2022 boxes, do NOT make 137 Instructor (or in the future your supervisor, company controler or CFO) go on an analytical safar to try to figure it out! 138 Frustrated CFOs who need further explanations on budget numbers is common at this time of year! 139 If using an Excel document, please make it in an easily printable (watch margin settings, etc.) format 140 Project 1 Master Budget + Ready 100% 14704 23 16 POW Insert Format Tools Data G Excel File Edit View AutoSave OFF Home Insert Page Layout Window Help ACC 105 070 online Module 4 Project 1 -Saved to my Mac Formulas Data Review View 7% O Tue 10:45 PM Q E Q- Search Sheet Share Insert AY Delete A Format Sort & Filter Arial 10 - A- A- Wrap Text Number Paste BI A Merge & Conter $ - % > 0 .00 .co . Conditional Format Formatting as Table Cell Styles Check for Updates F H. H . 1 M N R Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G23 XVfx A B c D F G 132 In addition, provide a 200-300 word description 132 (ther within the same document or a separte document such as Word, or in "Wie Submission tablarca your choice!) 134 that species how you detemined the 2022 Sales Budget Mor each of the two markets and the assumptions. 135 as well as how you determined the numbers for all of the casts and expenses budgeted for 2022. 136 In other words clearly show how you arted at all of the numbers that you entered into the 2022 boxes, do NOT make 137 instructor (or in the future, your supervisor, company controler or CFOI go on an analytical safari to try to figure it out! 129 (Frustrated CFOs who need further explanations on budget numbers is common at this time of yeart) 139 if using an Excel document, please make it in an easily printable watch margin settings, etc. fomat. 140 141 Submit as appropriate on Blackboard ("saving your documents ust in case s also recommended! 142 no later than Wednesday, March 24 at 11:59 PM NO extensions will be granted, a 0 score if missed 143 Do NOT email it! And be sure to not 'submit' unti truly ready to submit! 144 145 146 PROJECT 1 SCORING/GRADING: 147 149 In lieu of a detailed rubric, here is a scoring Overview 149 A completed budget with an appropriately detailed (200-300 word) description of the methodologies and the appropriate 150 elements of the master budget, the input from the key personnel; and course content, wel written, readable 151 the numbers are clearly supported (again not requinng an analytical safari to tigure out) and the math is corect 20 points 152 Minus 2 - 4 points for EACH instance of a math error and budget number not being appropriately supported or explained. 153 and items contrary to module content or key information given however, applicable interpretations are OK and expected!). 154 Minus 1 point for EACH IBBue with grammar, Speling, business writing etc Again, this should be CFO-submission worthy! 155 And once again, late submissions wil NOT be accepted (i.e., points). 156 157 Reminder: This is an individual project! Projects that appear to be much the same as other submissions wil be subject 159 to further review and very likely, a score 159 180 Scores and feedback will be posted by Monday, March 29 (and perhaps before that) 161 162 Finally, again, this is intended to be a real-world leaming experience, worthy of mentioning in an interview 163 ("Da you have hudgeting expericnon?" is a common question) and maybe just maybe it could be fun! 164 185 166 1 167 Project 1 Master Budget Ready + E 100% 14704 23 16 X1P IN
