Excel AssignmentACCTG 333 – Professor Perols

Assignment Overview

The exercises below are designed to increase your effectiveness and efficiency in using Excel to

analyze data. Excel is widely used in accounting and students need to master Excel (AICPA

2022). This is an individual assignment to be completed on your computer (PC or Mac,

although a PC is preferred). You are allowed to discuss the assignment with others in the class,

but you may not copy. When you have completed the assignment, you will upload your Excel

Workbook file to Canvas under “Excel Assignment Submission.”

To complete these exercises, consult online help such as https://support.office.com/enus/excel and https://exceljet.net/ and the help feature within Excel (F1). Keywords for each

exercise are provided to assist you. Note that if the keyword for an exercise states for example:

OR() then your answer must use the OR() function. Also, your formula in one exercise cannot

cell reference your answer from a previous exercise.

Getting Started

Download the Northwind Excel Workbook from Canvas. Save the file to your computer and

rename it as your first initial and lastname, e.g., rperols.xlsx.

If you are a Mac user (and do not have access to a PC), stop and check for software updates

before proceeding. Help -> Check for Updates. Run/install updates before proceeding.

Unless stated in the instructions, no additional cells/data/formulas should be added beyond

the yellow highlighted cells in the Northwind Excel Workbook.

Practice Shortcut Keys

First spend time practicing the shortcut keys below. Make sure to use these shortcut keys

whenever you work in Excel (many of them also work in other applications). Learning these

(and other) shortcuts will save you a lot of time and are more or less necessary for you to be

efficient when you work. For macOS users, most of these shortcuts work with the

command button instead of CTRL. In the future, if you are provided a Windows based PC

for in your internships and/or job, I highly recommend all students practice these shortcuts

on a Windows based PC in order to be efficient (an important skill).

o F1 Displays the Help task pane.

o CTRL+A Selects the entire table

Use CRTL+A+A selects the entire worksheet.

o Ctrl+F Opens the ‘find text’ dialog box.

o CTRL+S Saves the active file with its current file name, location, and file format.

o CTRL+C Copies the selected cells.

o CTRL+X Cuts the selected cells.

o CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any

selection. Available only after you cut or copied an object, text, or cell content.

Page 1 of 6

Excel Assignment

ACCTG 333 – Professor Perols

o CTRL+ALT+V displays the Paste Special dialog box. Available only after you have cut

or copied an object, text, or cell content on a worksheet or in another program.

o CTRL+Y Repeats the last command or action, if possible.

o CTRL+Z Uses the Undo command to reverse the last command or to delete the last

entry you typed.

o CTRL+ARROW KEY moves to the edge of the current data region (data region: a

range of cells that contains data and that is bounded by empty cells or datasheet

borders) in a worksheet.

o CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in

the same column or row as the active cell, or if the next cell is blank, extends the

selection to the next nonblank cell.

o Shift + Spacebar Selects entire row.

o Ctrl + Spacebar Selects entire column.

o CTRL+HOME moves to the beginning of a worksheet (CTRL+SHIFT+HOME extends

the selection of cells to the beginning of the worksheet).

o CTRL+END moves to the last cell on a worksheet, in the lowest used row of the

rightmost used column (CTRL+SHIFT+END extends the selection of cells to the last

used cell on the worksheet).

o CTRL+PAGE DOWN moves to the next sheet in a workbook.

o CTRL+PAGE UP moves to the previous sheet in a workbook.

Exercises

1. First, practice a little formatting. Format the OrderDetails worksheet by formatting the

header row using top and bottom (but not side) borders, light grey fill color (the exact shade

of grey is not important), and bold and centered text. Also format the data using

appropriate data type format (determine what is “appropriate” by examining the content of

each column, e.g., Discount should be percentages and UnitPrice should be currency).

Expand all the columns so that all the data are visible (select the entire worksheet, i.e., use

CTRL+A+A, and double click on a line between two Excel Worksheet Column Headers, e.g.,

between A and B above the table headers. You do not need to do more formatting for the

assignment beyond this brief practice in exercise 1.

2. Before getting started on the formulas, take some time to understand the data. For

example, note that workbook contains two files, OrderHeaders and the OrderDetails, which

contain archive transaction information. Each row in the OrderHeaders table represents a

distinct order. Each order can, however, have many rows in the OrderDetails table because

a given order can be for multiple items. Each row in the OrderDetails table is associate with

a specific order header row and a specific item. Each row also shows the quantity, unit

price, and discount for the sale of the item. Remember that in the Systems Understanding

Page 2 of 6

Excel Assignment

ACCTG 333 – Professor Perols

Aid (SUA) assignment, the top part of orders contained OrderID, date, customer

information, and supplier information while the bottom part contained rows with

information about the actual items sold (this structure of storing the header portion and the

detail portion is very common for different accounting objects, e.g., purchase orders,

receiving reports, invoices, sales orders, and sales invoices). Also note that the other tables

contain master tables, e.g., Suppliers, Customers, and Products, where each row contains

information related to a single Supplier, Customer, Product, etc. No action/answer is

required for exercise 2 (just an overall understanding of the data).

3. Cell References and Calculations – In the OrderDetails worksheet, in cell F2, calculate the

LineItemTotal as UnitPrice * Quantity * (1-Discount) using cell references. Format this cell

as currency with two decimal places and copy down cell F2 to the bottom of the table, i.e.,

F2 through F2156, using short cut keys: CTRL+C, ARROW LEFT, CTRL+ARROW DOWN,

ARROW RIGHT, CTRL+SHIFT+ARROW UP, CTRL+V. Note that this is a very common

sequence of commands that you want to learn (do not memorize it, just practice it

throughout this assignment when you need to copy down a formula).

4. Absolute and Relative Cell References – In the OrderDetails worksheet, in the yellow

highlighted cell I2, enter 5%. In cell G2 calculate the LineItemTotal with Additional Discount

as UnitPrice * Quantity * (1-Discount-Additional Discount) where the additional discount is

held constant at the value in cell I2. Note that you need to use both relative and absolute

cell references for this formula to work correctly. There is also a shortcut (F4) for applying

absolute cell references. Copy down the formula in cell G2 to the bottom of the table, i.e.,

G2 through G2156, using short cut keys (for the rest of the assignment, assume that you

should copy down formulas if the formulas relate to each row in a table).

5. VLOOKUP() – First, in the OrderHeaders worksheet, in column C use a vlookup to display the

company name from the Customer worksheet for each order. Please see the short video I

posted on Canvas to help you through the first part of this exercise.

Second, in the Products worksheet, in Column D use a vlookup to display the country from

the Suppliers worksheet for each supplier.

6. COUNT(), AVG(), SUM(), MAX(), and MIN() – In the OrderDetails worksheet, in cells B2159,

C2159, D2159, E2159, and F2159 calculate the Total Number of Line Items (number of line

item rows), Average UnitPrice (average of unit price in column C) Total Quantity (sum of all

line item quantities in column D), HighestDiscount (maximum of all discounts in column E),

and Smallest LineItemTotal (minimum of all LiteItemTotals in column F).

7. COUNTIF(), AVERAGEIF(), and SUMIF() – In the OrderDetails worksheet, in cell A2162 enter

51. In cells B2162, C2162, and D2162 use COUNTIF() to calculate Number of Product 51

Sales, AVERAGEIF() to calculate Average Unit Price of Product 51, and SUMIF() to calculate

the Quantity Sold of Product 51 (use a cell reference in your formulas to the value in A2162

so that your count, average, and sum updates when the value in A2162 changes).

Also, in the Employees worksheet, in column M use SUMIF to show Total Sales (column O in

OrderHeaders) for each employee ID. Note each employee is associated with many orders.

Page 3 of 6

Excel Assignment

ACCTG 333 – Professor Perols

8. IF() – In the OrderDetails worksheet, in column K create an if statement that returns “Yes” if

the Quantity (values in column D) is above 40 (strictly greater than) and otherwise “No”.

9. AND() and OR() – In the OrderDetails worksheet, in columns L and M use IF statements with

an AND() and OR(), respectively, to return “Yes” if Quantity is between 30 and 40 (strictly

greater than 30 and equal to or less than 40), and otherwise “No”. The OR() requires more

thinking (see tips in the Check Figures document).

10. Nested IF() – In the OrderDetails worksheet, in column N use a nested IF statement to return

“Yes” if Quantity is between 30 and 40 (strictly greater than 30 and equal to or less than

40), and otherwise “No” (see tips in the Check Figures document).

11. Missing values – In the OrderHeaders worksheet, in column Q create an if statement that

returns “Yes” if the ShippedDate is missing (indicated inside the if statement as an empty

string, i.e., two quotation marks in a row without whitespace), and otherwise “No”.

12. Comparing Existing Dates – In the OrderHeaders worksheet, in column R create an IF

statement that returns “Yes” if the ShippedDate is on or after the RequiredDate, otherwise

“No”. Note that a date in Excel is stored as a number representing the number of days since

1/1/1900. The number is simply formatted to display as a date. Because of this you can

directly compare two dates that are already in an Excel worksheet (you are really comparing

two regular numbers). To demonstrate, change the formatting of the value in G2 to a

number. Notice that the cell is actually storing 42762 (the number of days between

1/27/2017 and 1/1/1900). Also note that the formatting will not change your answer for

exercise 12.

13. YEAR() and MONTH() – The YEAR() and MONTH() functions are used to get the year or

month of a date stored in Excel. In the OrderHeaders worksheet, in columns S and T use

YEAR() and MONTH() to return the OrderDate year and month, respectively. In column U,

use YEAR() inside an IF statement to return “Yes” if the OrderDate is in 2018 and otherwise

“No”. In column V, use YEAR(), MONTH(), and AND() inside an IF() statement to return

“Yes” if the OrderDate is in the first quarter of 2018, and otherwise “No”.

14. DATE() – It is not as easy to compare a date already in Excel to a date that we want to

specify. If we type in 1/12/2019, Excel will interpret this as 1 divided by 12 divided by 2019

and to compare 1/12/2019 to other dates in Excel we therefore first need to convert the

date to a number that represents the number of days since 1/1/2019. This can be done

using DATE().

In the OrderHeaders worksheet, in cell AB2 simply use DATE() or DATEVALUE() to find out

how many days there have been since 1/1/1900 and the due date of this assignment

11/30/2023. Change the cell formatting to a number if the results from DATE() are

formatted as a date.

Note that the function DATEVALUE() works the same way as DATE(), but converts a date

string, e.g., “11/2/2019”, rather than integers separated by commas, e.g., 2019,11,2, to the

number of days since 1/1/1900.

Page 4 of 6

Excel Assignment

ACCTG 333 – Professor Perols

In column W use DATE() and AND() inside an if statement to return “Yes” if the OrderDate is

in the first quarter of 2018, and otherwise “No”.

In column X, use DATE() only inside an if statement to return “Yes” if the order date is after

(strictly greater than) 10/15/2018, and otherwise “No”.

In column Y, use AND() and DATE() inside an if statement to return “Yes” if the order date is

before (strictly less than) 11/18/2018 and has not yet been shipped, and otherwise “No”.

15. SUMIFS – In the Employees worksheet, in column N use SUMIFS (not SUMIF) to show:

Total Sales (column O in OrderHeaders) for each employee but only include orders that

have been shipped (column G in OrderHeaders) in this calclation. Note that an order has

been shipped if ShippedDate is not empty, indicated using “” (note that there is nothing

to the right of as blank in Excel is indicated by nothing.

16. LEFT(), RIGHT(), FIND(), and LEN() – In the Customers worksheet, in columns O, P, Q, R, and S

use LEFT() to find the first eight characters of ContactName, use RIGHT() to find the last nine

characters of ContactName, FIND() to find the number of characters before space in

ContactName (you need to use FIND()-1 for this), LEN() to find the number of characters in

Contact Name, and LEN() and FIND() to find the number of characters after the space in

Contact Name.

In column D use LEFT() and FIND() to show the first name of the customer contact (shown in

column C). Note that you need to embed the FIND() inside the LEFT() functions and use

FIND to return the location of the character that separates the first name and the last name.

In column E, use RIGHT(), LEN(), and FIND() to show the last name of the customer contact

(and you again need to embed formulas)

Note that when embedding a formula inside another formula the behavior of the nested

formula does not change, e.g., FIND() finds the first occurrence of one string inside another

string and always searches from the left even when embedded within RIGHT().

Pivot Tables

Complete exercises 17-20 before comparing your answer to the Check Figures document.

17. Creating Pivot Tables

a. Use the Sales worksheet data to create a Pivot Table into a new worksheet. Name the

new worksheet SalesPivot. For Mac users especially, do not create the pivot table by

selecting the worksheet (data). Instead, from the Sales worksheet, simply Insert -> Pivot

Table and the data will be automatically selected.

b. Click and Drag ProductName and CustomerName to Rows, OrderDate to Columns (year

and quarter will also be added), and LineItemTotal to Values. For Mac users especially,

“drag” OrderDate to Columns to ensure that year and quarter will also be added.

c. Rearrange the Rows fields to show all customers and the products that they have

purchased (rather than all products and the customers that purchased those products).

Page 5 of 6

Excel Assignment

ACCTG 333 – Professor Perols

d. In the Column Labels, use the + button to expand the pivot table show Year and then

Quarter. If the + button is not available go to Pivot Tables tab -> Analyze -> Show -> +/Buttons. Note that the Pivot Table now groups the sales data based on CustomerName,

ProuctName, and the quarter of the OrderDate and then sums LineItemTotal.

18. Other Aggregate Functions in Pivot Tables

a. Use the – button to collapse the pivot table details back to the annual level (view the

data grouped by year rather than quarter).

b. Add Discount to Values. Calculate average discount (rather than sum) and change the

format to percentage with one decimal. To do this, use Value Field Settings (accessed

by double clicking or right clicking on the Sum of Discount header, or by opening the

drop down menu for the Sum of Discount in the Values field selector).

c. Add OrderID to Values and count how many line items are being grouped. Inside Value

Field Settings, change the format to number with zero decimals.

d. Inside Value Field Settings, change the format of Sum of LineItemTotal to currency with

zero decimals.

19. Formatting Pivot Tables

a. In Design Subtotals, select to not show subtotals and Grandtotals (turn off for both

rows and columns).

b. In Design Report Layout, select Show in Tabular Form

c. In Design Report Layout, select Repeat All Item Labels

d. Change the name of the columns headers (you can make these changes directly in the

column headers or in Field Settings) to Customer Name, Product Name, Average

Discount, Number of Order Lines. (Note: do not change Sum of LineItemTotal).

e. Replace all empty cells with 0 (right click inside the pivot table, select Pivot Table

Options and set “For empty cells show:” to 0.

20. Filtering and Slicing Pivot Tables

a. Filter customer names to only show customers that begin with B by left clicking the filter

icon to show the filter drop down menu (the little triangle in the column header in the

same cell as the text Customer Name), selecting Labels Filters, and Begins With.

b. Filter product names to only show products that begin with letters between O-Z using

Labels Filters Between…

c. Insert a Slicer using OrderDate and select Feb, May, and Aug.

21. Obtaining Details from Pivot Tables

a. For Customer Name and Product Name: Bon app’ and Pavlova, double left click the

Number of Order Lines for 2018 (double left click on the 2). Note that the details of the

two order lines will display in a new worksheet.

b. Change the new worksheet name to Bon App Pavlova 2018 Details.

Page 6 of 6

The price is based on these factors:

Academic level

Number of pages

Urgency

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Delivering a high-quality product at a reasonable price is not enough anymore.

That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more