It is March, and the annual NCAA Basketball Tournament is down to the final four teams. Randy Kitchell is a T-shirt vendor who plans to order T-shirts with the names of the final four teams from a manufacturer and then sell them to the fans. The fixed cost of any order is $750, the variable cost per t-shirt depends on the quantity ordered. A quantity discount reduces the cost per shirt for larger orders, according to the table shown below. Randy’s selling price during the tournament is $10. However, after the tournament Randy will have to sell any remaining t-shirts at a reduced price of $4 each. Build a spreadsheet model that will let Randy experiment with different values for demand and his order quantity. Quantity ordered Cost per shirt(at least)
0 to 199 (0) $13200 to 399 (200) $11400 to 599 (400) $9600 to 799 (600) $7800 to 999 (800) $5
Create a two-way table to show the sensitivity of profit to changes in Demand and Order Quantity. Put Demand as the row, Order Quantity as the column. Use the values 200, 400, 600, 800, 1000 for both.
Demand
200 400 600 800 1000
Order Quality 200
400
600
800
1000