Advertising Example Model

                          Class 1: Advertising example  

As a newly appointed product-marketing manager, one of your jobs is to advise the Executive Committee as to how advertising expenditure should be allocated in a small local market in Baltimore. Last year’s 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 whether some other allocation would be advantageous and whether the total budget should be changed.

Your product (a toy named, Supercalifragilisticexpialidocious) sells for $40 and costs $25 to produce. Sales in the past have been seasonal and the marketing department estimated (from historical sales data) the following seasonal factors for unit sales

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

In addition to production costs, you must take into account the cost of the sales force (projected to be $34,000 over the year), allocated as follows:

In addition, you must take into account the cost of advertising itself, and overhead costs (typically, 15% of revenues).

Quarterly unit sales seem to run around 4,000 units when advertising is around $10,000. Clearly, adverting will increase sales, but there are limits to its impact (As a result, the return on investment had a decreasing rate.)  The marketing department estimated the relationship between advertising and sales (once again using historical sales data). Converting that relationship to current conditions gives the following formula for quarterly demand:

[pic 1]

Your job is to complete the spreadsheet model that will help you to analyze and generate insights from the situation above. You could use the Excel Template available in Blackboard’s Course Documents>Session 1 folder, or you could develop an entire new model on your own.  

Task: (Building the Base Model) What would be the profit if the company spends $10,000 on advertisement in each of the four quarters? [pic 2]

Check your answer: If the base model is built correctly, the profit should be $69,662.  

...