As product marketing manager, one of our jobs is to prepare recommendations to the

company$$s executive committee as to how advertising expenditures should be allocated. Last year$$s

advertising budget of $$40,000$ was spent in equal increments over the four quarters of the year. Initial

expectations are that we will repeat this plan in the coming year. However, the committee would like

to know whether 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$$Q$1$: $90\%,$ Q$2$: $110\%,$

Q$3$: $80\%,$ and Q$4$: $120\%.$ In addition to product costs, we must take into account the cost of the sales

force $($projected to be $$34,000$ over the year, allocated as follows: Q$1$ and Q$2,$ $$8,000$ each; Q$3$ and

Q$4,$ $$9,000$ each$),$ the cost of advertising itself, and overhead $($typically $15\%$ of revenues$).$

Quarterly unit sales seem to run around $4,000$ units when advertising is around $$10,000.$ Clearly,

increasing advertising expenditures will increase demand, but there are limits to its impact. Our

consultants several years ago estimated the relationship between advertising and demand. Converting

that relationship to current conditions gives the following formula:

Quarterly Demand in Units $=35$ Seasonal Factor $3,000+$ Advertising

a$.$ Build a spreadsheet model to predict the quarterly and annual profit for the base scenario

described in the problem.

GRBU $503$ Sensitivity Analysis Notes $5$

b$.$ Assuming that quarterly advertising expenditures remain the same in each quarter throughout

the year, use a one$-$way data table to determine the annual advertising expenditure that max$-$

imizes the company$$s annual net income. Consider advertising expenditure values from $$0$ to

$$120,000$ in increments of $$5,000.$ Plot the results of your analysis on a line graph.

c$.$ Assuming that quarterly advertising expenditures remain the same in each quarter throughout

the year, use a one$-$way data table to determine the annual advertising expenditure that maxi$-$

mizes the company$$s profit margin. Consider advertising expenditure values from $$0$ to $$120,000$

in increments of $$5,000.$ Plot the results of your analysis on a line graph