2017 Fiscal Year Guest Survey - Advertisement Results
Survey Duration
Months
Year(s)
2017 Fiscal Start Date: 7/1/2016
2018 Fiscal Start Date:
7/1/2017
How did you hear about the Resort? (# Guest Results = # of times a Guest Responded with this answer)
Resort Seasonality
Word Of
Total
M/Year
Month
Season
Magazine Radio
Television Internet
Mouth
Other
Surveyed
Month
Season
2016 JUL
925
41
1,887
1,239
422
395
4,909
Jan
Low
2016 AUG
660
16
965
406
317
26
2,390
Feb
Low
2016 SEP
694
46
928
681
323
46
2,718
Mar
Mid
2016 OCT
304
28
646
568
339
79
1,964
Apr
Mid
2016 NOV
892
46
374
307
266
149
2,034
May
Mid
2016 DEC
1,551
36
247
283
313
138
2,568
Jun
High
2017 JAN
722
20
837
63
12
12
1,666
Jul
High
2017 FEB
375
84
819
36
231
79
1,624
Aug
Mid
2017 MAR
1,387
113
269
539
315
84
2,707
Sep
Low
2017 APR
331
50
675
71
149
240
1,516
Oct
Low
2017 MAY
1,476
119
196
609
25
21
2,446
Nov
Low
2017 JUN
1,893
14
131
22
358
283
2,701
Dec
High
Total
11,210
613
7,974
4,824
3,070
1,552
29,243
Average
New Budget Analysis
Today's Date
Type
Magazine
Radio
Television
Internet
Cost
Per Ad
$ 1,200
$ 300
$ 5,000
$ 1,100
Totals
Past Year, Monthly Advertising
Ads
Past Guest
Amount
Placed
Results
Spent
3
1
1
2
Cost per
Guest Result
New Monthly Advertising Negotiation for Next Fiscal Year
New
New Cost
Ads To
Amount Consider a Anticipated Guest
Budget
Per Ad
Place
to Spend Change?
Results
$ 4,000 $ 1,300
$
300 $
325
$ 12,000 $ 5,500
$ 2,200 $ 1,200
Budget +/-
Guest Results +/-
Type
Notes
Magazine Lowest Cost per Guest Result, yet same number Ads placed.
Radio
Budget no longer supports even one Ad.
Television Budget > than doubled, yet highest Cost per Guest Result.
Internet Budget not increased thus # Ads stayed the same.
Hiring Marketing Consultants Analysis
$ 125,000
5
Monthly Loan Payments
Down Payment
$ 10,000 $
15,000 $ 20,000 $ 25,000
Annual
Rate
Retainer Amount
Term (Years)
2.0%
2.5%
3.0%
3.5%
$ 30,000
Office 2016 – myitlab:grader – Instructions
Excel Project
YO16_XL_BU02_GRADER_PS2_HW - Advertise 1.3
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. Since 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.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
Start Excel. Open the file named e02_grader_hw_Advertise.xlsx. Save the file with the name
e02_grader_hw_Advertise_LastFirst, replacing LastFirst with your last and first name.
0.000
2
On the GuestData worksheet, in cell H2, use a function to determine the number of months
listed in cells A6:A17.
5.000
3
In cell J2, use a date function to calculate the survey duration in years using the 2017 Fiscal
Start date and 2018 Fiscal Start date.
3.000
4
5
In cells B6:B17, use Flash Fill to return the month abbreviation, in all caps, from column A.
Assign the named range Season to cells L6:M17.
2.000
2.000
6
In cells C6:C17, use the VLOOKUP function that will use the month in column B to return the
correct season — Low, Mid, or High — based on the named range Season.
5.000
7
In cells D19:J19, calculate the averages for each column with a rounded value to zero decimal
places.
5.000
Updated: 03/03/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
8
9
10
Excel Project
Points
Possible
Instructions
Assign the named range AvgMagazine to cell D19. Assign the named range AvgRadio to
cell E19. Assign the named range AvgTelevision to cell F19. Assign the named range
AvgInternet to cell G19.
On the AdvertisingPlan worksheet, in cell F2, enter a function that will return the current date.
In cell D6, reference the named range AvgMagazine to return the value in cell D19 on the
GuestData worksheet. Similarly, in cell D7, reference the named range AvgRadio. In cell D8,
reference the named range AvgTelevision. In cell D9, reference the named range
AvgInternet.
3.000
3.000
4.000
11
In cells E6:E9, calculate the Amount Spent — a monthly figure — by multiplying the Cost Per
Ad and the Ads Placed.
4.000
12
In cells F6:F10, calculate the Cost per Guest Result by dividing the Amount Spent by the Past
Guest Results.
4.000
13
In cells C10:E10, calculate the appropriate totals for each column.
6.000
14
In cells I6:I9, calculate the Number of Ads that can be purchased based on the New Budget
and the New Cost Per Ad in columns G and H. Be sure to include a function (INT) that will
round the number down to the nearest integer since a partial ad cannot be purchased.
4.000
15
In cells J6:J9, calculate the Amount to Spend — this is a monthly figure — by multiplying the
New Cost Per Ad and the Ads to Place.
4.000
16
17
18
19
In cells G10 and I10:J10, calculate the appropriate totals for each column.
6.000
In cell H11, calculate the amount of the budget remaining by subtracting the Amount to
Spend total from the New Budget total. Note that the totals are in row 10. A negative number
indicates that the new plan is over budget. A positive number indicates that the new plan is
under budget and has excess spendable funds.
In cells K6:K9, add a formula that will return Increase? if the Ads to Place is equal to zero or
if the New Cost Per Ad is less than or equal to the Budget +/- in cell H11. Any others should
return Decrease?. This column now indicates the media types for which the resort may want
to consider an increase or decrease in the Ads to Place, along with any necessary budget
adjustment.
In cells L6:L9, calculate the Anticipated Guest Results by dividing the Amount to Spend by the
Cost per Guest Result — column F. The resulting value should be rounded to zero decimals.
Updated: 03/03/2017
2
4.000
6.000
4.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
20
21
22
Excel Project
Points
Possible
Instructions
In cell L10, calculate the appropriate total for Anticipated Guest Results.
4.000
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.
Based on the data in cells A5:A9, D5:D9, and L5:L9, create 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, Style 6 to the chart and edit the chart title to read PAST
VS. ANTICIPATED MONTHLY GUEST RESULTS. Set the chart title to 12 pt font. Move and
resize the chart so the top left corner is in cell A11 and the bottom right corner is in cell F22.
4.000
6.000
23
Based on the data in cells A5:A9 and D5:E9, add a Clustered Column – Line on Secondary Axis
Combo Chart. Make this chart appear on its own worksheet — chart sheet — named
GuestResultsBySpending.
3.000
24
Apply the Chart Style, Style 6 to the chart and edit the chart title to read Past Advertising
Amount Spent Compared to # of Guest Results Experienced. Set the chart title font to
18 pt. Set the legend text and the font of both axes to 12 pt.
3.000
25
On the MarketingConsultants worksheet, in cells D10:H13, use a PMT function to calculate the
end of the month payment amount. Enter one formula that can be entered in cell D10 and
filled to the remaining cells. To calculate the amount for the pv argument, subtract the down
payment amount from the retainer amount. The formula results should be positive.
5.000
26
Insert the File Name code in the left custom footer section of the Header/Footer tab in the
Page Setup dialog box on all worksheets in the workbook.
1.000
27
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
Updated: 03/03/2017
3
100.000
Current_Instruction.docx
Purchase answer to see full
attachment