Applications Programming --- Lab 2

Objectives:

• Get familiar with Excel
• Get familiar with Excel formula, especially with various types of cell references and some commonly used built-in functions

References:

• Lab 1 references
• Excel
• Advanced Excel

Problem Description

An online bookstore keeps a list of books for sale. Each book has an ISBN number, a title, a unit price and a short description. An example of such data collection is shown in the worksheet named "Books" in the input file of this lab.

The bookstore also uses a worksheet named "Transactions" to keep records of book sales. Each time a customer buys a book (maybe multiple copies of the book), a record with the customer's email address, the date of the transaction, the ISBN of the book and the number of copies the customer bought in this transaction are recorded to the "Transactions" worksheet.

Part of the sample book data was selected from chapters.indigo.ca. The ISBN part of the data was made up randomly, and the description of the books should include more information of the book besides the author of the book. The bookstore is assumed to be in Canada and sells books to Canadians. Thus, the bookstore will charge 5 percent of GST tax on each book sale transaction. The GST tax rate is subject to change although it doesn't change frequently. The current GST tax rate is recorded in cell F2 in the "Books" worksheet.

The transaction data in this assignment was entirely made up.

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 are way more data in these two worksheets, especially in the Optional Order list.

Your tasks

• Download the Lab 2's input workbook: lab2-input.xlsx
• Enter an Excel formula to cell E2 in "Transactions" worksheet to find the title of the book in this transaction (based on the book's ISBN). The requirement is that this formula can be copied and pasted (without any manual modification) to all the cells in column E of other transactions to find the title of the book in each transaction.
• Enter an Excel formula to cell F2 in "Transactions" worksheet to find the unit price of the book in this transaction. Again, without any manual modifiction, we can use this formula to find the unit price of the books in other transactions.
• In column G2, enter an Excel formula to calculate the before-tax charge of this transaction as the multiplication of unit price and number of copes.
• In column H2, enter an Excel formula to calculate this transaction's tax as the before-tax charge multiply the book tax rate of 5 percent.
• In column H2, enter an Excel formula to calculate this transaction's tax as the before-tax charge multiply the book tax rate of 5 percent.
• In column I2, enter an Excel formula to calculate this transaction's total charge as the sum of before-tax charge and tax charge.
• Add a new worksheet named "Summary". In this newly added worksheet, insert a pivot table. The pibot table should make it easy for the bookstore manager to do inventory control by finding the sales data (total number of copies and total dollar amount generated by the sales) for each book (identified by their ISBN) and each day.
• Don't forget to format all the data cells in appropriate type so that their values are presented in human readable and easily understandable format.
• Save your work.

In your formula, if possible, you MUST always use cell references instead of literal values. Pay attention to the cell reference types, so that copy/pasted formula always give you the right result.

There is a weekly assignment 2 following this lab. The submit deadline of Assignment 2 is 13:00, 1 October 2026, Thursday.