Please create the spreadsheet and word document for the following.

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 $40 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 pitches will only last about 30 seconds.

One problem is that you much purchase the gifts (umbrellas and magazine subscriptions) in advance. The cost is $20 for each umbrella, and $25 for each magazine subscription. You cannot store leftover umbrellas and must give away every item you buy.

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 100,000 homes (different homes each hour, but 100,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, 100,000 people see each spot and you will have 100,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 $20. Management estimates that 25% of the umbrella recipients will do this, and 15% 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 showing how you would maximize net income, given the above situation and constraints. The second part is a word document with your recommendation(s) on how management should proceed. 

When you make your first recommendation, plug that back into the spreadsheet and show how much it increases net income. Include that increase in your recommendation. If it doesn’t increase net income, don’t make that recommendation.

You should be able to plug your recommendation back into your spreadsheet.