ComputerScienceExpert
$18/per page/
| Start Excel. Open Hawaiian_Trips_Exam1A.xlsx and save the workbook as HawaiianTrips_LastNameFirstName. **Make sure the Lookup Tables sheet is active. ** |
|
Assign the range name rates to the base price and commission range. Assign the range name tours to the data for the package, tour description, departure, and base cost. |
|
Click cell D13 on the Data sheet and insert a lookup function that looks up the tour code, compares it to the tours table, and returns the description. Copy the function down through cell D22. |
|
Click cell E13 and insert a lookup function that looks up the tour code, compares it to the tours table, and returns the departure date. Copy the function down through cell E22. |
|
Click cell F13 and insert a lookup function that looks up the tour code, compares it to the tours table, and returns the base cost of the trip. Format the cell with the accounting format then copy the function down through cell F22. |
|
In cell G13, insert a formula that adds taxes and fees to the base cost of the trip (in cell F13) by using the percentage value in the input area. Use a mixed reference to the cell containing 18% in the input area above the data. Format the cell with the accounting format then copy the formula down through cell G22. |
|
In cell H13, insert a PMT function to calculate the payments for students who want to pay for their trips in FOUR installments. Use the interest rate and months in the input area above the data. Use absolute cell references in the formula where needed. Make sure the result is a positive value. Copy the function down through cell H22. |
|
Click cell I13 and calculate the agent commission using the base cost of the trip and another lookup function that returns the commission rate based on the base cost of the trip using the rates lookup table. The function should then calculate the monetary value of the commission. Format the cell with the accounting format then copy the function down through cell I22. |
|
Merge and center the main title on the first row over all data columns on the Data sheet. (Columns A:I) Apply the “Title” cell style. Merge and center the subtitle on the second row over all data columns and apply the “Explanatory Text” style. |
|
Apply Currency number format to the monetary values in column H. Hide the Tour Code column. |
|
Wrap text in the range F12:I12. Set the column widths for these columns to 15. Adjust the row height to AutoFit Row Height. Freeze the panes so that the first column and rows 1 through 12 do not scroll off-screen. |
|
Apply the Light Red Fill with Dark Red Text conditional formatting to values in the Total Cost with Taxes column for the top 4 values. |
|
Insert a function to calculate the total for all trips (including taxes) in cell G5, the median trip cost in cell G6, and the average trip cost in cell G9 |
|
Insert a function to calculate the lowest trip cost in cell G7 and the highest trip cost in cell G8. Click cell G10 and enter a function to display today's date with the time. Format the cell to display the time (Example: 9/9/15 3:30 PM). |
|
Copy the Data sheet and place the copied sheet before the Summary sheet. Remove the conditional formatting rule on the Data (2) sheet. |
|
Convert the data range in the Data (2) sheet to a table. Apply the Table Style Medium 2 style to the table. |
|
Sort the table by departure date from newest to oldest and then alphabetically by trip description. Apply a filter to display trips arranged by agents Sampson and Bills only. |
|
Display a total row. Select a function that calculates totals for all monetary columns. |
|
Create column sparklines in column H in the Summary sheet to display six-month trends for each agent. Show the high point in each sparkline. Apply the SparkLine Style Accent 1, Darker 50% style. Apply the Blue, Accent 1 high point marker color. |
| Insert an IF function in column I If the agent ’s average sales are less than than the average of all sales for the six months, then the agent does not qualify for a bonus. (0 should be the result) If the agent qualifies, then put in the bonus stored in cell K1. |
|
Create a clustered bar chart of the agents and their six-month sales, using the chart type that displays the months on the category axis. Move the chart to a new chart sheet named Sales Chart. |
|
Apply the Layout 1 chart layout. Type January-June 2016 Sales by Agent for the chart title. Apply the Style 8 chart style. |
| Create a footer with the sheet tab code in the center, the file name code on the right side of each sheet, and the text “Hawaiian Trips” in the left section.**Make sure the footer is on ALL sheets. ** |
|
Apply 0.2" left and right margins and scale to one page (Height and Width) for the Data and Data (2) sheets. Select Landscape orientation for the Data (2) sheet. |
|
Change the width of the summary page to fit on 1 Page when printed (Height and Width). Also on the summary worksheet, make it centered horizontally on the page. |
|
Save the workbook. Ensure that the worksheets are named correctly and in the following order: Lookup Tables, Data, Data (2), Sales Chart, and then Summary. Close the workbook and exit Excel. Submit the workbook back into MyITLab. |
Ofce 2013 – myitlab:grader – InstructionsExcel ProjectHawaiiProject Description:You are currently working for a Hawaiian travel company that specializes in arranging travelaccommodations for faculty tours and vacations in the exciting destination of Hawaii. You created aworkbook to store agent names, student IDs, and tour codes. The workbook also contains aworksheet to store lookup tables. You will complete the workbook for you manager’s approval. Youwill insert formulas and a variety of functions, convert data to a table, sort and Flter the table,prepare a chart, and make the data more visually appealing by adding conditional formatting.Instructions:For the purpose o± grading the project you are required to per±orm the ±ollowing tasks:StepInstructionsPointsPossible1Start Excel. OpenHawaiian_Trips_Exam1A.xlsxand save the workbook asHawaiianTrips_LastNameFirstName.**Make sure the Lookup Tables sheet is active. **0.0002Assign the range nameratesto the base price and commission range. Assign therange nametoursto the data ±or the package, tour description, departure, andbase cost.**Make sure youDO NOTinclude the labels when naming these ranges. **2.0003Click cell D13 on the Data sheet and insert a lookup ±unction that looks up the tourcode, compares it to the tours table, and returns the description. Copy the ±unctiondown through cell D22.4.0004Click cell E13 and insert a lookup ±unction that looks up the tour code, compares itto the tours table, and returns the departure date. Copy the ±unction down throughcell E22.4.0005Click cell F13 and insert a lookup ±unction that looks up the tour code, compares itto the tours table, and returns the base cost o± the trip. Format the cell with theaccounting ±ormat then copy the ±unction down through cell F22.4.0006In cell G13, insert a ±ormula that adds taxes and ±ees to the base cost o± the trip(in cell F13) by using the percentage value in the input area. Use amixedreferenceto the cell containing 18% in the input area above the data. Format thecell with the accounting ±ormat then copy the ±ormula down through cell G22.4.0007In cell H13, insert a PMT ±unction to calculate the payments ±or students who wantto pay ±or their trips in FOUR installments. Use the interest rate and months in theinput area above the data. Use absolute cell re±erences in the ±ormula whereneeded. Make sure the result is a positive value. Copy the ±unction down throughcell H22.**To make thisvalue display as a positive number, Place a negative sign be±ore the ±unction**4.0008Click cell I13 and calculate the agent commission using the base cost o± the tripand another lookup ±unction that returns the commission rate based on the basecost o± the trip using the rates lookup table. The ±unction should then calculate themonetary value o± the commission. Format the cell with the accounting ±ormatthen copy the ±unction down through cell I22.4.0009Merge and center the main title on the ²rst row over all data columns on the Datasheet. (Columns A:I) Apply the “Title” cell style. Merge and center the subtitle onthe second row over all data columns and apply the “Explanatory Text” style.6.000Updated: 09/30/20151Current_Instruction.docx
Reference TablesBase Price$399$999$1,199$1,399 $1,599Commission3.0%3.5%4.0%4.5%5.0%PackageTour DescriptonDepar±ure Base Cos±10Adven±ures in Kona6/11/2016 $1,299.0020Waikiki Beach Boa± Tour6/4/2016$999.0030Snorkeling a± Hanauma Bay6/11/2016$499.0040Hike The Haleakala Volcano6/18/2016 $1,149.0050Wings Over Kauai Air Tours6/25/2016 $1,699.0060Wales a± Molokini Island6/11/2016 $1,449.0070Tur±les a± Tur±le Bay6/25/2016$599.00
Attachments: