The exercises below are designed to increase your effectiveness and efficiency in using Excel to analyze data.
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 2019).
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.
You will have class time to work on the assignment and you may email me questions anytime.
You must upload your completed Excel Workbook to Canvas under Excel Assignment Submission.
To complete these exercises, consult various online help such asand 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 include the OR() function. You may use other functions in addition to the required keyword function.
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.
Unless stated in the instructions, no additional cells/data/formulas should be added.
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.
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. For all users, I recommend that you spend a few hours the week before you start working using a PC to practice these shortcuts and the formulas you will be learning in this assignment:
oF1 Displays the Help task pane.
oCTRL+A Selects the entire table
§Use CRTL+A+A selects the entire worksheet.
oCtrl+F Opens the find text dialog box.
oCTRL+P Displays the Print dialog box.
oCTRL+S Saves the active file with its current file name, location, and file format.
oF12 (or Alt+F+S+A) Displays the Save As dialog box.
oCtrl+O Opens the Open dialog box.
oCTRL+C Copies the selected cells.
oCTRL+X Cuts the selected cells.
oCTRL+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.
oCTRL+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.
oCTRL+Y Repeats the last command or action, if possible.
oCTRL+Z Uses the Undo command to reverse the last command or to delete the last entry you typed.
oCTRL+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.
oCTRL+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.
oCTRL+PAGE DOWN moves to the next sheet in a workbook.
oCTRL+PAGE UP moves to the previous sheet in a workbook.
oShift + Spacebar Selects entire row.
oCtrl + Spacebar Selects entire column.
oCTRL+HOME moves to the beginning of a worksheet (CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet).
oCTRL+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).
Exercises: Formulas (and some formatting)
1.Format the OrderDetails worksheet, format 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 is 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.
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 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 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.
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 (dont 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 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 cell references and absolute cell reference 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() In the OrderHeaders worksheet, in column C use a vlookup to display the company name from the customer worksheet for each order. Note VLOOKUP from another sheet or across sheets.
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 the ProductID 16. In cells B2162, C2162, and D2162 use COUNTIF() to calculate Number of Product 16 Sales, AVERAGEIF() to calculate Average Unit Price of Product 16, and SUMIF() to calculate the Quantity Sold of Product 16 (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 is changed).
In the Employees worksheet, in column M use SUMIF to show Total Sales (column O in OrderHeaders) for each employee ID.
Note that each employee is associated with many orders. Also keep in mind absolute cell references.
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.
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
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 dates in Excel are stored as numbers, and then formatted to display dates.
These numbers represent the number of days since 1/1/1900. Because of this you can directly compare two dates that already are in an Excel worksheet (you are really comparing two regular numbers).
However, it is not as easy to compare a date already in Excel to a date that we want to specify.
If we type 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 and DATEVALUE (see below).
The YEAR() and MONTH() functions are used to get the year or month of a date stored in Excel, e.g., the year of 43,771 is 2019 (43,771 represents the number of days between 1/1/1900 and 11/2/2019).
13.YEAR() and MONTH() – 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() and MONTH() inside an IF() statement to return Yes if the OrderDate is in the first quarter of 2018, and otherwise No.
14.DATE() – In the OrderHeaders worksheet, in cell AB2 use DATE() and find out how many days there have been since 1/1/1900 and 12/2/2021 (you can enter the assignment due date inside DATE(). Change the formatting to a number if the results from DATE() is formatted as a date. Also change the formatting of the value in G2 to a number. Notice that the results in Question 12 do not change.
In column W use DATE() 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() 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.
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.
15.SUMIFS – In the Employees worksheet, in column N use SUMIFS 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 seven characters of ContactName, use RIGHT() to find the last 11 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 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 columnd 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 (exercise 17-21 combined).
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 have purchased those products).
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 the letter 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.
