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 customers 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 years 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