Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT
ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT
Complete the case by using the spreadsheet to determine your health care coverage strategy and by
documenting recommendations in a memorandum.
You want to model four oil price situations, which you call Probable, Possible, Worst Case, and
Optimistic:
￿
In the Probable situation, per-barrel oil prices escalate slightly in 2012
2014 to $100, $110, and
$120, respectively.
￿
In the Possible situation, per-barrel oil prices escalate significantly in 2012
2014 to $100, $125,
and $150, respectively.
￿
In the Worst Case situation, per-barrel oil prices escalate greatly in 2012
2014 to $100, $150,
and $200, respectively.
￿
In the Optimistic situation, per-barrel oil prices actually decrease in 2012
2014 to $100, $90,
and $80, respectively.
Within each of these four situations, you can either choose to pay or not pay for employee health care
coverage. The added choice results in the following eight scenarios:
1. Prob-No
This scenario uses the Probable oil price situation and assumes that you are not
paying for health care.
2. Prob-Yes
This scenario uses the Probable oil price situation and assumes that you are paying
for health care.
3. Poss-No
This scenario uses the Possible oil price situation and assumes that you are not paying
for health care.
4. Poss-Yes
This scenario uses the Possible oil price situation and assumes that you are paying for
health care.
5. Worst-No
This scenario uses the Worst Case oil price situation and assumes that you are not
paying for health care.
6. Worst-Yes
This scenario uses the Worst Case oil price situation and assumes that you are
paying for health care.
7. Opt-No
This scenario uses the Optimistic oil price situation and assumes that you are not
paying for health care.
8. Opt-Yes
This scenario uses the Optimistic oil price situation and assumes that you are paying
for health care.
You want answers to the following questions:
￿
If you pay for health care and oil prices increase, can you still make your profit goal in the
foreseeable future?
￿
If oil prices increase, can you make your profit goal only if you do not adopt health care?
￿
How much impact do oil prices actually have for you? Looking at 2014 net income, cash, debt,
and return on sales, how different are the Optimistic-Yes and Probable-Yes results? For that
matter, how much different are the Optimistic-Yes and Worst-Yes results?
￿
Is there a case to be made for paying for health care in 2012
2014, no matter what? Can the
case be made in terms of 2014 net income, cash, debt, and return on sales?
You will use your spreadsheet to gather data about these questions.
Assignment 2A: Using the Spreadsheet to Gather Data
You have built the spreadsheet to model the business situation. For each of the eight scenarios, you want to
know the 2014 net income after taxes, the 2014 end-of-year cash on hand, the 2014 end-of-year debt owed,
and the 2014 return on sales.
You will run
scenarios with the eight sets of input values using Scenario Manager. (See Tutorial
C for details on using Scenario Manager.) Set up the eight scenarios. Your instructor may ask you to use
conditional formatting to make sure that your input values are proper. (Note that you can enter
noncontiguous cell ranges in Scenario Manager, such as C19, D19, C20:F20.)
what-if
 
Search JabSto ::




Custom Search