The fixed overhead is distributed using activity-based costing principles. For example, at current production levels, the forming machine spends 100 hours on model S shelves and 700 hours on model LX shelves. The forming machine is used 800 hours of the month, of which 12.5% of the time is spent on model S shelves and 87.5% is spent on model LX shelves. The $95,000 of fixed overhead in the forming department is distributed as $11,875 (= 95,000 0.125) to model S shelves and $83,125 (= 95,000 0.875) to model LX shelves. The fixed overhead per unit of output is allocated as $29.69 (= 11,875/400) for model S and $59.38 (= 83,125/400) for model LX. In the calculation of the standard overhead cost, the fixed and variable costs are added together, so that the overhead cost for the forming department allocated to a model S shelf is $149.69 (= 29.69 + 120, shown in cell G20 rounded up to $150). Similarly, the overhead cost for the forming department allocated to a model LX shelf is $229.38 (= 59.38 + 170, shown in cell H20 rounded down to $229).

1. What are the best production plan for Shelby Shelving? What is the best profit the firm can achieve?
2. Who will you support between the engineer and the controller? Give a short verbal argument on why you do so.
3. Suppose Shelby wants to invest on their three departments capacity. Do some sensitivity analysis and answer the following questions.
   1. Does all departments capacity need to increase? Give the reason to support your claim.
   2. In general, which departments capacity should be considered the first to increase? Why?
4. Suppose there are some additional restrictions on the capacity expanding investment.
   • Shelby plans to borrow at most $1500 from the bank atannual rate of 12% for one month, to implement any possible capacity expanding.
   • It costs $150 to increase every one hour of capacity in forming department, and $100 for increasing every one hour of stamping capacity.
   • In assembly department, the capacity for Model S and Model LX can be easily converted, without any cost, between each other for any amount smaller than 20 units. But it costs $300 for any conversion beyond the first 20 units and for any unit of new capacity expanding.

Please answer the following questions.

1. If Shelby can only change one departments capacity, which department should be selected? And why?
2. If Shelby can change multiple departments capacity, could you propose a best investment plan? What is it?

. TO6. CaseData (1)-Excel (Product Activation Failed) Insert Page LayoutFormulasData Review View T Home Tell me what you want to do... Sign in Share s Cut AutoSum . A FillSort&t Find& Sort &Find & -Merge & Center . $ . % , 1.000 Conditional Format as Cell insert Delete Format tyra B 1 u E- 2-A. Format Painter Formatting Table Styles Filter Select Font Alignment Cells Editing R21 28 Relevant Input Information 29 1. Resource parameters Model S Model LX Available 31 Stamping Requirement 32 Forming Requirement (hr/unit 33 Assembly Capacity (unit) 1900 1400 35 2. Economic parameters 36 Fixed Cost $385,000 Model S Model LX $365.00 $445.00 $1,000.00 $1,200.00 $175.00 $210.00 38Variable Cost 39Direct Materials 40Direct Labor 41 General Variable Cost $1,540.00 $1,855.0 $1,800.00 $2,100.00 43 Unit Selling Price 45 Model Setup 47 Total Revenue 48 Total Variable Cost 49 Fixed Cost 50 Profit AccountingData Ready +90% 10:28 AM A/26/2019 Type here to search . TO6. CaseData (1)-Excel (Product Activation Failed) Insert Page LayoutFormulasData Review View T Home Tell me what you want to do... Sign in Share s Cut AutoSum . A FillSort&t Find& Sort &Find & -Merge & Center . $ . % , 1.000 Conditional Format as Cell insert Delete Format tyra B 1 u E- 2-A. Format Painter Formatting Table Styles Filter Select Font Alignment Cells Editing R21 28 Relevant Input Information 29 1. Resource parameters Model S Model LX Available 31 Stamping Requirement 32 Forming Requirement (hr/unit 33 Assembly Capacity (unit) 1900 1400 35 2. Economic parameters 36 Fixed Cost $385,000 Model S Model LX $365.00 $445.00 $1,000.00 $1,200.00 $175.00 $210.00 38Variable Cost 39Direct Materials 40Direct Labor 41 General Variable Cost $1,540.00 $1,855.0 $1,800.00 $2,100.00 43 Unit Selling Price 45 Model Setup 47 Total Revenue 48 Total Variable Cost 49 Fixed Cost 50 Profit AccountingData Ready +90% 10:28 AM A/26/2019 Type here to search