Excel case
Public Television Case
The deliverable (two parts, both due at the same time):
The first part of the deliverable is a spreadsheet that will show a pseudo budgeted income statement and cash flow, based on the given assumptions. The second part is a word document with your recommendations on how management should proceed.
Background:
You work for a public television station. You are running a week-long campaign in an effort to get donations. You run spots (sales pitches for money) and offer promotional gifts. The two gifts under consideration are an umbrella, and a one-year magazine subscription to a popular magazine. If a viewer donates $45 they will get the umbrella, and if they donate $50 they will get the one-year magazine subscription.
The campaign will last for 7 days, and will run for 12 hours each day. You will do one sales pitch each hour and will offer either the umbrella or the magazine subscription, but not both during the same pitch. Each sales pitch can be considered a spot. The sales pitch will only take about 60 seconds of air time.
One problem is that you must purchase the gifts (umbrellas and magazine subscriptions) in advance. The cost is $25 for each umbrella, and $25 for each magazine subscription.
The question for you is how many magazine spots and how many umbrella spots should the station air during the campaign, given the goal of maximizing net income?
Important facts:
Typical audience size during any hour is expected to be 110,000 homes (different homes each hour, but 110,000 on average). Your marketing experts say that 1% of the homeowners will respond positively to an umbrella spot (they will purchase an umbrella) and 1.5% of the homeowners will respond positively to a magazine spot (they will purchase a magazine subscription). For example, if 10 umbrella spots are run, 110,000 people see each spot and you will have 110,000 * 10 * .01 umbrella purchases.
Based on prior campaigns, management knows some viewers who make a donation for a gift will spontaneously donate money again at a later time (not for a gift, but just because they are great people). This gift is $25. Management estimates that 25% of the umbrella recipients will do this, and 10% of the magazine recipients will do this.
Management wants at least 30 spots to be run for each gift.
Management wants to earn as much net income as possible.
How many of each spot should be run?
The first part of your deliverable is the spreadsheet where you used the Solver tool showing the maximized net income and binding constraints given the above situation and constraints. The second part is a word document with your recommendations on how management should proceed. While there are about seven different recommendations you can make, I’m looking for four recommendations. The original spreadsheet answer will be your first recommendation. It is probably obvious, but you can’t make recommendation #1, then make recommendation #2, then combine recommendations #1 and #2 into recommendation #3. So each of the recommendations should be different from the other recommendations.
Your recommendations should be something that came from the binding constraints identified by the ‘answer’ report that you run from the solver tool. So it isn’t realistic for you to say the spreadsheet results suggest that hiring a famous person to give the pitches will result in a 25% increase in net income. Hiring a famous person may do that, but you would be hard pressed to support that number. Along the same line, don’t just suggest that we raise prices for the umbrella or the magazine.
For each recommendation, make the recommendation and include the new net income or the increase in net income.

Published by
Thesis
View all posts