YO19-Excel-BU02-PS1-Advertise-Instructions xStudent_*Excel_BU02_PS1_Advertise.xlsx*

**YO19_Excel_BU02_PS1_Advertise**

Start Excel. Download and open the file named Excel_BU02_PS1_Advertise.xlsx. Grader will automatically add your last name to the start of the filename. Save the file to the location where you are storing your files.

On the GuestData worksheet, in cell H2, add a COUNTA function to determine the number of months listed in cell range A6:A17.

In cell J2, add a DATEDIF function to calculate the survey duration in years using the 2020 Fiscal Start date and 2021 Fiscal Start date.

Assign the named range **Season** to cell range L6:M17.

In cell range C6:C17, add a VLOOKUP that will look up the value in column B and then return the exact season match of Low, Mid, or High based on the value in column B. Use the named range Season in the formula.

To count the number of seasons in cell B22, enter a COUNTIF function that counts the Seasons in cell range C6:C17 that meet the criteria in cell A22. Copy the formula through cell B24.

To total the number of guests surveyed by season in cell E22, enter a SUMIF function that sums the total surveyed in J6:J17 if it meets the criteria in cell D22. Use appropriate absolute referencing to copy the formula through cell E24.

In cell range D19:J19, calculate the averages for each column with a rounded (not formatted) value to zero decimal places.

Grader – Instructions Excel 2019 Project

##
YO19_Excel_BU02_PS1_Advertise

##
Project Description:

The Painted Paradise Resort and Spa has been investing in advertising using different media. When guests check in, the employee asks them how they heard about Painted Paradise Resort & Spa. Based on the customer’s response, the employee then notes in the system either magazine, radio, television, Internet, word of mouth, or other. Because almost every guest is asked, the number surveyed represents a significant portion of the actual guests. The past year’s data is located on the GuestData worksheet. Every time a guest answers the question by mentioning an advertising source, it is considered a guest result. Ideally, the resort wants to purchase advertising at a low cost but then see as many guest results from that advertising as possible.

Every year, upper management sets the advertising budget before the beginning of the fiscal year, July 1. For the coming year, upper management has given you a larger television budget because of a new video marketing campaign. Also, the advertising contracts get negotiated every year, because the media vendors require a one-year commitment. The contracts are negotiated after the budget has been set. You will develop charts for an upcoming presentation that will discuss a marketing strategy, potential changes to the budget given the new media prices, anticipated monthly guest results, and the prospect of hiring a marketing consulting company with a high retainer that would require a loan.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named

Excel_BU02_PS1_Advertise.xlsx. Grader will automatically add your last name to the start of the filename. Save the file to the location where you are storing your files.

0

2

On the GuestData worksheet, in cell H2, add a COUNTA function to determine the number of months listed in cell range A6:A17.

4

3

In cell J2, add a DATEDIF function to calculate the survey duration in years using the 2020 Fiscal Start date and 2021 Fiscal Start date.

2

4

Assign the named range

Season to cell range L6:M17.

2

5

In cell range C6:C17, add a VLOOKUP that will look up the value in column B and then return the exact season match of Low, Mid, or High based on the value in column B. Use the named range Season in the formula.

4

6

To count the number of seasons in cell B22, enter a COUNTIF function that counts the Seasons in cell range C6:C17 that meet the criteria in cell A22. Copy the formula through cell B24.

4

7

To total the number of guests surveyed by season in cell E22, enter a SUMIF function that sums the total surveyed in J6:J17 if it meets the criteria in cell D22. Use appropriate absolute referencing to copy the formula through cell E24.

4

8

In cell range D19:J19, calculate the averages for each column with a rounded (not formatted) value to zero decimal places.

4

9

Assign the following named ranges:

In cell D19,

AvgMagazine

In cell E19,

AvgRadio

In cell F19,

AvgTelevision

In cell G19,

AvgInternet

4

10

On the AdvertisingPlan worksheet, in cell F2, enter a function that will return the current date.

2

11

On the AdvertisingPlan worksheet, in cell D6, enter a formula that will reference the named range

AvgMagazine (to return the value in cell D19 on the GuestData worksheet).

In cell D7, reference the named range

AvgRadio

In cell D8, reference the named range

AvgTelevision

In cell D9, reference the named range

AvgInternet

8

12

In cell range E6:E9, calculate the monthly Amount Spent by multiplying the Cost Per Ad and the Ads Placed.

4

13

In cell range F6:F9, calculate the Cost per Guest Result by dividing the Amount Spent by the Past Guest Results.

4

14

In cell range C10:E10, calculate the appropriate totals for each column.

4

15

On the AdvertisingPlan worksheet, you need to finish out the new budget year analysis.

In cell range I6:I9, calculate the Number of Ads that can be purchased based on the New Budget divided by the New Cost Per Ad. A partial ad cannot be purchased so you need to create a formula that will round the number down to the nearest integer.

4

16

In cell range J6:J9, calculate the monthly Amount to Spend by multiplying the New Cost Per Ad and the Ads to Place.

4

17

In cell range G10:J10, calculate the appropriate totals for each column. If necessary, change the format for cell I10 to general.

4

18

In cell range K6:K9, determine if you will increase or decrease marketing based on the Ads to Place.

Enter a formula that will return the value of

Increase if the Ads to Place is equal to zero. Any other value should return a value of

Decrease

4

19

In cells L6:L9, calculate the Anticipated Guest Results by dividing the Amount to Spend by the Cost per Guest Result in column F. The resulting value should be rounded (not formatted) to zero decimals.

4

20

In cell L10, calculate the appropriate total for Anticipated Guest Results.

4

21

In cell L11, calculate the amount of anticipated guest results compared to the past by subtracting the Past Guest Results total from the Anticipated Guest Results total. Note that the totals are in row 10. A negative number indicates an anticipated decrease in Guest Results. A positive number indicates an anticipated increase in Guest Results.

4

22

On the AdvertisingPlan worksheet, you need to make two charts for your presentation.

Based on the data in cell ranges A5:A9, D5:D9, and L5:L9, add a 3-D Clustered Column chart to compare the past guest results to the anticipated guest results based on the new monthly advertising.

Apply the Chart Style 3.

Change the title to

Past vs. Anticipated Guest Results

Move and resize the chart so the top-left corner is in cell A13 and the bottom-right corner is in cell L32.

8

23

Create an additional chart based on the data.

Iin cell ranges A5:A9, D5:D9, and E5:E9, add a Clustered Column – Line on Secondary Axis Combo Chart. Move this chart to a chart sheet named

GuestResultsBySpending

Apply Chart Style 5.

Change the title to

Advertising Amount Spent by Past Guest Results

Set the chart title to 18 pt font size, set the value axis labels to 12 pt font size, and set all legend text to 12 pt font size.

10

24

On the MarketingConsultants worksheet, a monthly loan payment analysis has been started. The resort is considering hiring marketing consultants. However, they require a large up-front retainer fee. The resort would need to take out a loan to cover the cost. The resort needs an analysis of the loan payment by varying interest rate and down payment amount. Think carefully about how to use mixed and absolute cell referencing.

In cell D10, add a PMT function using the following arguments.

•Calculate the monthly Rate from the annual rate in cell C10.

•Calculate the number of monthly payments from the term in years in cell B5.

•Calculate the present value of the loan by subtracting the down payment in cell D9 from the retainer amount.

•Adjust the formula so the result is positive.

Copy the function in cell D10 down to D13 and across to H13.

4

25

Save and close

Excel_BU02_PS1_Advertise.xlsx. Submit the file as directed.

0

Total Points

100

Created On: 01/22/2021 1 yo19_excel_bu02_ps1

>GuestData

Fiscal Year Guest Survey – Advertisement Results

s

Fiscal Start Date:

ality

Month

Season

Surveyed

Month

Season

JUL

9

AUG

Low

SEP

46

8

OCT

28

Mid

NOV

46

9

Mid

DEC

JAN

20

12

High

FEB

36

79

Mid

MAR

84

Low

APR

71

149

Low

MAY

25

21

Low

JUN

14

22

283

High

Low

Mid

High

20 | 22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Survey Duration | Month | Year(s) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

20 | 2 | 1 | 7/1/21 | 2022 Fiscal Start Date: | 7/1/22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

How did you hear about the Resort? (# Guest Results = # of times a Guest Responded with this answer) | Resort | Season | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

M/Year | Magazine | Radio | Television | Internet | Word Of Mouth | Other | Total | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2020 | JUL | 9 | 2 | 5 | 41 | 1,887 | 1,2 | 3 | 422 | 395 | 4,909 | Jan | Low | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2020 | AUG | 660 | 16 | 965 | 406 | 317 | 26 | 2,390 | Feb | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2020 | SEP | 694 | 46 | 9 | 28 | 681 | 323 | 2, | 71 | Mar | Mid | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2020 | OCT | 304 | 646 | 568 | 339 | 79 | 1,964 | Apr | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2020 | NOV | 892 | 374 | 307 | 266 | 14 | 2,034 | May | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2020 | DEC | 1,551 | 36 | 247 | 283 | 313 | 138 | 2,568 | Jun | High | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2021 | JAN | 722 | 837 | 63 | 12 | 1,666 | Jul | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2021 | FEB | 375 | 84 | 819 | 231 | 1,624 | Aug | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2021 | MAR | 1,387 | 113 | 269 | 539 | 315 | 2,707 | Sep | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2021 | APR | 331 | 50 | 675 | 240 | 1,516 | Oct | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2021 | MAY | 1,476 | 119 | 196 | 609 | 2,446 | Nov | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2021 | JUN | 1,893 | 131 | 358 | 2,701 | Dec | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

11,210 | 613 | 7,974 | 4,824 | 3,070 | 1,552 | 29,243 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Average | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Season Count | Total Surveyed by Season |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

## AdvertisingPlan

Analysis

New Budget

Magazine3

Type

Radio1

$ 300

Magazine

Television1

Radio

Internet2

$ 1,200

Television

Internet

New Budget | |||||||||||

Today’s Date | |||||||||||

Past Year, Monthly Advertising | New Monthly Advertising Negotiation for Next Fiscal Year | ||||||||||

Type | Cost Per Ad |
Ads Placed | Past Guest Results | Amount Spent | Cost per Guest Result | New Cost Per Ad |
Ads To Place | Amount to Spend | Consider a Change? | Anticipated Guest Results | |

$ 1,200 | $ 4,000 | $ 1,300 | Notes | ||||||||

$ 300 | $ 325 | Lowest Cost per Guest Result, yet same number Ads placed. | |||||||||

$ 5,000 | $ 12,000 | $ 5,500 | Budget no longer supports even one Ad. | ||||||||

$ 1,100 | $ 2,200 | Budget > than doubled, yet highest Cost per Guest Result. | |||||||||

Totals | Budget not increased thus # Ads stayed the same. | ||||||||||

Budget +/- | Guest Results +/- |

## MarketingConsultants

5

Hiring Marketing Consultants Analysis | ||||

Retainer Amount | $ 125,000 | |||

Term (Years) | ||||

Monthly Loan Payments | ||||

Down Payment | ||||

$ 10,000 | $ 15,000 | $ 20,000 | $ 25,000 | $ 30,000 |

Annual Rate | 2.0% | |||

2.5% | ||||

3.0% | ||||

3.5% |