The Advertising Budget Decision

As product marketing manager, one of our jobs is to prepare recommendations to the Executive Committee as to how advertising expenditures should be allocated. Last years advertising budget of $40,000 was spent in equal increments over the four quarters. Initial expectations are that we will repeat this plan in the coming year. However, the Committee would like to know if some other allocation would be advantageous, and whether the total budget should be changed.

Our product sells for $40 and costs us $25 to produce. Sales in the past have been seasonal, and our consultants have estimated seasonal adjustment factors for unit sales as follows: Q1 90% Q2 110% Q3 80% Q4 120%

(A seasonal adjustment factor measures the percent of average quarterly demand experienced in a given quarter.)

In addition to production costs, we must take into account the cost of the sales force (projected to be $34,000 over the year, allocated as follows: Q1 and Q2, $8000 each; Q3 and Q4, $9000 each), the cost of advertising itself, and overhead (typically around 15% of revenues).

Quarterly unit sales seem to run around 4000 units when advertising is around $10,000. Clearly, advertising will increase sales, but there are limits to its impact. Our consultants several years ago estimated the relationship between advertising and sales. Converting that relationship to current conditions gives the following formula:

Unit sales = 35*Seasonal factor*SQRT(3000 + Advertising),

where SQRT( ) stands for the square-root function, e.g., SQRT(4)=2, and it is a built-in function in Excel.

Build a spreadsheet model to calculate profits in Q1, Q2, Q3, Q4.

QUESTION 2 In the Advertising Budget Decision Case, the unit sales formula is: Unit sales = 35 seasonal_factor SQRT(3000+advertising). A S1 Increase in advertising causes the unit sales to increase more when seasonal_factor is 0.9 Instead of 1.1. True False QUESTION 3 If the firm's advertising budget is $32,000 (instead of $40,000) the firm spends it in equal increments over the four quarters, the firm's profit is 67 897.45 66,149.91 66,500.34 68,765.54 QUESTION 4 If the firm's advertising budget is $32,000 (instead of $40,000) and the firm allocates it optimally over the four quarters, the firm's profit is: 67.791.59 66,149.51 68,303.21 69,000.13 QUESTION 5 If the firm's advertising budget is $32,000 (instead of $40,000) and the firm allocates it optimally over the four quarters, the firm's break-even production cost is: 27.14 28.56 27.13