Crystal Ball Data Workbook Variables Name: Value: Last Var Column Worksheet Data Last Data Column Used 0 2 Sheet Ref Err:509 Err:509 Sheet Guid 11e0deb8-0c08-4038-8e57-384db2d30d37 e7fbd26e-49dc-4086-aa43-576cf65ddcd7 Deleted sheet count Last row used 28 31 Data blocks CB_Block_0 CB_Block_0 Decisioneering:7.0.0.0 Decisioneering:7.0.0.0 CB_Block_7.0.0.0:1 Decisioneering:7.0.0.0 Hotel Overbooking Model Data Rooms available Price Overbooking cost 300 $120 $100 Reservation limit Customer demand Reservations made Cancellations Customer arrivals 310 312 310 6 304 Model Overbooked customers 4 Net revenue $35,600 Markdown Pricing Model Data Retail price Inventory Selling season (days) Days at full retail Intermediate markdown Clearance markdown Sales Data Price (X) $70 $49 emand function parameters Intercept Slope $70.00 1000 50 40 30% 70% Average Daily Sales (Y) 7.00 32.20 91 -1.2 Model Full Retail Sales Retail price Daily sales Days at retail price Units sold at retail $70.00 7.00 40 280 Retail revenue $19,600.00 Discount Sales Discount Discount price Daily sales Unit sold Clearance Sales Clearance price Units sold at clearance 30% $49.00 32.20 322 Discount revenue $15,778.00 $21.00 398 Clearance revenue $8,358.00 Total revenue $43,736.00 Moore Pharmaceuticals Data Market size Unit (monthly Rx) revenue $ Unit (monthly Rx) cost $ Discount rate 2,000,000 130.00 40.00 9% Project Costs R&D $ 700,000,000 Clinical Trials $ 150,000,000 Total Project Costs $ 850,000,000 Model Year Market growth factor Market size Market share growth rate Market share Sales 1 3 3.00% 2,121,800 20.00% 11.52% 244,431 4 3.00% 2,185,454 20.00% 13.82% 302,117 Annual Revenue $ 249,600,000 $ 308,505,600 $ 381,312,922 Annual Costs $ 76,800,000 $ 94,924,800 $ 117,327,053 Profit $ 172,800,000 $ 213,580,800 $ 263,985,869 $ 471,302,771 $ 145,016,237 $ 326,286,534 2,000,000 8.00% 160,000 2 3.00% 2,060,000 20.00% 9.60% 197,760 Cumulative Net Profit $ (677,200,000) $ (463,619,200) $ (199,633,331) $ 126,653,203 Net Present Value $ 185,404,860 5 3.00% 2,251,018 20.00% 16.59% 373,417 $ 582,530,225 $ 179,240,069 $ 403,290,156 $ 529,943,358 Model A B 1 Newsvendor Model 2 3 Data 4 Selling price $ 18.00 5 Cost $ 12.00 6 Discount price $ 9.00 7 8 9 Model 10 Demand 41 11 Purchase Quantity 44 12 13 Quantity Sold 41 14 Surplus Quantity 3 15 16 Profit $ 237.00 17 Page 1 Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Unit cost $175 Production volume 1500 Total manufacturing cost Total purchased cost $237,500 $262,500 Cost difference (Manufacture - Purchase) Best Decision -$25,000 Manufacture Purchased from supplier Model Pricing Decision Model Data Price $500.00 Sales Function Slope Sales Function Intercept Sales -2.9485 3240.9 1766.65 Model Total Revenue $883,325.00 A B C 1 Profit Model 2 3 Data 4 Unit Price $40.00 5 Unit Cost $24.00 6 Fixed Cost $400,000.00 7 Demand 50000 8 9 10 11 Model 12 Unit Price $40.00 13 Quantity Sold 40000 14 Revenue $1,600,000.00 15 16 Unit Cost $24.00 17 Quantity Produced 40000 18 Variable Cost $960,000.00 19 Fixed Cost $400,000.00 20 21 Profit $240,000.00 22 Retirement Plan Model Data Retirement contribution (% of salary) Employer match Annual salary increase Annual return on investment 8% 35% 3% 8% Model Employee Employer Age Salary Contribution Contribution Balance 22 $50,000 $4,000 $1,400 $5,400 23 $ 51,500 $4,120 $1,442 $11,394 24 $ 53,045 $4,244 $1,485 $18,034 25 $ 54,636 $4,371 $1,530 $25,378 26 $ 56,275 $4,502 $1,576 $33,486 27 $ 57,964 $4,637 $1,623 $42,425 28 $ 59,703 $4,776 $1,672 $52,267 29 $ 61,494 $4,919 $1,722 $63,089 30 $ 63,339 $5,067 $1,773 $74,977 31 $ 65,239 $5,219 $1,827 $88,021 32 $ 67,196 $5,376 $1,881 $102,320 33 $ 69,212 $5,537 $1,938 $117,980 34 $ 71,288 $5,703 $1,996 $135,118 35 $ 73,427 $5,874 $2,056 $153,857 36 $ 75,629 $6,050 $2,118 $174,334 37 $ 77,898 $6,232 $2,181 $196,694 38 $ 80,235 $6,419 $2,247 $221,094 39 $ 82,642 $6,611 $2,314 $247,707 40 $ 85,122 $6,810 $2,383 $276,717 41 $ 87,675 $7,014 $2,455 $308,323 42 $ 90,306 $7,224 $2,529 $342,742 43 $ 93,015 $7,441 $2,604 $380,207 44 $ 95,805 $7,664 $2,683 $420,971 45 $ 98,679 $7,894 $2,763 $465,306 46 $ 101,640 $8,131 $2,846 $513,507 47 $ 104,689 $8,375 $2,931 $565,894 48 $ 107,830 $8,626 $3,019 $622,811 49 $ 111,064 $8,885 $3,110 $684,631 50 $ 114,396 $9,152 $3,203 $751,757 Staffing Model Data Desired Throughput Hours Worked Per Day Days in Month May 700 6.5 22 June 750 6.5 20 July 800 6.5 22 August 825 6.5 22 Model May Products Product 1 Product 2 Product 3 Product 4 Product 5 Product 6 Product 7 Product 8 Product 9 Product 10 Misc Total Product Mix 22% 17% 13% 12% 9% 9% 6% 5% 3% 1% 3% 100% Hours Per File 3.50 2.00 1.50 5.50 4.00 3.00 2.00 2.00 1.50 3.50 3.00 FTEs Required Files/M onth 154 119 91 84 63 63 42 35 21 7 21 700 June Hours Files/M Hours Required onth Required 539.00 165.00 577.50 238.00 127.50 255.00 136.50 97.50 146.25 462.00 90.00 495.00 252.00 67.50 270.00 189.00 67.50 202.50 84.00 45.00 90.00 70.00 37.50 75.00 31.50 22.50 33.75 24.50 7.50 26.25 63.00 22.50 67.50 2089.50 750.00 2238.75 14.61 17.22 July August Files/M Hours Files/M Hours onth Required onth Required 176.00 616.00 181.50 635.25 136.00 272.00 140.25 280.50 104.00 156.00 107.25 160.88 96.00 528.00 99.00 544.50 72.00 288.00 74.25 297.00 72.00 216.00 74.25 222.75 48.00 96.00 49.50 99.00 40.00 80.00 41.25 82.50 24.00 36.00 24.75 37.13 8.00 28.00 8.25 28.88 24.00 72.00 24.75 74.25 800.00 2388.00 825.00 2462.63 16.70 17.22 #VALUE! Profit Model Data Unit Price Average concession Fixed Cost Demand $10.00 $15.00 $12,000.00 3000 Unit Price average concession Demand Total sales Total concession $10.00 $15.00 3000 Model $30,000.00 $45,000.00 80% of gate and concession sales Fixed Cost $60,000.00 $12,000.00 Profit $48,000.00 #VALUE! Profit Model Data Sales volume year 1 Sales growth rate Selling price per unit year 1 Selling price increase rate Variable price per unit year 1 Variable price increase rate Fixed price year 1 Fixed price growth rate 80,000 4.00% $12.00 $0.50 $3.00 5.00% $400,000.00 8% Sales volume year 1 Yearly Sales growth rate Selling price per unit year 1 Yearly Selling price increase rate Variable price per unit year 1 Yearly Variable price increase rate Fixed price year 1 Yearly Fixed price growth rate 80,000 4.00% $12.00 $0.50 $3.00 5.00% $400,000.00 8.00% YEAR 3 Sales volume Unit price Total sales 89,989 $13.50 Unit variable cost Total variable cost $3.47 Model $1,214,853.12 $312,520.97 Fixed Cost $503,884.80 Profit $398,447.35 Discount rate NPV of Profit 4.00% $354,218.25 #VALUE! Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Purchased from supplier Unit cost Production volume $175 0 75 Total manufacturing cost Total purchased cost $59,375 $13,125 erence (Manufacture - Purchase) Best Decision $46,250 Outsource Model Spin Button Pros Allows one to scroll through a give range of values Con Tedious to use when a large range of data is used. Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Unit cost Production volume $175 0 1546 Total manufacturing cost Total purchased cost $243,250 $270,550 Purchased from supplier Model Cost difference (Manufacture - Purchase) -$27,300 Best Decision Manufacture Schroll Button Pros Allows one to scroll through a give range of values Easy to use when a large range of data is used Better than the spin button ### 0 Data SEA 0 SEA 1 OAK 2 CAL 3 TEX 4 KC 5 MIN 6 CHI 7 MIL 8 DET 9 CLE 10 TOR 11 BAL 12 NY 13 BOS 0 678 954 1670 1489 1399 1720 1694 1932 2023 2124 2334 2421 2469 SEA OAK CAL TEX KC MIN CHI MIL DET CLE TOR BAL NY 2 3 4 5 OAK CAL TEX KC MIN 678 954 1670 1489 1399 0 337 1467 1498 1589 337 0 1246 1363 1536 1467 1246 0 460 853 1498 1363 460 0 394 1589 1536 853 394 0 1846 1745 798 403 334 1845 1756 843 483 297 2079 1979 982 630 528 2161 2053 1015 694 622 2286 2175 1186 822 780 2457 2329 1209 961 936 2586 2475 1383 1113 1028 2704 2611 1555 1254 1124 4 1 Select 1st city Select 2nd city Distance Between them 1 1670 6 7 8 9 10 11 12 13 CHI MIL DET CLE TOR BAL NY BOS 1720 1694 1932 2023 2124 2334 2421 2469 1846 1845 2079 2161 2286 2457 2586 2704 1745 1756 1979 2053 2175 2329 2475 2611 798 843 982 1015 1186 1209 1383 1555 403 483 630 694 822 961 1113 1254 334 297 528 622 780 936 1028 1124 0 74 235 307 430 621 740 867 74 0 237 328 434 641 746 860 235 237 0 95 206 409 509 632 307 328 95 0 193 314 425 563 430 434 206 193 0 384 366 463 621 641 409 314 384 0 184 370 740 746 509 425 366 184 0 187 867 860 632 563 463 370 187 0 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D E F #VALUE! Price Demand COST MODEL C = 5000 + 5D Total Cost Total Revenue TOTAL REVENUE Total Profit TOTAL PROFIT H I J K L Place the One-Way Data Table here DEMAND MODEL D = 2500 - 3P TOTAL COST G #VALUE! Expected Crowd Average Concessions Expenditure Fixed Cost Ticket Price REVENUE FROM TICKETS CONCESSION SALES PROFIT PERCENTAGE BAND PROFIT 4500 people $20.00 per person $8,500.00 $15.00 per person #VALUE! Expected Crowd Average Concessions Expenditure $ 3000 people 25.00 per person Fixed Cost $ 12,000.00 Ticket Price $ 15.00 per person REVENUE FROM TICKETS $ 45,000.00 CONCESSION SALES $ 75,000.00 PROFIT PERCENTAGE 80% BAND PROFIT $ 138,750.00 0 #VALUE! Increase Per Year 4% Year Sales Volume 5% 8% Selling Price Unit Var Cost Fixed Cost 1 2 3 NPV Discount Rate $0.50 4% Total Rev Total Costs Total Profit 0