BUSINESS ANALYTICS

Late in the summer, before the start of the school year, you decide to make a quick phone call to your old college roommate Jeremy Hertz, who now lives in Aspen, Co where he runs a ski-equipment shop. I love living in a ski town, says Jeremy. Its great here all year round! Why dont you come for Labor Day weekend? Itll be beautiful, we can do some hiking and eat in all the good restaurants before they get too crowded. Plus, Jeremy continues, arent you taking a Business Analytics Course? I could use your help with my stocking decision for the upcoming ski season.

How could you resist? So Labor Day weekend finds you in Jeremys apartment, planning your next day hiking excursion. But first, you say, tell me about your stocking problem.

Jeremy says Why this is hard, is that demand varies. Some seasons Ive have requests for 250 skis, but one bad year I could only sell 150. Plus there are other complications. If you could help me figure out how many skis to order this year, Id be truly grateful. Some additional information:

• He purchases skis from a manufacturer each fall for the coming winter season.
• The manufacturer he works with only sells skis in lots of 20.
• The most popular intermediate model costs $150 and sells for $275.
• Special orders take too long, so he cant sell skis he hasnt ordered/stocked in the fall.
• If he orders too many skis, those left over at the end of the winter are sold at the stores spring scales for $100.
• Sales over the years is summarized in the table below.

Demand Prob

150 10%

175 30%

200 35%

225 20%

250 5%

Technical Analysis

1. Assume Jeremy orders 200 skis. On the template, fill in the formulas for the given quantities (for a given demand, given an order level of 200). The formula should have only cell addresses (so no embedded numbers).*
2. Once youve worked out a formula for the profit, with the intermediate calculations showing develop a single cell formula to calculate the same thing. (Or, skip this step and learn how to use the Data Table function in Excel.)
3. What are sensible order levels? Set up rows in the table for each sensible order level.
4. Use your formula (or the data table function) to determine the profit for each demand/order level pair. (The $s are important here.)
5. Do whatever you wish, to help Jeremy choose how many skis to order. Remember, he does not control the demand, but he does know the probabilities of each demand level.

Fill out the Excel provided

B20 ft c D E F H J K L M N o 9 R 175 200 Ski Purchase Cost Regular Sales Price Spring Sales Price $150 $275 $100 Demand Distribution Demand Probability 150 10% 175 30% 200 35% 225 20% 250 5% Demand 150 175 200 225 250 B 1 Mountain Skl Sports 2 3 1/2) Demand 4 Order Level 5 6 Ski Cost 7 Skis Sold at Regular Price 8 Skis Sold at Spring Sale 9 Regular Sales Revenue 10 Sale Ski Revenue 11 Profit 12 13 Profit (one-line) 14 15 3/4) Profit calculations: 16 (add more rows as needed) 17 18 19 Order Level 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 42