Question

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. It’s great here all year round! Why don’t you come for Labor Day weekend? It’ll be beautiful, we can do some hiking and eat in all the good restaurants before they get too crowded”. “Plus”, Jeremy continues, “aren’t 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 Jeremy’s 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 I’ve 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, I’d 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 can’t sell skis he hasn’t ordered/stocked in the fall.
• If he orders too many skis, those left over at the end of the winter are sold at the store’s 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 you’ve 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.

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

Answer 1

Probability Demand Cost

0-10 150 150*150

11-40 175 175*150

41-75 200 200*150

76-95 225 225*150

96-100 250 250*150

Hence if order is 250 but the demand is for 150, then the remaining 100 would be sold for 100, i.e loss of 50, below is the calculation:

Sale (150*275+100*100) 51,250

Cost (250*150) 37,500

Profit on sale 13,750