Excel Assignment In this problem, you will calculate the marginal benefit and the marginal benefit per dollar of each unit of advertising for each type of advertising. You will determine the levels of different types of advertising at which the marginal benefits per dollar between three kinds of advertising are equal. You will also evaluate different combinations of three types of advertising. Problem

Ad Allocation Problem

The Dakota Valley Booster Club in North Sioux City, SD is a non-profit organization which fundraises to support extra-curricular activities at the Dakota Valley School District. The activities they support include fine arts, athletics, and academic related teams or groups. Each month, they hold a tailgate or meal (depending on the season) and charge $5 a person. Food for the meals are sponsored by local businesses so all income received is “profit” to be used for student activities. Getting the word out about the meals is important both because the amount of support the booster club can provide is related to their revenue at fundraisers and because the businesses who agree to sponsor the food are doing so for marketing and visibility purposes. As a small non-profit organization, advertising options and funding are limited.

Suppose you are determining the amount of advertising to do for a small non-profit booster club like the one at the Dakota Valley School District. You can elect to pay to boost visibility for the information placed on your Facebook group page; this means people in the area beyond those who subscribe to your posts will be able to see your event information. Another option is to place ads in the local newspaper. The local paper comes out once per week meaning you cannot do more than 4 advertisments before each meal. You can also print flyers and hire a high school student to distribute them at school functions. Costs of each unit of advertising and expected number of customers at each level of advertising are given in the tables below.

a) Using the information provided below, calculate the marginal benefit in dollars of each unit of advertising for each type of advertising.

b) Using the marginal benefit you found in part a and formulas from your texbook, find the marginal benefit per dollar spent on advertising for each kind of advertising at each level. This will tell you how many dollars of benefit you receive from each dollar spent.

Price for a tailgate/meal $5.00

Facebook Advertising

Cost per Boost $10.00

Units of Advertising Expected Customers Marginal Benefit, $ MB/Cost, $

0 0

1 20

2 40

3 55

4 70

5 80

6 85

7 90

8 90

Newspaper Ads

Cost per Week $70.00

Units of Advertising Expected Customers Marginal Benefit, $ MB/Cost, $

0 0

1 45

2 80

3 105

4 120

Flyers

Cost per 100 (Includes Distribution) $30.00

Units of Advertising, hundreds Expected Customers Marginal Benefit, $ MB/Cost, $

0 0

1 30

2 55

3 75

4 90

5 100

6 105

7 105

с) At what level of each type of advertising is the marginal benefit per dollar of advertising equal between the three kinds of advertising? Assume that given the opportunity, the club would want to advertise less instead of more.

Units of the Facebook Advertising

Units of the Newspaper Advertising

Units of the Flyers Advertising hundreds

d) Suppose the booster club you are helping currently places an ad in the paper for each of the 4 weeks leading up to the meal, hands out 400 flyers, and does one boost on Facebook before the event. What would you recommend they do in each area based on your calculations in part c?

The Facebook advertising should

The newspaper advertising should

The flyers advertising should

e) If this booster club was to advertise at the level suggested in part c, how much money would they spend in each area and in total?

Cost of the Facebook Advertising

Cost of the Newspaper Advertising

Cost of the Flyers Advertising

Total Cost

f) If this booster club was limited to $110 of advertising for each meal, how much would you recommend they spend in each area?

Spending on Facebook Advertising

Spending on Newspaper Advertising

Spending on Flyers Advertising

Instructions

Project Description:

In this problem, you will calculate the marginal benefit and the marginal benefit per dollar of each unit of advertising for each type of advertising. You will determine the levels of different types of advertising at which the marginal benefits per dollar between three kinds of advertising are equal. You will also evaluate different combinations of three types of advertising.

For the purpose of grading the project you are required to perform the following tasks:

Step Instructions Points Possible

1 Start Excel. 0

2 In cell E12, by using relative and absolute cell references, calculate the marginal benefit for the Facebook advertising assuming the level of advertising indicated in cell C12. Use cells E7, D11, and D12. Copy the formula from cell E12 down the column to cell E19. 1

3 In cell F12, by using relative and absolute cell references, calculate the marginal benefit per dollar spent on the Facebook advertising assuming the level of advertising indicated in cell C12. Use cells E9 and E12. Copy the formula from cell F12 down the column to cell F19. 1

4 In cell E24, by using relative and absolute cell references, calculate the marginal benefit for the newspaper advertising assuming the level of advertising indicated in cell C24. Use cells E7, D23, and D24. Copy the formula from cell E24 down the column to cell E27. 1

5 In cell F24, by using relative and absolute cell references, calculate the marginal benefit per dollar spent on the newspaper advertising assuming the level of advertising indicated in cell C24. Use cells E21 and E24. Copy the formula from cell F24 down the column to cell F27. 1

6 In cell E32, by using relative and absolute cell references, calculate the marginal benefit for the flyers advertising assuming the level of advertising indicated in cell C32. Use cells E7, D31, and D32. Copy the formula from cell E32 down the column to cell E38. 1

7 In cell F32, by using relative and absolute cell references, calculate the marginal benefit per dollar spent on the flyers advertising assuming the level of advertising indicated in cell C32. Use cells E29 and E32. Copy the formula from cell F32 down the column to cell F38. 1

8 In cells E40-E42, by using cell references, determine the levels of different types of advertising at which the marginal benefits per dollar between three kinds of advertising are equal. Refer to appropriate cells among C11-C19, C23-C27, and C31-C38. 1

9 In cells E44-E46, determine how the levels of different types of advertising described in part d should be changed to improve the current combination of three kinds of advertising. 3

10 In cell E48, by using cell references, calculate the cost of the Facebook advertising assuming the level of advertising indicated in cell E40. Use cells E9 and E40. 1

11 In cell E49, by using cell references, calculate the cost of the newspaper advertising assuming the level of advertising indicated in cell E41. Use cells E21 and E41. 1

12 In cell E50, by using cell references, calculate the cost of the flyers advertising assuming the level of advertising indicated in cell E42. Use cells E29 and E42. 1

13 In cell E51, by using cell references, calculate the total cost of advertising assuming the levels of advertising indicated in cells E40-E42. Use the Excel SUM function. 1

14 In cell E53, by using cell references, calculate the spending on Facebook advertising assuming the conditions described in part f. Use cell E9 and an appropriate cell among C11-C19. 1

15 In cell E54, by using cell references, calculate the spending on newspaper advertising assuming the conditions described in part f. Use cell E21 and an appropriate cell among C23-C27. 1

16 In cell E55, by using cell references, calculate the spending on flyers advertising assuming the conditions described in part f. Use cell E29 and an appropriate cell among C31-C38. 1

17 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0