# 08 01Simulation by z8OBCl

VIEWS: 0 PAGES: 32

• pg 1
```									This Excel file includes the worksheets for 3 simulation projects.

The first project is "Demand Simulation." The name of the worksheets in this project are:
"Demand Simulation"
"Step 1 (Demand)"
"Step 2 (Demand)"
"Step 3 (Demand)"
"Step 4 (Demand)"

The second project is "Machine Breakdown." The name of the worksheet in this project is "Machin
No instruction is given. You are to use the same procedure you learned in Project 1 to co

The third project is "Capital Budgeting." The name of the worksheets in this project are:
"Demand Simulation"
"Step 1"
"Step 2"
"Step 3"
"Step 4"
"100 Replications"
"100 Replications (2)"
s in this project are:

heet in this project is "Machine Breakdown."
ou learned in Project 1 to complete this project.

in this project are:
Probability   Demand
10%           8
20%           9
30%          10
20%          11
10%          12
10%          13
Lower Bound            Step 1
Probability   of CDF      Demand   Insert a Column (Column B). Label it "Lower Bound of CDF"
10%                       8     CDF = Cumulative Distribution Function.
20%                       9     The lower bound starts at 0. Enter this in Cell B4.
30%                      10
20%                      11     Enter the formula for B5=B4+A4
10%                      12     Copy and paste thru B10.
10%                      13
-
. Label it "Lower Bound of CDF"
on Function.
Enter this in Cell B4.
Lower Bound                  Random            Step 2
Probability   of CDF      Demand   Day   Number   Demand   To simulate the demand
10%         0.00          8      1    0.4344            generate 10 random nu
20%         0.10          9      2    0.7384
30%         0.30         10      3    0.6427            In Excel, this means usi
20%         0.60         11      4    0.4678            Note: RAND() is volatile
10%         0.80         12      5    0.2625
10%         0.90         13      6    0.4065
1.00          -      7    0.2160
8    0.2205
9    0.4031
10    0.6624
To simulate the demand for the next 10 days,
generate 10 random numbers (Between 0 and 1).

In Excel, this means using function RAND()
Note: RAND() is volatile.
Lower Bound                  Random            Step 3
Probability   of CDF      Demand   Day   Number   Demand   "Map" the generated ran
10%         0.00          8      1    0.9878     13
20%         0.10          9      2    0.7349     11     In Excel, this means usi
30%         0.30         10      3    0.8337     12     See figure below.
20%         0.60         11      4    0.3192     10
10%         0.80         12      5    0.9431     13
10%         0.90         13      6    0.1113     9
1.00          -      7    0.6795     11
8    0.8048     12
9    0.0741     8
10    0.8020     12
"Map" the generated random numbers to demand.

In Excel, this means using function VLOOKUP
See figure below.
Lower Bound                   Random            Step 4
Probability   of CDF      Demand   Day    Number   Demand   Notice that the simulate
10%         0.00          8      1     0.5145     10
20%         0.10          9      2     0.5572     10     Now, let us replicate the
30%         0.30         10      3     0.4060     10     The average demand fo
20%         0.60         11      4     0.9410     13
10%         0.80         12      5     0.7426     11     Next, highlight Columns
10%         0.90         13      6     0.6441     11     Click Data, Choose Tabl
1.00          -      7     0.7993     11     For the "Column input c
8     0.2090     9
9     0.4138     10       Run
10     0.6496     11         1
Average    10.6        2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Take the average demand for 10 days
Notice that the simulated demand is volatile.

Now, let us replicate the simulation for 50 runs.
The average demand for Run #1 (Cell J13) = G14

Next, highlight Columns I13:J16.
Click Data, Choose Table
For the "Column input cell" box, click on the cell to the right of the simulated average demand for Run #1 (Cell K13).

Average Demand
11
10.2
10.2
10.3
10.4
10
10.3
10.8
10.7
10.6
10.2
10
10
10.5
10.7
10.2
10.5
11
10.6
10.2
10.8
10.8
11
10.3
10.3
10.7
11.1
10.4
10.2
9.7
10.4
10.8
10.6
10.1
10.5
10.3
10.5
10.2
10.4
10.8
10.7
11
10.3
10.1
10.6
10.3
11.2
10.4
10.9
10
mand for Run #1 (Cell K13).
Now, try this problem …
We are to simulate the number of machine breakdowns for the next 10 days.
The probability distribution is given below:

Lower Bound Number of                  Random    Simulated Number
Probability     of CDF    Breakdowns          Day    Number    of Breakdowns
0.10                        0               1
0.30                        1               2
0.25                        2               3
0.20                        3               4
0.10                        4               5
0.05                        5               6
-               7
8
9
10
Assumptions
Startup Costs                                   \$     150,000 Variable Costs    75%
Selling Price                                   \$      35,000 Cost of Capital   10%
Fixed Costs                                     \$      15,000 Tax Rate          34%
Depreciation/Yr                                 \$      10,000
Demand/Yr         10.0

The problem is to simulate the NPV (assuming 4 year economic life).
of Revenue

units
Assumptions
Startup Costs                                       \$150,000 Variable Costs      75%
Selling Price                                        \$35,000 Cost of Capital     10%
Fixed Costs                                          \$15,000 Tax Rate            34%
Depreciation/Yr                                      \$10,000
Demand/Yr           10.0

Year 0             1        2
Demand                                                                    10       10
Revenue = Selling Price x Demand
Fixed Cost                                                           15,000    15,000
Variable Cost = 75% of Revenue
Depreciation                                                         10,000    10,000
Profit Before Tax = Revenue-Fixed,Variable & Depreciation
Tax = 34% of Profit Before Tax
Profit After Tax = Profit Before Tax - Tax
Net Cash Flow = Profit After Tax + Depreciation      (150,000)

Net Present Value

Step 1.
Assuming demand is given, find the NPV.
NPV = year 0 cash flow + sum of discounted cash flow from year 1 thru year 4.
of Revenue

units

3        4
10       10

15,000    15,000

10,000    10,000
Lower Bound                 Assumptions
Probability   of CDF      Demand        Startup Costs       \$   150,000 Variable Costs
10%                       8          Selling Price       \$    35,000 Cost of Capital
20%                       9          Fixed Costs         \$    15,000 Tax Rate
30%                      10          Depreciation/Yr     \$    10,000
20%                      11                                          Demand/Yr
10%                      12
10%                      13                              Year 0                    1
-          Demand                                       10
Revenue                                350,000
Fixed Cost                              15,000
Variable Cost                          262,500
Depreciation                            10,000
Profit before Tax                       62,500
Tax                                     21,250
Profit after Tax                        41,250
Net Cash Flow           (150,000)       51,250

Net Present Value    \$12,455.60

Step 2.
Insert the demand distribution table.
75% of Revenue
10%
34%

10.0 units

2          3          4
10         10         10
350,000    350,000    350,000
15,000     15,000     15,000
262,500    262,500    262,500
10,000     10,000     10,000
62,500     62,500     62,500
21,250     21,250     21,250
41,250     41,250     41,250
51,250     51,250     51,250
Lower Bound                  Assumptions
Probability   of CDF       Demand        Startup Costs       \$   150,000 Variable Costs
10%         0.00           8          Selling Price       \$    35,000 Cost of Capital
20%         0.10           9          Fixed Costs         \$    15,000 Tax Rate
30%         0.30          10          Depreciation/Yr     \$    10,000
20%         0.60          11                                          Demand/Yr
10%         0.80          12
10%         0.90          13                              Year 0                    1
1.00           -          Demand                                       10
Revenue                                350,000
Note: B5=B4+A4                           Fixed Cost                              15,000
Column B is the lower bound of the CDF   Variable Cost                          262,500
Depreciation                            10,000
Profit before Tax                       62,500
Tax                                     21,250
Profit after Tax                        41,250
Net Cash Flow           (150,000)       51,250

Net Present Value    \$12,455.60

Step 3. Replace the constant demand (10).
This means using VLOOKUP to simulate the demand.
Note that we can use RAND() directly in VLOOKUP
75% of Revenue
10%
34%

10.0 units

2             3          4
10            10         10
350,000       350,000    350,000
15,000        15,000     15,000
262,500       262,500    262,500
10,000        10,000     10,000
62,500        62,500     62,500
21,250        21,250     21,250
41,250        41,250     41,250
51,250        51,250     51,250

ate the demand.
y in VLOOKUP
Lower Bound                  Assumptions
Probability   of CDF       Demand        Startup Costs       \$    150,000 Variable Costs
10%         0.00           8          Selling Price       \$     35,000 Cost of Capital
20%         0.10           9          Fixed Costs         \$     15,000 Tax Rate
30%         0.30          10          Depreciation/Yr     \$     10,000
20%         0.60          11                                           Demand/Yr
10%         0.80          12
10%         0.90          13                              Year 0                     1
1.00           -          Demand                                        10
Revenue                                 350,000
Note: B5=B4+A4                           Fixed Cost                               15,000
Column B is the lower bound of the CDF   Variable Cost                           262,500
Depreciation                             10,000
Profit before Tax                        62,500
Tax                                      21,250
Profit after Tax                         41,250
Net Cash Flow           (150,000)        51,250

Net Present Value       \$9,339.53

Step 4. Done.
75% of Revenue
10%
34%

10.0 units

2          3          4
11         10          8
385,000    350,000    280,000
15,000     15,000     15,000
288,750    262,500    210,000
10,000     10,000     10,000
71,250     62,500     45,000
24,225     21,250     15,300
47,025     41,250     29,700
57,025     51,250     39,700
Trial No.     NPV    Step 5.
1    9,340   Now, we are going to use Data Tables to replicate the simulation for
2     -166
3   35,968   Enter the input column (Column A) as shown.
4   21,607   The NPV for the first trial is the NPV we simulated in Step 4.
5   22,356
6   12,495   Here's one way to do this:
7   15,449   On this worksheet, click B2.
8   28,584   Enter the equal sign "="
9   42,078   Now, click on the TAB for the previous worksheet (i.e., Step 4.)
10   16,049   Click on the NPV on that worksheet (the worksheet for Step 4.)
11   29,105   Hit Enter.
12   26,423
13   13,367   Now, highlight the entire table (i.e., Cells A2 thru B101)
14   15,449   Click Data, Choose Table.
15   38,134   Click on the cell to the right of the first NPV for the "Column input ce
16   25,117
17   16,755
18   -5,850
19    6,772
20   25,117
21   12,456
22   13,016
23   10,771
24   17,745
25     -995   A smart Cal Poly student should have no difficulty doing this!
26   11,541
27   16,751
28   16,400
29   21,962
30   22,561
31   19,082
32    3,305
33     -995
34   19,993
35   12,850
36   39,479
37   26,383
38   21,528
39    4,172
40    1,956
41   10,633
42   24,766
43   14,223
44   29,495
45   17,745
46   24,644
47   12,889
48   19,055
49    6,377
50   31,590
51   21,133
52    1,956
53   13,367
54   16,361
55   16,278
56   34,706
57   21,177
58   17,228
59   -2,383
60   36,094
61   26,778
62   17,579
63   12,061
64   19,528
65   11,544
66   13,241
67   -1,989
68   25,555
69   15,445
70   18,660
71   33,400
72   14,629
73   23,378
74    2,823
75   12,456
76   21,173
77   24,683
78   49,462
79   54,751
80   28,628
81   24,600
82     -640
83    6,811
84    6,855
85    8,117
86   21,173
87   39,001
88   36,489
89   45,123
90   21,611
91    4,216
92   31,712
93   18,956
94    2,910
95   18,057
96   21,090
97   19,871
98   17,185
99   26,805
100   31,629
plicate the simulation for 100 runs.

ulated in Step 4.

sheet (i.e., Step 4.)
rksheet for Step 4.)

2 thru B101)

for the "Column input cell" box.

ficulty doing this!
Trial No.      NPV    Step 6.
1    10,377   This step is necessary because the simulated NPV is volatile.
2   -11,929   To lock in the value, copy the entire NPV column, and "paste special
3    32,418   Now, we are read to do some statistical summary report.
4    21,650   Click Tools, choose Data Analysis, then choose descriptive statistics
5    11,627   The Input Range is the NPV column.
6    20,301   The Labels in first row box is checked because we included the label
7    -5,850   The "Output options" is to tell Excel where to put the statistical sum
8    27,338
9    14,700                   NPV
10    12,807
11    12,456   Mean                      16272.12929
12    16,794   Standard Error            1310.724426
13     9,422   Median                    15291.62967
14    23,768   Mode                      11627.27956
15    11,588   Standard Deviation        13107.24426
16     4,606   Sample Variance           171799852.1
17    49,150   Kurtosis                  0.089249409
18    -1,989   Skewness                  0.290980063
19     8,077   Range                     67074.56799
20    55,146   Minimum                   -11928.6934
21    26,734   Maximum                    55145.8746
22    28,628   Sum                       1627212.929
23    39,873   Count                             100
24     3,778   Confidence Level(95.0%)    2600.76209
25    18,139
26    -5,850
27    15,094
28    26,383
29    11,627
30      -683
31     8,594
32     4,172
33    19,516
34     2,350
35    31,195
36    25,594
37      -727
38    18,183
39    29,062
40    30,679
41    40,394
42    17,311
43     9,383
44    10,728
45    15,055
46     7,991
47     2,433
48    11,584
49    36,923
50    10,756
51    17,662
52    12,538
53    16,400
54    33,921
55    25,034
56      -600
57    18,562
58    33,834
59     6,855
60    30,718
61    -2,383
62    12,022
63    13,241
64     5,084
65    20,427
66    15,489
67     1,605
68    18,057
69    18,139
70    22,561
71    25,074
72    16,361
73    26,340
74    16,443
75     5,912
76     6,772
77    35,574
78    28,206
79    17,623
80    25,949
81    33,846
82    25,161
83    39,999
84     5,466
85     5,155
86   -10,229
87     2,867
88     9,422
89     4,133
90    20,305
91    22,956
92    14,266
93    10,728
94   -11,495
95    11,205
96    -1,429
97    12,061
98    35,144
99    22,088
100    34,789
ated NPV is volatile.
column, and "paste special" as "values."
summary report.
hoose descriptive statistics.

cause we included the label NPV.
re to put the statistical summary

```
To top