Microsoft and Zero-one Guy

Topics: Microsoft, Spreadsheet, Output Pages: 5 (733 words) Published: February 23, 2014
Labs
Lab 1: Learning @Risk Basics
In this lab you will create and run a Sales spreadsheet model. Please take your time on this lab; even though it is easy, it is your one guided opportunity to learn how to use the @Risk software. Launch @Risk from the Start menu. Begin by building the following spreadsheet:

1
2

A
Sales Forecast

C

D

E

Sales Dollars in
Millions

3
4
5
6
7
8
9

B

Customers
Big Guy
Little Guy
New Guy
Zero-One Guy
Total Sales

This
Year
$25.8
$5.2
$0.0
$2.0

Next Year

The Year
After

In C9, enter the formula =SUM(C5:C8), in D9 enter =SUM(D5:D8), etc. We want to add risk analysis to next years’ sales forecasts. We will add uncertainty to the sales for each of 4 customers, depending on what we know. Read all of this before you start entering anything else into your spreadsheet.



We believe that sales to Big Guy may increase by as much as 8%, are most likely to increase by 2%, but could go down as much as 3%. This year’s sales were $25.8 million. To represent this situation, multiply the previous year’s sales by 1 + RiskTriang(0.03, 0.02, 0.08) to get the next year’s sales.



Sales to Little Guy are expected to remain the same as last year, but with an average deviation of 15% either way. This year’s sales were $5.2 million. To represent this situation,
model
next
year’s
sales
using
the
@Risk
distribution
RiskNormal(previous, 0.15*previous), where “previous” is the cell address of previous year’s sales, for example RiskNormal(C6, 0.15*C6).



We are guaranteed $1 million from New Guy. There is a 30% chance sales will be less than $2.5M; a 75% chance sales will be less than $5M; a 95% chance sales will be less than $6M; and $6.5M is the best possible. This year there were no sales this customer. Model the sales in each of the future years using the @Risk function RiskCumul(1,6.5,{2.5,5,6},{.3,.75,.95}).



Zero-One Guy either places an order for $2 million per year, or orders nothing at all. This year we did get an order for $2 million. Next year we think that there is a 60% chance he will order; the year after we think the chance is 85%.

We will present the details for entering Zero-One Guy; the others you should figure out on your own or with the help of the TA:
1. Remember that this customer either places an order for $2 million per year, or orders nothing at all.
a. In cell C8 enter 2 indicating this year’s total.
b. In cell D8 enter = 2*RiskDiscrete({0,1},{40,60}). This indicates that there is a 60% chance RiskDiscrete will return a 1, and a 40% chance it will return a 0; the result will be multiplied by 2 million.

c. In cell E8 enter =2*RiskDiscrete({0,1},{15,85}).
2. Now enter the appropriate formulas for the other customers. When you have finished, open a Word document and copy and paste your spreadsheet into the document. 3. Since we would like to look at trends, use control-click or shift-click to select cells C9-E9. bottom. You will be asked for a name;

Then press the @Risk Add Output
use Total Sales.

Note: Output cells can be selected and added one-at-a-time; they only need to be added as a group if you want to look at trends among the cells.
4. Press the Simulation Settings
button. Set the # Iterations to 500, the
Sampling Type to Monte Carlo, and the Random Generator Seed to Fixed.

5. Run the simulation by pressing the Start Simulation
button. When the simulation
completes, you will find yourself in the @Risk Results window. Use the Explorer pane on the bottom side of the window to navigate among the Outputs (and Inputs, if you like) for analysis.
6. Right-click on the E9 Output, and select Histogram
. Spend some time with the
resulting plot window, trying out the various tabs, sliders and formatting options. See if you can figure out the chance that our sales exceed $40 million. Copy and paste the histogram into your Word document, and type in your estimate of the probability that sales...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • a good one Essay
  • Segment Zero: A Serious Threat to Microsoft? Essay
  • Microsoft Essay
  • Microsoft Essay
  • Microsoft Essay
  • Essay on Microsoft and the Xbox One
  • No One Essay
  • The One Essay

Become a StudyMode Member

Sign Up - It's Free