# Microsoft and Zero-one Guy

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...

Please join StudyMode to read the full document