08 01Simulation by z8OBCl

VIEWS: 0 PAGES: 32

									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.
                                         BTW, your answer will be different. Why?
   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