Insert Draw Arial Design Layout References 14 - A A Aar A Mailings Review View vers 33 zh Project Description: The Painted Paradise Resort and Spa has been investing in advertising using different medis. When guants 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 other magazine, radio, television, Internet, word of mouth, or other. Since almost every questis asked, the number surveyed represents a significant portion of the actual guests. The past year's data is located on the Guastata worksheel. Every time a guest answer the question by mentioning an advertising source is considered a guest result. Idealy, 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 face on JUN 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 quest results, and the prospect of hiring a marketing consulting company with a high retainer that would require a loan. Steps to Perform: Points Instructions Possible Start Excel. Open the file named 02 grader_hw_Advertise way. Save the file with the name 02_grader_hw_Advertise_LastFirst, replacing Lastist with your last and first name On the GuestData worksheet, in cell H2, use a function to determine the number of months listed in cells A6-A17 In cell 12. use a date function to calculate the survey duration in years using the 2017 Fiscal Start date and 2018 Fiscal Start date. In cells B6 B17, use Flash Fil to return the month abbreviation, in all caps, from column A Assign the named range Season to colls 16:M17. in cells C6C17, 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 In cells 19:19, calculate the averages for each column with a rounded value to zero decimal places Assign the named range Axat ion to call 019. Assign the named range AxaRadio to coll E19. Assign the named range Axe Calcision to cell F19. Assign the named range Axalatecost to cel G19. On the Advertisinalan worksheet, in cell F2. enter a function that will return the current date. 021150 worden English (United States Share AaBbCcD AaBbCcDdE AaBbCcDE AaBbceDE AEBCDER mahasis Heading 2 Heading 1 Normal Normal Indent Strong Styles Pane In cell D6, reference the named range AWOMAGazing to return the value in Cel 19 on the Guest Data worksheet. Similarly, in cell D7, reference the named range AyaRadio In coll Da, reference the named rango Ava Television. In all D9, reference the named range Avolaternet In cells E6:E9, calculate the amount Spent -a monthly figure -- by multiplying the Cost Por Ad and the Ads Placed In cells F6F10, calculate the Cost per Guest Result by dividing the Amount Spent by the Past Guest Results 13 In cells C10:10, calculate the appropriate totals for each column. In cells 16:19, 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. In cells J8:19, calculate the Amount to Spend -- this is a monthly figure -- by multiplying the New Cost Per Ad and the Ads to Place cells G10 and 110:J10, calculate the appropriate totals for each column 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 Ko K9, add a formula that will retum 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 Decemasa? 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 Resultcolumn F The resulting value should be rounded to zero decimals In cell L10, calculate the appropriate total for Anticipated Guest Results 21 In coll 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 Focus Heading 2 Grader - Instructions Excel 2016 Project Step Points Possible Based on the data in cels AS:A9, 05:09, and L5:L9, create a 3-D Clustered Column chart to compare the past quant results to the anticipated stranuits based on the new monthly advertising. Apply the Chart Style Style 6 to the chart and edit the chart it 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 comer is in cel A11 and the bottom right comer is in cell F22 Based on the data in cells A5-A9 and DS:E9, add a Clustered Column-Line on Secondary Axis Combo Chart. Make this chart appear on its own worksheet chart sheet-named GuestResultsBy Spending Apply the Chart Style, Style 6 to the chart and edit the chartstle 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 pl. On the Marketing Consultants 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 col D10 and filled to the remaining cells. To calculate the amount for the py argument, subtract the down payment amount from the retainer amount. The formula results should be positive 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 27 Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed Total Points 100 Home Insert Draw Page Layout Calitri (Body 11 Formulas AA Data Review =E3 ES View eser Conditional Formatting P Formuses Totle Cell Styles Recover Unseved Workbooks. We were able to save changes to one or more files. Do you want to recover them? C D L M N Painted Paradise E F G H I J K 2017 Fiscal Year Guest Survey - Advertisement Results Sur Duration March Years 2017 Dates 7/11/16 2018 Date: 2017 Guest Results of times de Responded with this answer Wando Total Radio Telewin Internet Mouth Surveyed 411,187 1.219 HANNAAANNA GuessDate Advertising Plan Marketing Consultants + 100009LL10 TOGGY! 2 MacBook Air Insert Draw Arial Design Layout References 14 - A A Aar A Mailings Review View vers 33 zh Project Description: The Painted Paradise Resort and Spa has been investing in advertising using different medis. When guants 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 other magazine, radio, television, Internet, word of mouth, or other. Since almost every questis asked, the number surveyed represents a significant portion of the actual guests. The past year's data is located on the Guastata worksheel. Every time a guest answer the question by mentioning an advertising source is considered a guest result. Idealy, 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 face on JUN 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 quest results, and the prospect of hiring a marketing consulting company with a high retainer that would require a loan. Steps to Perform: Points Instructions Possible Start Excel. Open the file named 02 grader_hw_Advertise way. Save the file with the name 02_grader_hw_Advertise_LastFirst, replacing Lastist with your last and first name On the GuestData worksheet, in cell H2, use a function to determine the number of months listed in cells A6-A17 In cell 12. use a date function to calculate the survey duration in years using the 2017 Fiscal Start date and 2018 Fiscal Start date. In cells B6 B17, use Flash Fil to return the month abbreviation, in all caps, from column A Assign the named range Season to colls 16:M17. in cells C6C17, 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 In cells 19:19, calculate the averages for each column with a rounded value to zero decimal places Assign the named range Axat ion to call 019. Assign the named range AxaRadio to coll E19. Assign the named range Axe Calcision to cell F19. Assign the named range Axalatecost to cel G19. On the Advertisinalan worksheet, in cell F2. enter a function that will return the current date. 021150 worden English (United States Share AaBbCcD AaBbCcDdE AaBbCcDE AaBbceDE AEBCDER mahasis Heading 2 Heading 1 Normal Normal Indent Strong Styles Pane In cell D6, reference the named range AWOMAGazing to return the value in Cel 19 on the Guest Data worksheet. Similarly, in cell D7, reference the named range AyaRadio In coll Da, reference the named rango Ava Television. In all D9, reference the named range Avolaternet In cells E6:E9, calculate the amount Spent -a monthly figure -- by multiplying the Cost Por Ad and the Ads Placed In cells F6F10, calculate the Cost per Guest Result by dividing the Amount Spent by the Past Guest Results 13 In cells C10:10, calculate the appropriate totals for each column. In cells 16:19, 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. In cells J8:19, calculate the Amount to Spend -- this is a monthly figure -- by multiplying the New Cost Per Ad and the Ads to Place cells G10 and 110:J10, calculate the appropriate totals for each column 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 Ko K9, add a formula that will retum 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 Decemasa? 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 Resultcolumn F The resulting value should be rounded to zero decimals In cell L10, calculate the appropriate total for Anticipated Guest Results 21 In coll 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 Focus Heading 2 Grader - Instructions Excel 2016 Project Step Points Possible Based on the data in cels AS:A9, 05:09, and L5:L9, create a 3-D Clustered Column chart to compare the past quant results to the anticipated stranuits based on the new monthly advertising. Apply the Chart Style Style 6 to the chart and edit the chart it 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 comer is in cel A11 and the bottom right comer is in cell F22 Based on the data in cells A5-A9 and DS:E9, add a Clustered Column-Line on Secondary Axis Combo Chart. Make this chart appear on its own worksheet chart sheet-named GuestResultsBy Spending Apply the Chart Style, Style 6 to the chart and edit the chartstle 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 pl. On the Marketing Consultants 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 col D10 and filled to the remaining cells. To calculate the amount for the py argument, subtract the down payment amount from the retainer amount. The formula results should be positive 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 27 Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed Total Points 100 Home Insert Draw Page Layout Calitri (Body 11 Formulas AA Data Review =E3 ES View eser Conditional Formatting P Formuses Totle Cell Styles Recover Unseved Workbooks. We were able to save changes to one or more files. Do you want to recover them? C D L M N Painted Paradise E F G H I J K 2017 Fiscal Year Guest Survey - Advertisement Results Sur Duration March Years 2017 Dates 7/11/16 2018 Date: 2017 Guest Results of times de Responded with this answer Wando Total Radio Telewin Internet Mouth Surveyed 411,187 1.219 HANNAAANNA GuessDate Advertising Plan Marketing Consultants + 100009LL10 TOGGY! 2 MacBook Air