CSCI 112
Fall 2025 - Assignment 1
Submit deadline: 11:30, 22 September 2025, Monday

Objective:

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

References:

• Lab 2
• 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 workbook of Assignment 1.

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 could 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.

Your tasks

• Download the input workbook of this assignment: A1-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 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 to a workbook with meaningful file name.

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

How to submit:

To submit your assignment, 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 assignment folder named "A1". Then you can click on the "Add a File" button to browse and upload your Excel solution file.

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.