PROJECT 1: MASTER BUDGET The real-world (this company does exist in the northwest suburbs of Chicago) 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, 2019 and you, the budget manager, just received an e-mail from the CFO, asking you to put together a 2020 budget. The projected (because the year is not yet over) 2019 financial information is below. Now you need to prepare a 2020 budget by filling in the boxes below and submitting to the CFO. So what to fill in for 2020 (after completing the 2019 income statement)? in a Master Budget, you would seek information and input from your company's: Sales Manager Production Manager Administration Manager This information (although reflecting real-world vagueness and uncertainty) is in the detail below: 2019 Projected 2020 Budget Sales $2,200,000 Production Costs: Direct Material 630,000 Direct Labor 420,000 Manufacturing Overhead (you fill in) Other Expenses: Sales commissions 440,000 Office/Mgmt Salaries 300,000 Rent 144,000 Utilities 6,000 Supplies 25,000 All Other 56,000 Total expenses (you fill in) Net Income (you fill in) Budget input from key personnel: SALES MANAGER The Sales Manager is in charge of sales of wire shelving in linear 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 (full year) 2019: 190,000 linear feet to the home builders at a sales price of $10 per linear foot. 20.000 linear feet to the chain store market at a sales price of $15 per linear foot. For 2020, a continued "slump", "challenge", etc. in the builders market is expected, but the chain store market is expected to improve. Sales prices (what is charged to the customers) per linear foot will remain the same in 2020. PRODUCTION MANAGER The production manager is in charge of assembly of these units in the assembly facility adjacent to the office. Production at the company facility is based on the assembly of the shelving by the linear foot. You assemble what you need to fill orders, since there is no space for unsold inventory. The production manager is also responsible for procurement of the raw materials (metal). You currently pay at a rate of $3 per linear 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 linear foot. The production manager earns an annual salary (which is the entire manufacturing overhead). That salary (unique to each student in the class!) is as follows: Hannah: $50,000 Ricardo: $53,000 Marcel: $57,000 Karen: $60,000 simone: $63,000 Tristin: $67,000 Oscar: $70,000 Ericka: $73,000 Sasha: $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 linear foot x the number of expected linear 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. Hiring an administrative assistant to help implement the new accounts payable software. Reminder: sales commissions are variable and based on a % of sales (in dollars) PROJECT 1 REQUIREMENTS: Using either this Excel document or a separate format (for example, a Word document, but not Googledocs!), prepare a 2020 Master Budget by completing where applicable for 2019 and then filling in the boxes as applicable for 2020. In addition, provide a 200-300 word description, either within the same document as long as it is findable and readable or (either within the same document or a separate document such as Word, or in "Write Submission" tab/area, your choice) details of how you determined the 2020 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 2020. In other words, clearly show how you arrived at all the numbers that you entered into the 2020 boxes, do NOT make instructor (or in the future, your supervisor, company controller or CFO) go on an analytical safari to try to figure it out! If using an Excel document, please make it in an easily printable format. Submit as appropriate on Blackboard ( "saving" your documents just in case is also recommended!) no later than Tuesday October 22 at 11:59 PM (absolutely NO extensions will be granted) Do NOT email it! PROJECT 1 SCORING/GRADING: 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, well written, readable the numbers are clearly supported (again not requiring an analytical safari to figure out) and the math is correct: 20 points Minus 2 - 4 points for EACH instance of a math error and budget number not being appropriately supported or explained. and items contrary to module content or key information given (however, applicable interpretations are fine!) . Minus 1 point for EACH issue with grammar, spelling, syntax etc. (Again, this should be CFO-submission worthy!) Again, late submissions will NOT be accepted (i.e., 0 points). Reminder: This is an individual activity. Projects that appear to be much the same as other submissions will be subject to further review and very likely, a 0 score. Scores and feedback will be posted by October 29.. Finally, again, this is intended to be a real-world learning experience, worthy of mentioning in an interview and maybe even fun!