PROJECT 1 REQUIREMENTS:
Using either this Excel document or a separate format (for example, a Word document, but not Googledocs!), prepare a
2021 Master Budget by completing where applicable for 2020 and then filling in the boxes as applicable for 2021.
In addition, provide a 200-300 word description
(either within the same document or a separate document such as Word, or in "Write Submission" tab/area, your choice)
that specifies how you determined the 2021 Sales Budget (for each of the two markets) and the assumptions,
as well as how you determined the numbers for all of the costs and expenses budgeted for 2021.
In other words, clearly show how you arrived at all of the numbers that you entered into the 2021 boxes

The real world scenario Your company assembles wire (light-metal) closet Shelving units at a manufacturing facility and ships them to home builders and big box" do it yourself chains. It is October, 2020 and you, the budget manager, just received an e-mail from the CFO, asking you to put together a 2021 budget The projected (because the year is not yet over) 2020 financial information is below. Now you need to prepare a 2021 budget by fing in the 2021 boxes below and submitting to the CFO. So what to fill in for 2021 (after completing the 2020 income statement)? For a successful Master Budget, you would seek information and input from your company's Sales Manager Production Manager Administration Manager This information (reflecting some real world vagueness and uncertainty) is in the detail further below. 2020 Projected 2021 Budget Sales $ 2200,000 Production Costs Direct Material 630,000 Direct Labor 420,000 Manufacturing Overhead (you fiin) Other Expenses Sales commissions 440,000 Office Mont Salaries Rent Uties 325,000 144,000 9,000 25,000 60,000 Supplies Al Other Total costs and expenses (you fiin) Net Income (you fiin) Budget input from key personnel: SALES MANAGER The Sales Manager is in charge of sales of Wire Shelving in near feet (the key metric for this business) She solicits and receive orders from home builders and chain stores The following is projected sales data for (ful year) 2020 190.000 near feet to the home builders at a sales price of $10 per linear foot 20,000 near feet to the chain store market at a sales price of $15 per hear foot For 2021, a continued "skump", "challenge", etc. in the builders market is expected, so the linear feet sales should decrease slightly However, the chain store market is expected to show significant improvement Sales prices (what is charged to the customers) per near foot will remain the same in 2021. PRODUCTION MANAGER The production manager is in charge of assembly of these units in the assembly facility adjacent to the office. Production at the comparry facity is based on the assembly of the shelving by the near foot You assemble what you need to fl orders, since there is no space for unsold inventory. The production manager is also responsble for procurement of the raw materials (metal) You currently pay at a rate of $3 per near foot, but your supplier is planning a price increase The production manager is also responsible for the assemblers that he directly supervises. They are paid by production at $2 per near foot The production manager eas an annual salary which is the entire manufactuing overhead) That salary (unique to each student in the class, evidently some are more generous than others!) is as follows: $77,000 However, in all of the salary scenarios above, the production manager wants a raise next year! Reminder direct material and direct labor are both variable costs - An appropriate budget would be based on expected cost per near foot x the number of expected near feet in sales (based on the input from the sales manager) ADMINISTRATION MANAGER The administration manager is in charge of basically everything at your facility except sales and production A couple of the issues she is working on include: Negotiating with the landlord - your lease is up in December and he wants to increase the rent Hring an administrative assistant to help implement the new accounts payable software. PROJECT 1 REQURERENTS Using either this Excel document or a separate format for example, a Word document, but not Googledocs!). prepare a 2021 Master Budget by completing where applicable for 2020 and then ng in the boxes as applicable for 2021. n annon, provide a A IM Word Oeschpoon (either within the same document or a separate document such as Word, or in "Write Submission tab/area, your choice) that specifies how you determined the 2021 Sales Budget for each of the two markets) and the assumptions, as well as how you determined the numbers for all of the costs and expenses budgeted for 2121 h other words, dearly show how you anived at al of the numbers that you entered into the 2021 boxes fusing an Excel document, please make it in an easily printable (watch margin settings, etc.) format. PROJECT 1 SCORIGIGRADING- In lieu of a detailed rubric, here is a scoring overview A completed budget with an appropriately detailed (200-300 word) description of the methodologies and the appropriate elements of the master budget, the input from the key personnel, and course content, wel witten, readable the numbers are clearly supported (again not requiring an analytical safari to fgure out) and the math is comect: 20 points Minus 2 - 4 points for EACH instance of a math emor and budget number not being appropriately supported or explained. and items contrary to module content or key information given (however, applicable interpretations are expected.). Minus 1 point for EACH issue with grammar, spelling, syntax etc. (Again, this should be CFO-submission worthry! be subject Reminder. This is an individual activity. Projects that appear to be much the same as other submissions w to further review and very kely, a score. And worse. fusing an Excel document, please make it in an easily printable (watch margin settings, etc.) format