CSCI 112
Fall 2021 - Assignment 2
Submit deadline: 11:30am, 27 September 2021, Monday

Objective:

• Get familiar with Excel
• Get familiar with Excel formula, especially with cell references and some basic built-in functions

References:

• Lab 2
• Excel
• Advanced Excel

Problem Description

A hypothetical department store keeps a list of products for sale. Each product has a unique product ID, a name and a unit price. An example of such data collection is shown in the worksheet named "Products" in the input workbook of assignment 2.

The department store also has some sales representatives that sell products and earn commission from the sales. In the worksheet named "Sales Personnel", the sales representatives' names and their respective commission rates (presumably based on their different seniority) are stored.

The department store then uses a worksheet named "Sales Data" to keep records of the product sale transactions. Each sale transaction record collects and stores the name of the sales person who made the sale (in column "Sales Representative"), the date the transaction happened (in column "Sales Date"), the product ID of the product (in column "Product ID") and the number of units of this product (in column "# of Units") being sold.

All the sales of the products in the department store are subject to sales tax. And the sales tax rate (12 percent for now) is stored in Cell E2 in the worksheet named "Products".

Note that the data shown in the input file is only a sample collection to show you the format of the data. You should assume that there is more data in these three worksheets.

Your tasks

• Download the input workbook of this assignment: A2-input.xlsx
• Enter an Excel formula to cell E2 in "Sales Data" worksheet to find the unit price of the product sold in this transaction based on its product ID.
• In cell F2, enter an Excel formula to calculate the before-tax sales amount of this transaction as the multiplication of the unit price and the number of units.
• In cell G2, enter an Excel formula to calculate the after-tax sales amount of this transaction as the sum of before-tax sales amount and the tax amount (calculated as before-tax sales amount times the tax rate).
• In cell H2, enter an Excel formula to calculate the commission the sales representative can earn from this sales transaction. And commission is calculated as the before-tax sales amount times this sales person's commission rate.
• Add a new worksheet named "Summary". In this newly added worksheet, insert a pivot table to help the department store manager to find that, for each sales representative, how many transactions he/she has done and how much total commission he/she has earned based on the transaction data collected and stored in the worksheet "Sales Data".
• Save your work to a file named your Lastname_A2.xlsx.

Therefore, in your formula, if possible, you MUST always use cell references instead of literal values. Pay attention to the cell reference type, so that copy/paste formula always gives you the right result.

How to submit:

To submit your assignment, name your solution file your Lastname_A2.xlsx, then login to your VIU Learn account, find the CSCI 112 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A2". Then you can click on the "Add a File" button to browse and upload your solution file Lastname_A2.xlsx.

If you have difficulties to make the VIU Learn submission work, please email me to let me know and attach your Excel solution file with your email.