Download the file HW6_Excel Assignment(Spring 2020).xlsx from Blackboard to your computer.
others
Description
MIS 303 Spring 2020
HW6: Excel Assignment – Instructions
In this assignment, you are to follow the instructions to
complete and submit the assignment as individuals. Please make sure you follow
the instructions closely and complete all tasks on multiple worksheets.
1.
Download the file HW6_Excel Assignment(Spring 2020).xlsx from Blackboard to your
computer.
2.
Rename the file as YourFirstName-YourLastName.xlsx. You should fill
your own first and last names to replace the parts of YourFirstName and YourLastName
in the file name. E.g., John-Smith.xlsx
3.
Open the file using Excel 2016. Rename the
worksheets as suggested below. In the following instructions, each worksheet
will be referred with the new name only.
|
Sheet1 à |
2016 Orders |
|
Sheet2 à |
Income Statements |
|
Sheet3 à |
Pivot Tables |
|
Sheet4 à |
Business Charts |
|
Sheet5 à |
Goal Seek |
|
Sheet6 à |
Solver Analysis |
4.
On the sheet of 2016 Orders, complete the following tasks.
a.
Make of list on the entire dataset.
b.
Sort the dataset by Product first and then by Region.
(Other idea: By Salesperson first
and then by Month)
c.
Add a column at the right of Product. Name it Price. Use vLookup function to pull product prices from the sheet Income Statements (D4:D7) and show
prices for all orders.
d.
Add a column at the right of Unit. Name it Total. The order totals = Price
* Unit.
e.
At the right of Total, add a column called Discount.
Use a nested If function to decide the discounts. The company gives 10%
discount to all orders from the West region, and the other regions only enjoy
10% discount in December.
f.
At the right of Discount, add a column called Totalw/Dis.
It is the order totals – discounts.
g.
In cell L2, use the AVERAGE function to calculate
the average number of units sold from the column Unit. Next, use conditional formatting tool and highlight the
orders with unit sold greater than average unit sold. Select the green fill and
dark green text color option if the cells meet this rule.
5.
On the sheet of Income Statements, complete all the green-colored cells using Excel
functions or formulae.
·
Revenue is
Units Sold * Unit Price and COGS is
Units Sold * Unit Cost.
·
Total
Revenue is the sum of the revenues for all products.
·
Total
COGS is the sum of the COGS
for all products.
·
Gross
Profit = Total Revenue – Total COGS
