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:

Q$1$: $80\%$

Q$2$: $90\%$

Q$3$: $110\%$

Q$4$: $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