Grader hw Advertise

Anonymous
timer Asked: Apr 11th, 2019
account_balance_wallet $10

Question 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.


THE WORD FILE IS JUST AN INSTRUCTIONS. SO YOU DON'T HAVE TO DO ANYTHING ON IT. JUST USE IT AS INSTRUCTION. THE WORK HAS TO BE ON EXCEL FILE.

Unformatted Attachment Preview

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

Tutor Answer

Ace_Tutor
School: University of Maryland

attached is my complete answer

1) The file was renamed as e02_grader_hw_Advertise_LastFirst as follow:

2) The screenshot for cell H2 is shown below:

3) The screenshot for the cell J2 is shown below:

4) The cells B6:B17 were done as follow:

5) The named range Season was assigned to cells L6:M17 as follow:

6) The VLOOKUP function was used for cells C6:C17 as follow:

7) The screenshot for cells D19:J19 were shown as below:

8) The named range AvgMagazine was assigned to cell D19 as follow:

The named range AvgRadio to cell E19 as follow:

The named range AvgTelevision was assigned to cell F19 as follow:

The named range AvgInternet was assigned to cell G19 as follow:

9) The screenshot for cell F2 on the AdvertisingPlan worksheet was done as follow:

10) The named range AvgMagazine was referred to cell D6 according to the following
screenshot:

The cell D7 was referred to the name range AvgRadio as shown below:

The cell D8 was referred to the name range AvgTelevision as follow:

The cell D9 was referred to the name range AvgInternet as follow:

11) The Amount Spent was calculated in cells E6:E9...

flag Report DMCA
Review

Anonymous
Tutor went the extra mile to help me with this essay. Citations were a bit shaky but I appreciated how well he handled APA styles and how ok he was to change them even though I didnt specify. Got a B+ which is believable and acceptable.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors