VIEWS: 0 PAGES: 32 POSTED ON: 10/1/2012 Public Domain
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