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 A 1 Profit Model 2 3 Data 4 Unit Price 5 Average concession 6 Fixed Cost 7 Demand 8 9 10 11 Model 12 Unit Price 13 average concession 14 Demand 15 Total sales 16 Total concession 17 18 80% of gate and concession sales 19 Fixed Cost 20 21 Profit 22 B C $10.00 $15.00 $12,000.00 3000 $10.00 $15.00 3000 $30,000.00 $45,000.00 $60,000.00 $12,000.00 $48,000.00 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 Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Unit cost $175 Production volume 75 Total manufacturing cost Total purchased cost $59,375 $13,125 Cost difference (Manufacture - Purchase) Best Decision $46,250 Outsource Purchased from supplier 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. Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Unit cost $175 Production volume 1546 Total manufacturing cost Total purchased cost $243,250 $270,550 Cost difference (Manufacture - Purchase) Best Decision -$27,300 Manufacture Purchased from supplier Model 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 OAK 0 678 954 1670 1489 1399 1720 1694 1932 2023 2124 2334 2421 2469 Select 1st city Select 2nd city Distance Between 1 CAL 678 0 337 1467 1498 1589 1846 1845 2079 2161 2286 2457 2586 2704 4 SEA OAK CAL TEX themKC MIN CHI MIL DET CLE TOR BAL NY BOS 2 1 1670 3 TEX 954 337 0 1246 1363 1536 1745 1756 1979 2053 2175 2329 2475 2611 4 KC 1670 1467 1246 0 460 853 798 843 982 1015 1186 1209 1383 1555 5 MIN 1489 1498 1363 460 0 394 403 483 630 694 822 961 1113 1254 6 CHI 1399 1589 1536 853 394 0 334 297 528 622 780 936 1028 1124 1720 1846 1745 798 403 334 0 74 235 307 430 621 740 867 7 MIL 8 DET 1694 1845 1756 843 483 297 74 0 237 328 434 641 746 860 9 CLE 1932 2079 1979 982 630 528 235 237 0 95 206 409 509 632 10 TOR 2023 2161 2053 1015 694 622 307 328 95 0 193 314 425 563 11 BAL 2124 2286 2175 1186 822 780 430 434 206 193 0 384 366 463 12 NY 2334 2457 2329 1209 961 936 621 641 409 314 384 0 184 370 13 BOS 2421 2586 2475 1383 1113 1028 740 746 509 425 366 184 0 187 2469 2704 2611 1555 1254 1124 867 860 632 563 463 370 187 0 A 1 Profit Model 2 3 Data 4 Unit Price 5 Average concession 6 Fixed Cost 7 Demand 8 9 10 11 Model 12 Unit Price 13 average concession 14 Demand 15 Total sales 16 Total concession 17 18 80% of gate and concession sales 19 Fixed Cost 20 21 Profit 22 B C $10.00 $15.00 $12,000.00 3000 $10.00 $15.00 3000 $30,000.00 $45,000.00 $60,000.00 $12,000.00 $48,000.00 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 1 Sales volume Unit price Total sales 80,000 $12.00 Unit variable cost Total variable cost $3.00 Model $960,000.00 $240,000.00 Fixed Cost $400,000.00 Profit $320,000.00 Discount rate NPV of Profit 4.00% $958,321.57 YEAR 2 YEAR 3 Sales volume Unit price Total sales 83,200 $12.50 86,528 $13.00 $1,040,000.00 Sales volume Unit price Total sales Unit variable cost Total variable cost $3.15 Unit variable cost Total variable cost $3.31 $262,080.00 Fixed Cost $432,000.00 Fixed Cost Profit $345,920.00 Profit $1,124,864.00 $286,191.36 $466,560.00 $372,112.64 Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Unit cost $175 Production volume 75 Total manufacturing cost Total purchased cost $59,375 $13,125 Cost difference (Manufacture - Purchase) Best Decision $46,250 Outsource Purchased from supplier 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. Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000 $125 Unit cost $175 Production volume 1546 Total manufacturing cost Total purchased cost $243,250 $270,550 Cost difference (Manufacture - Purchase) Best Decision -$27,300 Manufacture Purchased from supplier Model 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 OAK 0 678 954 1670 1489 1399 1720 1694 1932 2023 2124 2334 2421 2469 Select 1st city Select 2nd city Distance Between 1 CAL 678 0 337 1467 1498 1589 1846 1845 2079 2161 2286 2457 2586 2704 4 SEA OAK CAL TEX themKC MIN CHI MIL DET CLE TOR BAL NY BOS 2 1 1670 3 TEX 954 337 0 1246 1363 1536 1745 1756 1979 2053 2175 2329 2475 2611 4 KC 1670 1467 1246 0 460 853 798 843 982 1015 1186 1209 1383 1555 5 MIN 1489 1498 1363 460 0 394 403 483 630 694 822 961 1113 1254 6 CHI 1399 1589 1536 853 394 0 334 297 528 622 780 936 1028 1124 1720 1846 1745 798 403 334 0 74 235 307 430 621 740 867 7 MIL 8 DET 1694 1845 1756 843 483 297 74 0 237 328 434 641 746 860 9 CLE 1932 2079 1979 982 630 528 235 237 0 95 206 409 509 632 10 TOR 2023 2161 2053 1015 694 622 307 328 95 0 193 314 425 563 11 BAL 2124 2286 2175 1186 822 780 430 434 206 193 0 384 366 463 12 NY 2334 2457 2329 1209 961 936 621 641 409 314 384 0 184 370 13 BOS 2421 2586 2475 1383 1113 1028 740 746 509 425 366 184 0 187 2469 2704 2611 1555 1254 1124 867 860 632 563 463 370 187 0