Assignment: Advertising Budget
Project Description:
The Painted Paradise Golf Resort and Spa tends to attract local residents for weekend getaways and special events, so they have an aggressive marketing strategy that includes many different mediums for their advertising. In April, William Mattingly, CEO, would like to present a summary of their advertising costs to the board of trustees. The report to the board will include the budgeted amount for each month as well as the actual amount spent, along with a consolidated summary of the quarter. In order to make it easier and more consistent for different areas of the resort to advertise, he would also like an advertising request form created so each manager understands what their options for advertising are.
Instructions
1 Start Excel. Open the downloaded Excel file named e04ps1_grader_h1.xlsx. Save it as an Excel Workbook named e04ps1_grader_h1_LastFirst, using your last and first name. Enable content, if necessary.
2 With JanAds, FebAds, and MarAds worksheets grouped, select cell A13 and type Total. Enter a formula in cells B13:C13 to calculate the sum of each column. Enter a formula in cells D5:D13 that calculates the difference between the Actual and Budget amounts (B5-C5). Format cells A13:D13 with the Total style. Ungroup the worksheets.
3 On the Q1Summary worksheet tab, enter a 3D formula in cells B5:B12 to SUM the Actual advertising amounts. Enter a 3D formula in cells C5:C12 to SUM the Budget advertising amounts.
4 With the MarAds and Q1Summary worksheets grouped, select cells D5:D12 and fill All across the worksheets. Select cells A13:D13 and fill All across the worksheets. Select cells B5:C12 and fill Formats across the worksheets. Ungroup the worksheets.
5 On the Q1Averages worksheet tab, create a consolidated summary based on position. The summary should find the AVERAGE actual and budget amounts for each type of advertising for January through March using cells A5:C12. (Select Left column only and do not link to the data).
6 On the Q1Averages worksheet tab, calculate the difference (Over/Under) in cells D5:D12. In cell A13, type Total. Enter a formula in cells B13:D13 to calculate the totals. Format cells A13:D13 with the Total style.
7 On the AdRequest worksheet tab, create a list validation rule in cell C9 with the source data from cells A3:A10 on the AdOptions worksheet. The Input message should say Choose type from list and the Error message should say Invalid type. In C9, select Television.
8 On the AdRequest worksheet tab, create a date validation rule in cell C11 so the date must be greater than today's date. Use the TODAY function. Do not enter any messages. In cell C11, type =TODAY()+1.
9 On the AdRequest worksheet tab, create a date validation rule in cell C12 so the date must be greater than the date in cell C11 (the start date for the data validation will be =C11). In C12, enter =TODAY()+2. Select cell C14 and type 400. Select cell C15 and type 3.
10 On the AdRequest worksheet tab, create a text length validation rule in cell C18 to limit the comment to 200 or fewer characters. The Input Message should say Comments must be less than or equal to 200 characters (no period). In cell C18, type Television ad for three mornings during the news (no period).
11 On the AdRequest worksheet, add a hyperlink in cell A9 to go to the AdOptions worksheet. On the AdOptions worksheet, add a hyperlink in cell A2 to go to the AdRequest worksheet.
12 If necessary, add the Compare and Merge Workbooks button to the Quick Access Toolbar. Share the workbook, and save a copy as e04ps1_grader_h1_LastFirst_2, usingyour last and first name. On the JanAds worksheet tab, select cell B5 and change it to 4520. Select cell B12 and change it to 610. Save and close the workbook, but leave Excel open.
13 Open e04ps1_grader_h1_LastFirst.xlsx. Compare and merge the workbook with e04ps1_grader_h1_LastFirst_2.xlsx and then check the changes in cells B5 and B12. Stop sharing the workbook, click Yes, and save the changes.
14 On the AdRequest worksheet, unlock cells C9, C11:C12, C14:C15, and C18. Hide the row and column headers and the gridlines.
15 Hide the vertical and horizontal scroll bars for the workbook. Hide the AdOptions worksheet (but not the sheet tabs for the workbook). Protect the Ad Request worksheet without a password.
16 Save e04ps1_grader_h1_LastFirst.xlsx with the worksheets in the following order: JanAds, FebAds, MarAds, Q1Summary, Q1Averages, AdRequest, AdOptions (hidden), and Documentation. Close Excel. Submit the file as directed.
Attachment:- mosken_e04ps1_grader_h1.rar