The purpose of this assignment is to demonstrate your ability to properly create and document a model, and subsequently use the model to do a sensitivity analysis.
Complete the following exercise:
Assume Smith Workstations charges $350 to build a standard cubicle for their clients. An order has come in from an insurance carrier to build out 150 cubicles. Build a model in Excel for the following scenarios:
- Determine the profitability of the order using the following information: Labor cost is $10.00 per hour per person, and it takes 2 labor hours to build a cubicle. The cost for materials is $250 for each cubicle. How much profit or loss will Smith Workstations make?
- Assume the same information as in the first scenario, but assume that productivity increases from 2 hours to 1.75 hours, and the cost for materials is $225. How much profit or loss will Smith Workstations make?
- Assume the same information as the second scenario, but the order increases from 150 cubicles to 225 cubicles. How much profit or loss will Smith Workstations make?
- Assume the same information as the third scenario, but the price drops to $325. How much profit or loss will Smith Workstations make?
The model should contain the following sections: Description, Inputs, Formula/Outputs.