See Attachment
Create a worksheet called “Capacity BEP” that will let a user enter revenue per unit of output, as well as Fixed
and variable cost per unit. From that, compute each machine’s break-even point and the volume at which the
two machines will be equal.
You’ll need to put appropriate formulas in the cells surrounded by dark lines and values in the cells that are
shaded. My sheet is below — You don’t actually need to shade and include boxes – I just added that to help. Be
sure that you never see an error or any other weird output, regardless of what a user enters (including
nothing).
HINT: On paper:
use variables for each input above, such as: r=revenue/unit; FA=Fixed cost for A; FB=Fixed cost for B;
vA=variable cost for A; vB=variable cost for B; q=quantity.
Using those variables, solve for the point at which costs or profit are equal. Use for the point of
indifference, but with cell references instead of variables.
Use the value for point of indifference and either the inputs for Mach A or B to determine Profit
PART 2: Excel chart for visualizing break-even points
Create a worksheet called “Graphing Break-Even”
a. Enter the values of all cells that do not have thick borders in columns A, B, & C. Use these values just so you
know your results work, but any reasonable values in these cells should result in correct output for the boldlined
cells.
b. Use values 0 and 3000 in columns D and E as start and end points for your independent variable (because two
points are all that you need to define a line).
c. Create an Excel line chart on which all intersections are clearly visible. Changing any of the values I have
shown in grey will be reflected in changes to the chart.
PART 3: Decision Theory
Create a worksheet named “Decision Theory” and refer to your class notes and spreadsheets you constructed as
part of independent work to properly complete the following:
d. Enter the values of all cells that do not have thick borders. Use these values just so you know your results
work, but any reasonable values in these cells should result in correct output for the bold-lined cells.
e. Create an appropriate formula for the dark-bordered cells for each of the five solution methods, such that
the formula can be copied down to the three cells below it without manually making further changes (i.e. use
absolute addressing). Note that you should NOT have “helper” cells, even for Minimax Regret.
f. Use conditional formatting to highlight the selected cell from the four shown for each method
g. Create an appropriate formula for each of the five cells in the “VALUE” row to show the value that would be
selected from the four above
h. Create an appropriate formula for each of the five cells in the “BEST ALTERNATIVE” row that would reference
the information above in the same column and look up the correct alternative from the first column.
i. Create a formula to compute EVPI, relative to the “Rational” approach, given the selected probability of SON1
(currently shown as 44%)
NOTES: Your spreadsheet should refer only to the cells equivalent to those below – you may not have “helper”
cells – the spreadsheet should be blank except for the items below.
You should be able to change the names of the alternatives, the values of the alternatives for both States of
nature, and the probability of SON1 and your sheet should correctly compute all values for those inputs.
If you come to office hours for help, bring your class notes and an electronic version of the worksheets you
0 comments