Assignment 1: vLookup & Excel Functions
1 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 le Excel Assignment.xlsx from Blackboard to your computer.
2. Rename the le as YourFirstName-YourLastName.xlsx. You should ll your own rst and last names to replace
the parts of YourFirstName and YourLastName in the le name. E.g., John-Smith.xlsx
3. Open the le using Excel 2016. Rename the worksheets as suggested below. In the following instructions, each
worksheet will be referred with the new name only.
Sheet 1 – 2016 Orders
Sheet 2 – Income Statements
Sheet 3 – Pivot Tables
Sheet 4 – Business Charts
Sheet 5 – Goal Seek
Sheet 6 – Solver Analysis
2 Tasks
2.1 vLookup & Excel Functions
1. On the sheet of 2016 Orders, complete the following tasks:
Sort the dataset by Product rst and then by Region. (Other idea: By Salesperson rst and then by
Month)
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.
Add a column at the right of Unit. Name it Total. The order totals = Price * Unit.
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.
At the right of Discount, add a column called Totalw/Dis. It is the order totals { discounts.
1
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 ll and dark green text color option if the cells meet this rule.
2. 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 Pro t = Total Revenue { Total COGS
Salaries is 12% of Total Revenue
Advertising is 4% of Total Revenue
Miscellaneous expenses are 1% of Total Revenue
Total Operating Expense = Salaries + Advertising + Miscellaneous
Earning Before Taxes = Gross Pro t { Total Operating Expense
Calculate Taxes based on tax rate of 25% of Earning Before Taxes
Net Pro t = Earnings Before Taxes { Taxes
3. On the sheet of Pivot Tables, create a pivot table. You should start with the sheet of 2016 Orders as it contains
all your raw data. But when you choose the location to place your pivot table, make sure you choose an existing
sheet { the sheet of Pivot Tables, and click on the cell A1 to place the pivot table separately. Here are some
suggested ideas for pivot tables: Product sales by Salespersons, Seasonal sales by Regions.
Requirements:
Add meaningful title for your pivot table
Use proper grouping elds and summary elds.
Use proper structure of the pivot table to ensure readability
Use proper type and format for summarized values
Add a short paragraph below or on the side of the pivot table and describe 3 major ndings from this
table.
2.2 Goal Seek
On the sheet of Goal Seek, nd the way to achieve your 2017 pro t goals.
1. On the sheet of 2016 Orders, complete the following tasks:
Go back to Income Statement sheet. It must be completed by now. Copy the cells A3:G23 to Goal Seek
sheet, by pasting it at the cell A3.
The cell J3 should show a label Price Increasing Rate; and J4 should show 5%. If they are not there
because of your copying and pasting, please add them to your sheet at proper locations.
Edit your data sheet and include 5% increase on Unit Price of all products. Make sure you use formula
with reference to J4, where it shows the increasing rate of prices.
Use the Goal Seek tool and see if we want to achieve a $125; 000 net pro t, what is the increasing rate of
price we should apply in 2017?
2
2.3 Scenario Analysis
Go back to the sheet of Income Statement. Use the scenario manager to create three scenarios stated below and
create a scenario summary, which will be generated as a separate worksheet.
1. You will create 3 di erent scenarios by changing the product pricing mix in order to determine their impacts
to Net Pro t:
The First Scenario is to raise the price of Product B by $5:00. However, this would cause sales of Product
B to fall by 800 units and sales of Product C to increase by 700 units. Title the scenario name as Product
B Price Change.
The Second Scenario is to raise the price of Product C by $4:00. However, this would cause sales of
Product C to fall by 550 units and sales of Product B to increase by 400 units. Title the scenario name
as Product C Price Change.
– The Third Scenario is to raise the price of both Product B and Product C by $6:00. This would cause
sales for Products B and C to both decrease by 350 units each. Title the scenario name as Product B
and C Price Changes.
2. Create a Scenario summary report, which will become a new and separate worksheet. Make sure the Results
Cells include Earnings Before Taxes and Net Pro t. Rename this worksheet as Scenario Analysis, and move
the sheet to the right of the sheet of Goal Seek.
3. Write up a brief conclusion on your scenario analysis result in the sheet of Scenario Analysis, below the summary
report. Which scenario will bring the company the best net pro t from 2017 sales?
2.4 Solver Analysis
Go back to Income Statement sheet. It must be completed by now. Copy the cells A3:G23 to Solver Analysis sheet,
by pasting it at the cell A3.
1. At the blank Rows 1 & 2, merge some cells and add a title 2017 Income Statement Projections.
2. Change the label Unit Sold (at the cell C3) to Units to Sell.
3. Within the Solver Analysis worksheet, input the following Solver Parameters:
Objective cell: Set Net Pro t (C22) to the value of $80; 000.
Variables: Units to Sell (C4:C7).
Constraint 1: Product A must sell at least 6000 units.
Constraint 2: Total Operating Expenses is less than or equal $215; 000.
Constraint 3: All unit sold quantities (C4:C7) are integers.
4. Write up a brief conclusion on the major ndings from the solver analysis.
3
