Business Tech II: Excel Projects

Project 1 Project 2 Project 3 Project 4
Project 5 Project 6 Project 7 Project 8

Project 5 [Top]

You will learn how to . . .

  • Label tabs (rename, color)
  • Insert and delete tabs
  • Add tab names to headers/footers
  • Enter identical information on multiple tabs at the same time
  • Hyperlink to places within the worksheet
  • Create 3-D references

At Business Tech Realty, you want to create a worksheet where realtor sales are kept separately per employee, but summarized for all realtors for the year.

  • Start a new worksheet. Save it as excel_project5.xlsx in your H:\ drive.
  • Watch Tutorial 5-1. Using the information from Project 3, create a multi-tab worksheet
    • Rename tabs so that one tab represents a summary.
    • The other tabs represent employees by name. It is optional whether or not you color code the tabs.
  • Create cells on the summary tab as described below, leaving enough room at the top to later add the company logo and contact information (same as Project 1). Adjust columns as necessary to accommodate cell content.

  • Watch Tutorial 5-2. Create basic information for all employee tabs at the same time as described below, leaving enough room at the top to later add the company logo and contact information. Adjust columns or merge cells as necessary to accommodate cell content.
  • On the employee tabs, using information from Project 3, enter the sale information for each employee, calculating the total sales for the year and the percent of total sales for each quarter (Total sales / quarterly sales).
    • Use the same name for employees from Project 3
    • You can select the city or town the realtor represents
    • Format all dollar amounts for currency, no decimal places, and no $$ signs.
    • Format all percents for a percent with 1 decimal place.
    • Total the percent column (hint: If your percentage calculation is correct, the percent column should total 100 percent).
  • Watch Tutorial 5-3. On the summary tab, create a hyperlink on each employee's name to the employee's tab.
  • On the summary tab, using a 3-D reference, enter a formula to take the total sales from each employee tab and enter it in the appropriate cell on the summary tab.
    • Calculate the percent of total sales per employee.
    • Format all dollar amounts for currency, no decimal places, and no $$ signs.
    • Format all percents for a percent with 1 decimal place.
    • Total the percent column.
  • Watch Tutorial 5-4. On ALL tabs, insert the company logo and company information at the top, to match the style you used in Project 3.
  • Customize the footer of each tab to include the following: left-aligned, the tab name; right-aligned, your name, file name and project number.
  • Submit excel_project5.xlsx to Moodle.

Project 6 [Top]

You will learn how to . . .

  • Create and move charts
  • Add elements such as legends, titles, axis titles, etc. to charts
  • Format charts

Create the following charts from Excel files you already created in previous projects. Each chart should have your name, project number and file name in a footer and be on a separate tab in the original worksheet. Delete unused tabs in worksheet.

  • Chart 1. Watch Tutorial 6-1.
    • Open your excel_proj3.xls file.
    • Create a column chart (any style) which shows quarterly sales for all employees
    • Place the chart on a new tab named Annual Sales 200X
    • Your chart must include X and Y axis titles, a centered title at the top, and a legend at the bottom
    • Customize the footer to include the following: left-aligned, the tab name; right-aligned, your name, file name, and project number.
    • Save file as excel_proj6a.xls.
  • Chart 2. Watch Tutorial 6-2.
    • Open your excel_proj4.xls file.
    • Merge heading rows so all text is in one cell. Wrap text if necessary.
    • Create a line chart which shows employee total earnings, total deductions, and net pay
    • Place the chart on a new tab named Line Chart
    • Your chart must include X and Y axis titles, a centered title at the top, and a legend at the top
    • Add data labels to the points.
    • Customize the footer to include the following: left-aligned, the tab name; right-aligned, your name, file name, and project number.
    • Save file as excel_proj6b.xls.
  • Chart 3. Watch Tutorial 6-3.
    • Open your excel_proj5.xls file.
    • Highlight employee names and sales on the Summary tab.
    • Create a pie chart of total sales.
    • Move the chart to its own tab. Name the tab Pie Chart.
    • Modify the chart title to Annual Sales by Employee.
    • Move the legend to the bottom.
    • Add data labels to the chart where it is most readable.
    • Customize the footer to include the following: left-aligned, the tab name; right-aligned, your name, file name, and project number. If necessary, resize the graphic so all will appear when printed.
    • Save file as excel_proj6c.xls.
  • Upload the following files to Moodle.
    • excel_proj6a.xls
    • excel_proj6b.xls
    • excel_proj6c.xls

Project 7 [Top]

Microsoft Word can be used to create many documents such as Invoices, Purchase Orders, etc. The problem with using Microsoft Word for this type of form is that Word has limited capabilities to perform mathematical calculations. It is possible to integrate an Excel worksheet into a Word document. This will allow the Word document to have the formula capability of a worksheet.

  • Watch Tutorial 7-1. Save this Word Invoice to the same folder your Excel files are being saved. This is an invoice created in Microsoft Word that you will modify.
    • Delete the table with the Quantity, Description, Price, and Total from the file.
    • Open up Excel and re-create the section you deleted (see above). Add a background color to the heading cells.
    • Adjust column widths until they are about the same as the original Word document.
    • Format all column with dollar amounts to numbers with commas, two decimal places, and no dollar signs.
    • Format the Quantity column to a number with commas, no decimal places, and no dollar signs.
    • Add a formula to the Total column to times Price X Quantity. Copy to all total cells.
    • Use a formula to subtotal the total column.
    • Use a formula to calculate a 6% sales tax.
    • Use a formula to total the subtotal and sales tax.
    • bold and right-align the words Subtotal, Tax, and Total.
    • Save your file as excel_proj7.xls.
  • Watch Tutorial 7-2. Open the Word document and click where the previous table was located.
    • Insert the Excel worksheet as an object from a file.
    • Test your worksheet by entering in the information below.
    • Verify your formulas calculated correctly.
    • Add customer information as listed below. Do not allow columns to wrap. Adjust column widths if necessary.
  • Insert your name, project number and file name right-aligned in a footer in both documents
    (Word and Excel).
  • Submit the excel_project7.xlsx to Moodle along with your Word invoice document.

Project 8 [Top]

You are to create a customer order form for a company that sells items from a catalog. In your order form, you want to create formulas to calculate total price per item, subtotals, and sales tax. You also want the form to lookup the correct sales tax rate, item descriptions, and item prices.

Create a new workbook and rename the tabs as:

Obtain a catalog from Mrs. Ewert that you want to use for your order form. Create an order form on the Invoice tab similar to the example below. Locate the company on the internet and utilize the company logo and address at the top. Do not include quantities, item numbers, descriptions, or prices at this time. Formulas will be used later to add those items. Format cells for the proper alignment and format prior to entering information (i.e. phone numbers, date =now(), format date, etc. . .)

Find a minimum of 15 items in the catalog and enter the data on the Items Table tab. Include item numbers, descriptions and prices from your catalog. Sort the items in ascending order by Item No.

Watch Tutorial 8-1. Go back to your invoice tab and enter VLOOKUP formulas in the Description and Price columns. Add a a minimum of five items in the invoice to test your formula. Enter in formulas to calculate totals per item and the subtotal.

Watch Tutorial 8-2. A file has been created for you that includes the state names and tax percentages. Right-click HERE to save the text file to your network folder. Open the text file in a new tab, and follow the wizard to place the information in columns. Make sure the column is sorted in ascending order by State Abbreviation.

Use a VLOOKUP statement to place the State Name and Tax percentage at the bottom right-corner of the customer order form. Then, create a formula to calculate the sales tax for the order. Test your VLOOKUP statement for a few different states to verify the formula works correctly.

Before finishing, add customer information in the worksheet and verify that the calculations work properly. Insert a footer with your name, project number, file name, and tab name in the footer. Upload your file to Moodle.

TOP