Advertising Model Applied Business Analytics for Decision Class 1 Preparation: Spreadsheet Modeling Exercise 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 southeastern Michigan. 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 $55 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 Q1 Q2 Q3 Q4 Seasonal Factor 90% 110% 80% 120% 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: Q1 Q2 Q3 Q4 Sales force cost $8000 $8000 $9000 $9000 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 1,900 units when advertising is around $10,000. Clearly, adverting will increase sales, but there are limits to its impact (i.e., the return on investment has a decreasing rate.) The marketing department estimates the relationship between advertising, sales, and price can be adequately explained using the following formula: Unit Sales in a quarter = 35 X Seasonal Factor X = 3000 + Advertising 30 Price Your job is to create a spreadsheet model that will help you to analyze and generate insights from the situation above. You can download and use the Excel Template available [HERE), or you could develop an entirely new model on your own. Task: (Building the Base Model) What would be the total profit across 4 quarters if the company spends $10,000 on advertisement in each of the four quarters? Check your answer: If your base model is built correctly, the profit should be $129,633