Mantis Models – An Excel design exercise
You are a product marketing manager for Mantis Models, a toy company dedicated to realistic insect models.
You have been asked to make a recommendation about how the advertising budget should be spent in the coming year. Last year’s marketing budget of $40,000 was spent equally in each of the four quarters. However, you think that a different quarterly allocation or budget amount would be useful to consider.
Mantis Models toys sell for an average of $40 and cost an average of $25 to manufacture. Sales are seasonal and seasonal adjustments to unit sales are:
Q1: 90% Q2: 110% Q3: 80% Q4: 120%
In addition to manufacturing costs, there are other costs to consider.
· Mantis Models has a sales force that costs $34,000 for the year. The sales forces costs are allocated $4,000 in Q1 and Q2 and $4,500 in Q3 and Q4.
· Overhead costs generally run about 15% of gross revenue.
Quarterly unit sales average 4,000 units when advertising is around $10,000 in the quarter.
Advertising increases sales but has a limit. A marketing consultant estimated the relationship between advertising and sales as follows:
Unit sales = 35 * Seasonal Factor * Sqrt (3,000 + advertising spend)
The post advertising budget
