Microsoft and Zero-one Guy
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
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