Situation Rockin Jons Apparel is planning to sell T-shirts at an upcoming rock concert. Jon has three
Question:
Situation
Rockin’ Jon’s Apparel is planning to sell T-shirts at an upcoming rock concert. Jon has three decisions to make: (1) the number of shirts to produce (Q), (2) the selling price (P), and the level of advertising (A). Because the shirts are specific to this specific concert at this venue, any unsold shirts must be thrown out with no salvage value. Customer demand Dfollows the formula D = a – bP + c(A/100), where a is the demand intercept, b is the demand price slope per dollar, and cis the demand advertising slope per 100 dollars.
Assignment
Create an Excel model for this problem. Inputs include a, b, c, and the unit variable cost C. The outputs are the demand and the profit (loss). The demand should include a MAX function to ensure that if the result of the formula for Dis is negative, then demand will just equal 0. The profit (loss) equals P × MIN(Q, D) − CQ – A.
The four model inputs vary as follows. Variable cost: There’s a 35% chance that the supplier will be unable to deliver on time, in which case an expedited supplier must be used, costing $15 per shirt instead of the usual $5 per shirt. Intercept: The intercept a is assumed to follow a normal distribution with a mean of 5,000 and a standard deviation of 500. Advertising slope: Historical data suggests that c is distributed according to a Weibull distribution with α = 1.2 and β = 7.2. Price slope: The effect of price on demand depends upon a variety of factors. Historical observations of b for similar situations include 202, 182, 222, 204, 198, 192, 190, 202, 187, 186. Assume that these 10 values are equally likely in the future.
Using a sample size of 200, run a simulation using a Data Table to compare three different options. Option 1 is a purchase quantity of 800, advertising of $400, and a price of $23.00. Option 2 (more conservative) is a purchase quantity of 600, advertising of $200, and a price of $19.00. Option 3 (even more conservative) is a purchase quantity of 580, advertising of $200, and a price of $17.00. Write up a summary page that compares your three scenarios. What are the risk/return trade-offs? (For example, how many times did each option lose money? Which options provided particularly large profits during certain trials? Which option performed best on average?) Which option would you recommend?
Management and Cost Accounting
ISBN: 978-1405888202
4th edition
Authors: Alnoor Bhimani, Charles T. Horngren, Srikant M. Datar, George Foster