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 Surveyed
Month 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 Low MAY 25 Low JUN 14 22 283 High Low Mid High Analysis
New Budget 3 Type 1 $ 300 Magazine 1 Radio 2 $ 1,200 Television Internet 5
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
Season
Magazine
Radio
Television
Internet
Word Of Mouth
Other
Total
Season
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
149 240
1,516
Oct
2021
MAY
1,476
119
196
609
21 2,446
Nov
2021
JUN
1,893
131
358
2,701
Dec
Total
11,210
613
7,974
4,824
3,070
1,552
29,243
Average
Season Count
Total Surveyed
by Season
Low
Mid
High
AdvertisingPlan
New Budget
Today’s Date
Past Year, Monthly Advertising
New Monthly Advertising Negotiation for Next Fiscal Year
Type
Cost
Per AdAds Placed
Past Guest Results
Amount Spent
Cost per Guest Result
New Cost
Per AdAds To Place
Amount to Spend
Consider a Change?
Anticipated Guest Results
Magazine
$ 1,200
$ 4,000
$ 1,300
Notes
Radio
$ 300
$ 325
Lowest Cost per Guest Result, yet same number Ads placed.
Television
$ 5,000
$ 12,000
$ 5,500
Budget no longer supports even one Ad.
Internet
$ 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
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%
image1
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
