Insert an IF function in column I that displays a $500 bonus if an agent ’ s average sales are greater than the average of all sales for the six months. If needed, apply the Blue, Accent 1 high point marker color. Apply the SparkLine Style Accent 1, Darker 50% style. Select a function that calculates totals for all monetary columns.Ĭreate Line sparklines in column H in the Summary sheet to display six-month trends for each agent. Apply a filter to display trips arranged by agents Avery and Ross only.ĭisplay a total row. Sort the table by departure date from oldest to newest and then alphabetically by trip description. Apply the Table Style Medium 21 style to the table. Remove the conditional formatting rule on the Data (2) sheet.Ĭonvert the data range in the Data (2) sheet to a table. Click cell G10 and enter a function to display today’s date.Ĭopy the Data sheet and place the copied sheet before the Summary sheet. Insert a function to calculate the lowest trip cost in cell G8 and the highest trip cost in cell G9. Insert a function to calculate the total for all trips (including taxes) in cell G5, the average trip cost in cell G6, and the median trip cost in cell G7. Freeze the panes so that the row of column labels does not scroll offscreen.Īpply the Light Red Fill with Dark Red Text conditional formatting to values in the Total Cost with Taxes column when the values are above average. Set the column widths for these columns to 11, if necessary. Merge and center the subtitle on the second row over all data columns.Īpply Currency number format to the monetary values in columns F, G, H, and I. Merge and center the main title on the first row over all data columns on the Data sheet. The function should then calculate the monetary value of the commission. Copy the function down through cell H22.Ĭlick cell I13 and calculate the agent commission using the base cost of the trip and a VLOOKUP function that returns the commission rate based on the base cost of the trip using the rates lookup table. Make sure the result is a positive value. Use appropriate relative, mixed, and/or absolute cell references in the formula. Use the interest rate and months in the input area above the data. In cell H13, insert a PMT function to calculate the payments for students who want to pay for their trips in three installments. Use a mixed reference to the cell containing 20% in the input area above the data. 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. Copy the function down through cell E22.Ĭlick 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. Copy the function down through cell D22.Ĭlick cell E13 and insert a lookup function that looks up the tour code, compares it to the tours table, and returns the departure date. Assign the range name tours to the data for the package, tour description, departure, and base cost.Ĭlick cell D13 on the Data sheet and insert a VLOOKUP function that looks up the tour code, compares it to the tours table, and returns the description. Make sure the Lookup Tables sheet is active.Īssign the range name rates to the base price and commission range. Open exploring_ecap_grader_h1_Trips.xlsx and save the workbook as e00a1Trips_LastFirst. You will insert formulas and a variety of functions, convert data to a table, sort and filter the table, and prepare a chart.įor the purpose of grading the project you are required to perform the following tasks: Step You will complete the workbook for your manager’s approval. The workbook also contains a work- sheet to store lookup tables. You created a workbook to store agent names, student IDs, and tour codes. You work for a travel company that specializes in arranging travel accommodations for student tours and vacations in exciting destinations such as Canada, Rome, and the Czech Republic.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |