Problem: A company is trying to determine the optimal level of advertising expenditures. Based upon past experience and test markets, please find below the relationship between sales revenue and advertising expenditures. The average gross margin percent (gross margin/revenue) is shown in parentheses for that level of sales. The margin increases as the revenue increases because of "economies of scale", but will eventually decrease because of the "diseconomies of scale". Please note that the unit selling price is the same at each level of advertising expenditure (price is not changed).
- No advertising => revenue of $1.2 mio (40%)
- Advertising $ 200,000 => revenue of $2.1 mio (43%)
- Advertising $ 400,000 => revenue of $2.9 mio (46%)
- Advertising $ 600,000 => revenue of $3.2 mio (48%)
- Advertising $ 800,000 => revenue of $3.4 mio (46%)
The income tax rate is 30%. Fixed G&A expenses are $600,000. Using Excel, please prepare a table showing:
- Revenue
- Gross margin
- Income before tax
- Net income
- Marginal revenue from Advertising (marginal product of advertising)
Please also briefly explain why the marginal revenue of advertising declines as the level of advertising increases (which is often the case in the real world).