Filename, Sort, Filter, Pivot Table, Subtotals, Data & Stati…
Filename, Sort, Filter, Pivot Table, Subtotals, Data & Statistical Functions, and Print Settings — 65 Points 1. Create a Folder on your Desktop named Exam. 2 . Download the following file(click on the 3 dots at the end of the line and click download), save it to your Exam folder. SU25_Excel_Template_Ex2.xlsx 3. Use the SU25_Excel_Template_Ex2.xlsx file that you downloaded from Blackboard for this exam. “Save As” or Rename this workbook as your last name + your first initial + _Ex2.xlsx. Below is an example of the filename you would use if you were Sam Smith: Filename Example: SmithS_Ex2.xlsx 4. You are continuing your work with Experience Alaska, Inc. and they have asked you for specific information about their planned late-May through mid-July 2021 trip packages. It is highly recommended that you save your work frequently. 5. With your newly renamed file open in Excel, make the Packages sheet active. 6. Using the appropriate command option, sort the list by Departure Date (oldest to newest), Trip Type (Z to A), and Package Price (smallest to largest) in order. 7. Copy the Packages sheet to a new sheet and name the new sheet Filter. 8. Make the Filter sheet active. Turn on Filters for all data columns. Using Number Filters for both, display the data for trips with a Trip Length of greater than 5 Nights, and a Package Price of $2,300 or more. Your Filters should account for the possibility of any future trips that may be added. 9. Return to the Packages sheet. Management would like to further analyze their current booking information. Create a Pivot Table on a New Worksheet showing the Average of Package Price by both Trip Type (in rows) and Destination (in columns). Format the Trip Cost data as Currency with Dollar Signs and Zero Decimals. Rename your new worksheet Pivot Analysis. 10. Management would like another booking information analysis. Copy the Packages sheet to another new sheet, and name the new sheet Subtotals. Create a Subtotal that displays the average amounts of only the Package Price at each change in Trip Type. You will first need to sort your information appropriately. 11. Management now needs to determine how their trip prices will be affected by a proposed tax. Copy the Packages sheet to another new sheet, and name the new sheet Trip Tax. Insert a new column between column D and column E and name the new Column E, PriceWithTax. Ensuring that the Range_lookup finds an Exact Match, use the VLOOKUP function to calculate the new PriceWithTax based upon the percentages in the Proposed Tax Table that gives the DeparturePoint for each PackageNum. Apply the same formatting used in Column D, Package Price. 12. Return to the Packages sheet once again, and in cell A30 enter the text Total price of trips going to Seward on the AK RR. In cell D30, use the appropriate conditional math function to determine that total. Format your result with Currency and zero decimals. 13. In cell A31 of the Packages sheet, enter the text Number of trips departing on July 5. In cell D31, use the appropriate conditional statistical function to determine that number. 14. With your Packages tab/worksheet active, set the print settings to the following: a) Landscape orientation b) Fit to one page c) Center both vertically and horizontally d) Create a Custom Header with Your Name in the center section e) Create a Custom Footer using the appropriate button to display the File (Workbook) name in the center section. Three Year Forecast Activities — 35 Points 1. Make your Three Year Forecast sheet active. Experience Alaska, Inc. would like you to continue to assist them with their forecast information. 2. Previously, we created this forecast worksheet and determined the Award Status. Now use Conditional Formatting in the range B23:D23 to emphasize when a “Cash Bonus” is to be awarded. Show the cell contents with a Bold & Italic Dark Blue, Text 2 font (first row, fourth column), and with an Outline Border of the same color when the condition is met. Do not use more than one rule. 3. While on the Three Year Forecast sheet, create a Scenario Summary report with the following information using cells B23:D23 as the result cells: Scenario Name Data Specifics Original Expense Variables Same Percentages that are currently given Increased Advertising Use 17.50% instead of 15.00% Decreased Salaries Per Year Use 50.00% instead of 55.00% Hide column D (the Current Values: column) on your newly created Scenario Summary worksheet. Make sure your Scenario Summary report is easily understood by any reader. The Changing Cells and Result Cell descriptions should not display cell references. 4. Next, make the Goal Seek sheet active. You previously used the PMT function to determine the cost of a new boat (showing a negative dollar amount), but Experience Alaska wants to pay less per month. With the Goal Seek sheet active, use the Goal Seek feature of Excel to determine the down payment that would be necessary to make the Monthly Payment equal to $500. 5. Using the same Goal Seek sheet, create a Two-Variable Data Table for the Monthly Payment Amounts using an Annual Interest Rate Range of 3.00% to 5.00% (in increments of .25%) and Term in Years options of 2, 3, and 4. Use cell D5 as the upper-left corner of your table, and format your Monthly Payment amounts with Currency and zero decimals. Ensure that all data is visible. Apply a custom number format to make the formula reference in cell D5 appears as Monthly Payment. Excel Chart—20 Points 1. With your Three Year Forecast sheet active and your Scenario Summary showing the Original Expense Variables, create a Pie in 3-D chart demonstrating the Net Profit After Taxes by Forecast Year. a) Place your chart on a New Chart Sheet (not Object in) and name the tab “Profit Chart”. b) Create a title above the chart as Forecasted Net Profit After Taxes by Your Name. c) Use Outside End Data Labels to display only Percentages with one decimal place, and Category Names. Remove the legend. d) Apply a Pattern Fill and Explode the piece that illustrates the year with the highest forecasted Net Profit After Taxes percentage. Make sure your spelling on the chart is correct Finally—5 Points 1. Make sure your worksheets are in the following order: Three Year Forecast, Goal Seek, Scenario Summary, Profit Chart, Pivot Analysis, Packages, Subtotals, Filter, and Trip Tax. 2. Ensure that there are no spelling errors on your Three Year Forecast and Goal Seek worksheets. 3. Save your workbook one last time and exit Excel. 4. In the answer box below , click on the attachment icon and attach your file to it. ** Make sure your file is saved and attached properly to this question, otherwise your instructor will not receive your exam to grade. Screenshot 2025-02-26 124438.png 5. Once your file is submitted, delete all exams files and empty your recycle bin.