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.

Transcribed Image Text:

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

Answer rating: 100% (QA)

Answer Probability Demand Cost 010 150 150150 1140 175 175150 417... View the full answer