Project Description:
The Painted Paradise Resort & Spa has been investing in advertising using different advertising media. When guests check-in, the employee asks the guest 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 an advertising source-such as a magazine-as how he or she heard about the resort, it is considered a guest result.
Ideally, the resort wants to purchase advertising at a low cost but then see as many guest results as possible. Every year, upper management sets the advertising budget before the beginning of the fiscal-July 1 start-year. For this next year, upper management gave you more television budget because of a new video marketing campaign. Also, the advertising contracts get negotiated every year as the media vendors require a one year commitment.
The contracts are negotiated after the budget is 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 prospects of hiring a marketing consulting company with a high retainer that would require a loan.
1 Start Excel. Open the downloaded file named e02md02_grader_h2_Advertise.xlsx. Save the file with the name e02md02_grader_h2_Advertise_YourOffice.
2 On the GuestData worksheet:
In cell H2, enter a COUNTA function to determine the number of months listed in cells A6:A17.
In cell J2, enter a DATEDIF function to calculate the survey duration in years using the 2014 Fiscal Start date and 2015 Fiscal Start date.
In cells B6:B17, use Flash Fill to return the three character code for the month-beginning with JUL for July.
3 On the GuestData worksheet:
Select cells L6:M17 and name the range season.
In cells C6:C17, add a VLOOKUP that will return whether it is Low, Mid, or High season based off the named range season.
In cells D19:J19, calculate the averages for each column with a value-not just formatted-to zero decimal places.
4 Create the following named ranges:
D19: AvgMagazine
E19: AvgRadio
F19: AvgTelevision
G19: AvgInternet
5 On the AdvertisingPlan worksheet, add the following:
In cell F2, enter a function that will return the current date.
Set the following cells to these formulas:
D6: =AvgMagazine
D7: =AvgRadio
D8: =AvgTelevision
D9: =AvgInternet
*Note these are monthly averages. Thus all calculations on this worksheet are estimates based on the monthly average.
6 On the AdvertisingPlan worksheet, add the following:
In cells E6:E9, calculate the Amount Spent-this is a monthly figure-by multiplying the Cost Per Ad and the Ads Placed.
In cells F6:F9, calculate the Cost per Guest Result by dividing the Amount Spent by the Past Guest Results.
In cells C10:F10, calculate the appropriate results for each column.
7 On the AdvertisingPlan worksheet, add the following:
In cells I6:I9, calculate the Number of Ads that can be purchased based off the New Budget and the New Cost Per Ad in columns G and H. Note: A partial ad cannot be purchased. Further, $324 would not be enough to purchase one radio ad, since the cost per ad is $325.
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.
In cells G10 and I10:J10, calculate the appropriate totals for each column.
8 On the AdvertisingPlanworksheet, add the following:
In cell H11, calculate the amount over or under the budget by subtracting the Amount to Spend total from the New Cost Per Ad total. Note: the totals are in row 10.
In cells K6:K9, enter a function 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 that the resort may want to consider an increase or decrease to the Ads to Place-and 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-not the just the format-should be rounded to zero decimals.
9 On the AdvertisingPlanworksheet, add the following:
In cell L10, calculate the appropriate total for Anticipated Guest Results.
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.
Evaluate the statements in cells H15:L18. Bold any false statements.
10 Starting on the AdvertisingPlan worksheet, create the following chart:
Based on the data in cells A5:A9, D5:D9 and L5:L9, add a 3D Clustered Column Chart.
Under chart styles, set the chart to Style 6. Then, change the title to read PAST V. ANTICIPATED MONTHLY GUEST RESULTS (do not include a period).
Move and resize the chart so the upper left hand corner is in cell A11 and the lower right hand corner is in cell F22. Set the chart title to 12 point font.
11 Starting on the AdvertisingPlan worksheet, create the following chart:
Based on the data in cells A5:A9, D5:D9 and E5:E9 in the AdvertisingPlan worksheet, add a 3-D Clustered Column - Line on Secondary Axis Combo Chart. Make this chart appear on its own worksheet, named GuestResultsBySpending.
Under chart styles, set the chart to Style 6. Then, change the title to read Past Advertising Amount Spent compared to # of Guest Results Experienced, Monthly (do not include a period).
Set the chart title to 16 point font. Set all axis data labels to 18 point font. Set all legend text to 12 point font.
12 On the MarketingConsultants worksheet, add the following:
In cells D10:H13, add PMT functions to calculate the monthly payments. Enter one function, using mixed referencing, that can be entered in cell D10 and filled to the remaining cells. The down payment should be subtracted from the Retainer-or Principal-Amount in the third argument of the PMT function.
Be sure that the function returns a positive value.
13 On the Documentation worksheet:
Insert a row between rows 21 and 22. Type GuestResultsBySpending into cell B22.
Type 12/31/2015 into cells A6, A8, and A20:A23.
Type Your Office into cells B6, B8, and C20:C23.
Type Completed the new marketing campaign analysis into cell C8.
14 Save the workbook and then exit Excel. Submit the workbook as directed.
Misc MyITLab assignment. should only take an hour. please deliver soon as possible today
Attachment:- e02md02_grader_h2_Advertise.xlsx