The real-world (this company does exist; it is in the northwest suburbs of Chica

ID: 2805521 • Letter: T

Question

The real-world (this company does exist; it is in the northwest suburbs of Chicago) situation: 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, 2017 and you, the budget manager… just received an e-mail from the CFO, asking you to put together a 2018 budget. The projected 2017 financial information is below. Now you need to prepare a 2018 budget by filling in the boxes below and submitting to the CFO. So what to fill in? Using the sequence of events in a Master Budget, you should 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: 2017 Projected 2018 Budget Sales $2,200,000 Cost of Sales: Direct Material 630,000 Direct Labor 420,000 Manufacturing Overhead 80,000 Other Expenses: Sales commission (fully based on sales) 440,000 Office/Mgmt Salaries 300,000 Rent 120,000 Utilities 6,000 Supplies 25,000 All Other 56,000 Total expenses 2,077,000 Net Income 123,000 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) 2017: 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 2018, a continued "slump" in the builders market is expected, but the chain store market is expected to improve. Sales prices per linear foot will remain the same in 2018. 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 $80,000 salary (which is manufacturing overhead) and wants a raise in pay. 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 of your choice (for example, a Word document), prepare a 2018 Master Budget by filling in the boxes as applicable for 2018. In addition, provide a 200-300 word description of (either within the same document or a separate document, or in "Write Submission" tab/area, your choice) the important methodologies and logic you used to come up with those numbers; this should be consistent with both the information provided by the personnel and the master budget content in our textbook. Submit as appropriate on Blackboard (I would also recommend "saving" your documents just in case!) no later than October 12 at 11:59 PM PROJECT 1 SCORING/GRADING: In lieu of a detailed rubric, here are the best and worse case scenarios: 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 and the math is correct (I will be checking!): 25 points A budget that appears just thrown together with minimal or no description of the methodologies; 5 points Anything in-between the two scenarios above will be scored accordingly. Late submissions will NOT be accepted (i.e., 0 points) Reminder: This is an individual activity. Projects that appear to be the same as other submissions will be subject to further review. An overall summary of project results will be posted after the project due date.

Explanation / Answer

Ans:

Budgeted figures for 2018 are as under:

2017 Projected

2018 Budgeted

Sales

2200000

2230000

Cost of sales

Direct material

630000

667800

Direct Labor

420000

Manufacturing head

80000

84000

Other expenses

Sales Commission

440000

446000

Office/Management salaries

300000

315000

Rent

120000

126000

Utilities

6000

Supplies

25000

Other miscellaenous expenses

56000

Total Expenses

2077000

2145800

Net Income

123000

84200

Based on the information from the sales manager it is estimated that because of slump in the home builders market the sales would be stagnant at 190000 linear feet. While a growth of 10% is estimated in the sales to chain stores. So the sale is estimated at the level of $ 22000. It is considered that rates for both the markets are at the level of 2017.

Now, based on the input from the production manager an increase of 5% is estimated on the material cost and the material cost is taken at the rate of $3.15 per linear feet. Assemblers cost has been taken at the same level of $2 per linear feet. Further it is estimated that there would be an increase in the salary of production manager and it would be $84000 from $80000.

Based on the input from the administration manager, it is estimated that there would be an increase of 5% in the lease rent and it would be $ 126000 from $120000. Further, the cost on account of office/management salaries would be increased by $15000 as it is estimated that an administration assistant would be hired. All other parameters have considered at the same level. Sales commission is considered at 1% of sales turnover.

So considering the inputs from sales manager, production manager and the administration manager it is estimated the profit would come down to $84200 from $123000. This scenario would reverse when the company is able to improve the sales volume and the sale pricing.