Business Finance Homework Help

I need help to solve those questions in Excel please,

Excel Assignment

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 as https://support.office.com/en-
us/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 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:
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+P Displays the Print dialog box.
o CTRL+S Saves the active file with its current file name, location, and file format.
o F12 (or Alt+F+S+A) Displays the Save As dialog box.
o Ctrl+O Opens the ‘Open’ dialog box.

Excel Assignment

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.
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 CTRL+PAGE DOWN moves to the next sheet in a workbook.
o CTRL+PAGE UP moves to the previous sheet in a workbook.
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).
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.

Excel Assignment

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 (don’t 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).

Excel Assignment
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”.

Excel Assignment

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().

Excel Assignment

Pivot Tables (exercise 17-21 combined).
https://www.goskills.com/Excel/Articles/Advanced-p…
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.

Excel Assignment

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.