Accounting case study

profileMath81
example_-__case.xls
Home>Business & Finance homework help>Accounting homework help>Accounting case study

Shelby Base

Shelby Shelving
Product S Product LX
Price 1,800 2,100
Variable Costs:
Direct Materials 1,000 1,200
Stamping-Direct Labor 35 35
Forming-DL 60 90
Assembly-DL 80 175 85 210
Total Variable Direct Costs 1,175 1,410
Var Overhead / unit
Stamping 80 90
Forming 120 170
Assembly 165 365 185 445
Total Variable Costs 1,540 1,855
Contribution Margin 260 245
Number of units 400 1,400
Contribution 104,000 343,000 447000 Net Revenue
Assembly Fixed Overhead Hours* 165000
Stamping Fixed Overhead 0.3 0.3 540 125000
Forming Fixed Overhead 0.25 0.5 800 95000
Total Fixed Overhead 385000
Profit 62000
*Both available for up to 800 hrs each
Constraints
1) Most can produce of S is 1900 units/mo
2) Most can produce of LX is 1400 units/mo
3) Max hrs on forming machine is 800/mo
4) Max hrs on stamping machine is 800/mo

Shelby with spinners

Shelby Shelving
Product S Product LX
Price 1800 2100
Variable Costs:
DM 1000 1200
S-DL 35 35
F-DL 60 90
A-DL 80 175 85 210
Total Variable Direct Costs 1175 1410
Var Overhead / unit
S 80 90
F 120 170
A 165 365 185 445
Total Variable Costs 1540 1855
Contribution Margin 260 245
Number of units 420 1390
Contribution 109200 340550 449750
A Fixed Constraint Information Hours* 165000
S 0.3 0.3 543 125000
F 0.25 0.5 800 95000
Total Fixed 385000
Profit 64750
Base Case Profit 1900 1400 62000
*Max 800 hrs each per month

Shelby with spinners
Base Profits
New Profits
Profits
Profit Analysis

Shelby with Solver

Shelby Shelving
Product S Product LX
Price 1800 2100
Variable Costs:
DM 1000 1200
S-DL 35 35
F-DL 60 90
A-DL 80 175 85 210
Total Variable Direct Costs 1175 1410
Var Overhead / unit
S 80 90
F 120 170
A 165 365 185 445
Total Variable Costs 1540 1855
Contribution Margin 260 245
Number of units 1900 650
Contribution 494000 159250 653250
Assembly Fixed Constraint Information Hours* 165000
Stamping Fixed 0.3 0.3 765 125000
Forming Fixed 0.25 0.5 800 95000
Total Fixed 385000
Profit 268250
Base Case Profit 1900 1400 62000
*Max 800 hrs each per month

Shelby with Solver
Base Profits
New Profits
Profits
Profit Analysis

Solver Sensitivity Report

Microsoft Excel 15.31 Sensitivity Report
Worksheet: [DBA - ACCT760 Shelby Shelving case.xls]Shelby with Solver ACC760
Report Created: 2/21/17 10:58:08 PM
Variable Cells
Final Reduced
Cell Name Value Gradient
$C$16 Number of units 1900 137.5
$F$16 Number of units 650 0
Constraints
Final Lagrange
Cell Name Value Multiplier
$G$20 Stamping Fixed Hours* 765 0
$G$21 Forming Fixed Hours* 800 490
Lagrange Multiplier: For each additional hour above the 800 hr constraint
490 more contribution margin can be added. This is achieved by producing
2 more LX (can produce two per hour) which each have a contribution margin
of 245.