Download the homework assignment workbook from Brightspace – Week 1 Folder.
Your basic task is to enter the data below into the appropriate cells of the workbook. Once you have entered the raw data you need to develop the formulas for the calculated fields, then format the worksheet to match the look of the finished worksheet below. The next page shows what your worksheet should look like when you are done.
William F. Kunstler
34 Ocean Avenue
Santa Monica, CA 90402
Invoice No. 1302
The items purchased are in the table below.
You will need to perform additional formatting to complete the assignment.
Solved Practice Worksheet
Basic steps for completing the assignment.
- Make cell E21 the active cell.
- Write a formula that calculates the extended price by multiplying the unit price by the quantity purchased.
- Copy the formula from E21 to the cell range E22 to E25.
- Make cell E26 the active cell.
- Write a formula that uses the =SUM() function to Subtotal the extended price values from the cell range E21 through E25.
- Make cell E27 the active cell.
- Write a formula that calculates the member discount by multiplying the sub total times the discount rate in cell c27.
- Make cell E28 the active cell. Calculate the sub total after the member discount is applied by subtracting the member discount from the sub total (cell E26).
- Make cell E29 the active cell. Write a formula that calculates the sales tax amount by multiplying the sub total after the member discount by the sales tax rate in cell C29.
- Make cell E30 the active cell. Write a formula that calculates the grand total on the invoice by adding the sales tax amount to the sub total after the discount is applied.
- Make cell C31 the active cell. Use the =AVERAGE() function to calculate the average unit price of the products purchased (cell range C21 through C25).
- Make cell C32 the active cell. Use the =MAX() function to calculate the most expensive unit price for the same cell range.
- Make cell C33 the active cell. Use the =MIN() function to calculate the least expensive unit price for the same cell range.
- Create the custom header and footer from your class documentation for the worksheet.
- Rename the worksheet tab from Sheet1 to Invoice-Template
- Save the workbook as an Excel workbook using your last and first names as in