COSC 1301: Lab 7 (ExcelChapter 2) - Maya’s Travel Agency

You are an agent at a local travel agency, Maya’s Travel Agency. You are required to track all of the end-of-summer deals that you sell. Customers can purchase a package with or without airfare. You are required to collect a down payment based on 10% of the cost of the package. Most of your customers choose to pay for their vacations on a monthly payment plan for one year, which you calculate based on a standard interest rate. In addition, you need to calculate general statistics to summarize for your manager. Spot-check results to make sure you created formulas and functions correctly.

Assigned Reading

Excel Chapter 2

Perform Preliminary Work

  1. Open the Excel chapter 2 starter file (COSC1301_Excel2_Starter.xlsx). Add a Standard Header to the top of the page (read COSC 1301 Lab Instructions to see what goes in the header).
  2. Assign the name Deals to range A18:C22.
  3. Insert a function to display the current date in cell B2.
  4. Save the new file to your disk, diskette, or USB drive, as lastname_firstinitial_E2.xlsx.

Calculate Costs

You are ready to calculate the total costs. The total cost is determined based on each customer’s package type, using the lookup table.

  1. Use a VLOOKUP function in cell E5 to determine the package cost for the first customer, based on the Package. Use the Range Name Deals that you created in step b. above when you construct the function. Figure 2.31 on page 407 shows an example.
  2. Use a VLOOKUP function in cell F5 to determine the Flight Cost for the first customer, based on the Package. Again, use the Range Name Deals.
  3. Insert a function to calculate the Total Cost of the Package for the first customer in cell G5. If the customer needs a flight, the total cost is the package cost plus the flight cost. If a flight is not needed the total cost is simply the package cost. The IF function would work well for this.
  4. Copy the three formulas down their respective columns.

Down Payment and Balance

You need to collect a down payment based on 10% of the Total Package Cost. You must then determine how much each client owes.

  1. Determine the amount of down payment for the first client in cell H5by the results of a formulathat calculates 10% of the Total Package Cost.
  2. Insert a formulato display the balance in cell I5, which is the difference between the total package cost and the down payment.
  3. Copy both those formulas down their respective columns.

Calculate the Monthly Payment

Most clients pay the remainder by making monthly payments for ONE year. Monthly payments are thus based on 12 months and the standard interest rate provided.

  1. Use a PMT function to calculate the first client’s monthly payment in cell J5. (Note that the value in B3 isan annual rate and must be converted to a monthly rate for use in the PMT function), using appropriate relative and absolute cell references. Monthly payments are based on a 1-year term. Add a minus sign to the function so that the calculated amount displays as a positive number.
  2. Copy the formula down the column.

Finalize the Workbook

You need to perform some basic statistical calculations and finalize the workbook with formatting and page setup options.

  1. Insert a function to calculate the totals on row 13 for columns E through J.
  2. Using the COUNTA, MIN, AVERAGE, MAX, and MEDIAN functions, with cellsG5:G12, determine the corresponding statistics for cells I18:I22.Format the monetary values with Accounting Number Format.
  3. Format the other column headings on row 17 to match the fill color in range F17:I17. Hint: the Format Painter is the easiest way to do this. Wrap text for the column headings and center them.
  4. Format all monetary values on rows 5 through 13 to currency with 2 decimal places. Adjust column widths as necessary to view all data. Apply double underline style (found in the Home tab, font group, underline icon More button) for the totals. Apply single underline style for the numbers above the totals.
  5. Insert a footer with your name on the left side, the date in the center, and the file name on the right side. Use the Header and Footer Elements on the Design tab to make this job easier.

Submitting the Report

Before submitting the report, you see that it should be printed with landscape orientation to fit on one sheet of paper. If you are submitting a printed copy you will also want to show and print the cell formulas. (If you are submitting an electronic copy, you need not show cell formulas.)

  1. Change the page orientation to landscape.Set 0.4" left and right margins, and ensure the page prints on only one page.
  1. Save your changes; then use Print Preview, followed by a Page Setup to make sure the worksheet fits on one sheet.
  1. Online students should submit the worksheet, following the instructions given by your professor. If you are turning in hard copy, you should print the worksheet with displayed values, and then a second time with the cell formulas showing.

Sample Solution Follows

1