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.
others
Description
YO_Excel16_BU02_PS2_v2
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.
Steps to
Perform:
|
Step |
Instructions |
Points Possible |
|
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 |
|
2 |
On the GuestData worksheet, in
cell H2, use a function to determine the number of months listed in cells
A6:A17. |
5 |
|
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 |
|
4 |
In cells B6:B17, use Flash Fill
to return the month abbreviation, in all caps, from column A. |
2 |
|
5 |
Assign the named range Season to cells L6:M17. |
2 |
|
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 |
|
7 |
In cells D19:J19, calculate the averages for each column
with a rounded value to zero decimal places. |
5 |
|
8 |
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. |
3 |
|
9 |
On the AdvertisingPlan worksheet, in cell F2, enter a
function that will return the current date. |
3 |
|
10 |
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. |
4 |
|
11 |
In cells E6:E9, calculate the Amount Spent — a monthly
figure — by multiplying the Cost Per Ad and the Ads Placed. |
4 |
|
12 |
In cells F6:F10, calculate the
Cost per Guest Result by dividing the Amount Spent by the Past Guest Results. |
4 |
|
13 |
In cells C10:E10, calculate the appropriate totals for
each column. |
6 |
|
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 |
|
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 |
|
16 |
In cells G10 and I10:J10,
calculate the appropriate totals for each column. |
6 |
|
17 |
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. |
4 |
|
18 |
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. |
6 |
|
19 |
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. |
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 |
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. |
6 |
|
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 |
|
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 |
|
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 |
|
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 |
|
27 |
Save the workbook. Close the workbook and then exit Excel.
Submit the workbook as directed. |
0 |
|
Total Points |
100 |
