NOTE: ONLY NEED ASSISTANCE W/ PART 3.

6 4 8 9 6 This problem focuses on a very profitable product that our company sells - the Tiger ONE widget or T1 for short. You have been asked to provide input on the ordering policy currently being used for the Ti. Part 1 - Demand Sales and marketing tracks actual weekly sales but report these sales by rounding to the nearest 1,000 units sold. For modeling purposes, we are assuming that the errors associated with rounding are not large enough to be of concern. The table below displays the sales volume and the number of weeks in the past year that each volume was realized. You can assume that nothing extraordinary was done to influence these numbers (e.g., sales promotions) so variability is due to natural market fluctuations. Weekly sales volume Number of weeks this sales volume occurred in the past year 1,000 2,000 3,000 7 4,000 5,000 6,000 7,000 8,000 9,000 3 10,000 1. Convert this data into a probability distribution. This will be used as our best estimate of the probability that each of these demands will occur in the future and used in calculations to help decision makers with deciding on an ordering policy. Part 2 - Current strategy The Ti design is propriety but production is outsourced to a single supplier. While this is certainly unusual, the arrangement has been in place since the founding of our company. The supplier has been consistent, on-time, high quality, and completely trustworthy even though our current management thinks they are a bit expensive. This supplier charges us $13 per unit. The T1 contains a component that degrades with time and a unit is only usable for one week after it is produced. All customer orders are filled from inventory - neither ordering in advance nor backordering is allowed. That is, when a customer places an order, it is either filled from stock or it is a lost sale. The arrangement with our supplier is that all production of the T1 is accomplished over the weekend. Hence, the overall ordering and sales process is as follows: We place an order with our supplier each Friday and they are delivered Monday moming. The age of individual units are not monitored so all units received are assumed to be 2 days 4 1 4 . old (i.e., conservative assumption that they are all produced on Saturday) so they can be sold until Friday as first quality. First quality units command the full price of $20 per unit. Any unsold units as of Friday afternoon can be disposed of on a secondary market for $3 each. (Participants in the secondary market destroys the T1 and uses one if its components that does not degrade for another purpose.) If customer orders regularly exceed supply, we will obviously have problems keeping customers because they will begin to look for alternative suppliers, To model this, it has been estimated that each unit of unmet demand (i.e., lost sales) will cost the company $20. Construct a spreadsheet model to determine the profit for a week with the current system. You can use the overall structure we discussed in the lesson or develop your own. Regardless of how you develop the model, you will need to validate it in the next couple of questions. 2. Assume for the moment that demand for a week is 5000 and the number ordered the previous Friday was 4000. Compute the profit by hand and verify the answer in your spreadsheet model is correct. (This tests a shortage.) 3. Now let weekly demand equal order quantity ordered the previous Friday at 5000. Verify the profit from your model is correct. 4. Finally, test you model for a surplus by letting weekly demand be 4000 and the number ordered the previous Friday be 5000. Again, verify that the spreadsheet is correct. 5. Construct a two-way data table that displays profit as a function of demand sales and order quantity. Let both demand and order quantity range from 1,000 to 10,000 in increments of 1,000. Note that all cells are will either reflect a shortage (demand exceeds order quantity), a surplus (order quantity exceeds demand), or equal demand and supply. Part 3 - An alternative strategy Now that you have a model built for the current situation, management wants to compare it with an alternative strategy that is being considered. This alternative will be based on offering customers whose orders can't be filled from on-hand stock a discount of 10% off full price if they will wait one day for delivery. This will replace the model assumption of the $20 per unit penalty. If this is actually implemented, our supplier has agreed to accept and expedite orders placed during the week. Their proposal is to produce any sized order overnight and deliver it the next day for a 20% premium in price plus a one-time charge of $5000/order. That is, management wants to consider meeting unmet demand with a sales price of $18 and paying a unit cost of $15.60 plus a $5000 setup charge with each order that placed or excessive demand. Develop a model like you did in Part 2 to compute the profit with this alternative strategy and verify the calculations are correct. Build the model so that you can place one order each day of the week because that's the worst-case scenario (i.e., be short every day of the week). Also, assume that this strategy will work for orders to satisfy a shortage on Friday - we'll deliver to the customer on Saturday and they can use them. Note that demands are forecasted as weekly and regular orders are placed weekly but the new strategy to handle shortages are daily. You'll need to address this by using "engineering judgement" that means making assumptions that can be defended and that allow you to solve the problem to get reasonable and believable answers. 6. Use this model to construct a two-way data table with the same values of demand and order quantity as in Q5. It's now time to help the mangers get a glimpse of the economic consequence of the possible alternative strategy for handling stockout. 7. Compute the expected profit for both strategies using the data tables you built in Q5 and 26. Use the demand distribution you computed in Q1. What do you think of the alternative strategy based solely on this analysis? 10. Compare the two strategies using your answers for Q5, Q6 and Q7 in a way that would help a decision maker. Note: There are a number of ways to do this so use a bit of imagination along with trial and error and determine a meaningful way to help a decision maker. I think they would want to know several things like what the risk involved with this approach, what is/are key financial factors (costs, price) that would have the most impact if it/they were changed a bit through policy (price) or negotiation (costs) 6 4 8 9 6 This problem focuses on a very profitable product that our company sells - the Tiger ONE widget or T1 for short. You have been asked to provide input on the ordering policy currently being used for the Ti. Part 1 - Demand Sales and marketing tracks actual weekly sales but report these sales by rounding to the nearest 1,000 units sold. For modeling purposes, we are assuming that the errors associated with rounding are not large enough to be of concern. The table below displays the sales volume and the number of weeks in the past year that each volume was realized. You can assume that nothing extraordinary was done to influence these numbers (e.g., sales promotions) so variability is due to natural market fluctuations. Weekly sales volume Number of weeks this sales volume occurred in the past year 1,000 2,000 3,000 7 4,000 5,000 6,000 7,000 8,000 9,000 3 10,000 1. Convert this data into a probability distribution. This will be used as our best estimate of the probability that each of these demands will occur in the future and used in calculations to help decision makers with deciding on an ordering policy. Part 2 - Current strategy The Ti design is propriety but production is outsourced to a single supplier. While this is certainly unusual, the arrangement has been in place since the founding of our company. The supplier has been consistent, on-time, high quality, and completely trustworthy even though our current management thinks they are a bit expensive. This supplier charges us $13 per unit. The T1 contains a component that degrades with time and a unit is only usable for one week after it is produced. All customer orders are filled from inventory - neither ordering in advance nor backordering is allowed. That is, when a customer places an order, it is either filled from stock or it is a lost sale. The arrangement with our supplier is that all production of the T1 is accomplished over the weekend. Hence, the overall ordering and sales process is as follows: We place an order with our supplier each Friday and they are delivered Monday moming. The age of individual units are not monitored so all units received are assumed to be 2 days 4 1 4 . old (i.e., conservative assumption that they are all produced on Saturday) so they can be sold until Friday as first quality. First quality units command the full price of $20 per unit. Any unsold units as of Friday afternoon can be disposed of on a secondary market for $3 each. (Participants in the secondary market destroys the T1 and uses one if its components that does not degrade for another purpose.) If customer orders regularly exceed supply, we will obviously have problems keeping customers because they will begin to look for alternative suppliers, To model this, it has been estimated that each unit of unmet demand (i.e., lost sales) will cost the company $20. Construct a spreadsheet model to determine the profit for a week with the current system. You can use the overall structure we discussed in the lesson or develop your own. Regardless of how you develop the model, you will need to validate it in the next couple of questions. 2. Assume for the moment that demand for a week is 5000 and the number ordered the previous Friday was 4000. Compute the profit by hand and verify the answer in your spreadsheet model is correct. (This tests a shortage.) 3. Now let weekly demand equal order quantity ordered the previous Friday at 5000. Verify the profit from your model is correct. 4. Finally, test you model for a surplus by letting weekly demand be 4000 and the number ordered the previous Friday be 5000. Again, verify that the spreadsheet is correct. 5. Construct a two-way data table that displays profit as a function of demand sales and order quantity. Let both demand and order quantity range from 1,000 to 10,000 in increments of 1,000. Note that all cells are will either reflect a shortage (demand exceeds order quantity), a surplus (order quantity exceeds demand), or equal demand and supply. Part 3 - An alternative strategy Now that you have a model built for the current situation, management wants to compare it with an alternative strategy that is being considered. This alternative will be based on offering customers whose orders can't be filled from on-hand stock a discount of 10% off full price if they will wait one day for delivery. This will replace the model assumption of the $20 per unit penalty. If this is actually implemented, our supplier has agreed to accept and expedite orders placed during the week. Their proposal is to produce any sized order overnight and deliver it the next day for a 20% premium in price plus a one-time charge of $5000/order. That is, management wants to consider meeting unmet demand with a sales price of $18 and paying a unit cost of $15.60 plus a $5000 setup charge with each order that placed or excessive demand. Develop a model like you did in Part 2 to compute the profit with this alternative strategy and verify the calculations are correct. Build the model so that you can place one order each day of the week because that's the worst-case scenario (i.e., be short every day of the week). Also, assume that this strategy will work for orders to satisfy a shortage on Friday - we'll deliver to the customer on Saturday and they can use them. Note that demands are forecasted as weekly and regular orders are placed weekly but the new strategy to handle shortages are daily. You'll need to address this by using "engineering judgement" that means making assumptions that can be defended and that allow you to solve the problem to get reasonable and believable answers. 6. Use this model to construct a two-way data table with the same values of demand and order quantity as in Q5. It's now time to help the mangers get a glimpse of the economic consequence of the possible alternative strategy for handling stockout. 7. Compute the expected profit for both strategies using the data tables you built in Q5 and 26. Use the demand distribution you computed in Q1. What do you think of the alternative strategy based solely on this analysis? 10. Compare the two strategies using your answers for Q5, Q6 and Q7 in a way that would help a decision maker. Note: There are a number of ways to do this so use a bit of imagination along with trial and error and determine a meaningful way to help a decision maker. I think they would want to know several things like what the risk involved with this approach, what is/are key financial factors (costs, price) that would have the most impact if it/they were changed a bit through policy (price) or negotiation (costs)