a) Randy Kitchell is a NCAA t-shirt vendor. The fixed cost of any order is $1850, the variable cost is initially at $6 per shirt but decreases by 0.1 cents ($0.001) for every t-shirt ordered. That is: Variable Cost = 6 – (0.001×Quantity Ordered)

b) Randy’s base selling price is $10 per shirt, but during the week of the tournament, much like variable cost, is initially at $10 per shirt but decreases by 0.25 cents ($0.0025) for every t-shirt ordered. That is: Full Price = 10 – (0.0025×Quantity Ordered)

c) After the week of the tournament Randy will have to sell the left-over t-shirts at a discount price which will be equal to 60% of the Full Price. d) The expected demand during the tournament (at full price) is 1500 shirts.

Exercise 1 Randy would like you to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity. Build such a spreadsheet model for him. On the model, include a graph that shows what his profit will be if he orders various amounts starting at 1000 shirts up to 1800 shirts (in increasing amounts of 50). (Your spreadsheet MUST include at least one IF statement)

Exercise 2. Using the spreadsheet model for financial statements developed in Exercise 1 above, answer the following on a separate worksheet in the same excel file submission 

a) What are the input variable(s), decision/control variable(s) and output variable(s) for this model? 

b) For the expected demand of 1500 shirts, what is the optimal amount to order and why? 

c) Suppose the expected demand was 1400 shirts. Based on your model, would it be better to have ordered 50 shirts too many or 50 shirts to few? Explain.

Answer rating: 100% (QA)

1 Consider the following graph whi... View the full answer