MIS 303 Excel Assignment – Instructions

MIS 303 Fall 2017
Excel Assignment – Instructions (50 pts with 10 pts bonus)
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 Excel Assignment.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. (2 pts) 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.

4. (12 pts) On the sheet of 2016 Orders, complete the following tasks.
a. Format the given dataset as a table. Adjust column width as needed.
b. Sort the dataset by multiple fields. Sort all order records by Region first and in
each Region group, the order records should then be sorted by Product.
c. Add a column at the right of Product. Name it Price. Use vLookup function to pull
product prices from the sheet Income Statements (B4:D7) and show unit 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 on Total to all orders
from the West region, and the other regions only enjoy 10% discount in
December (Dec).
f. At the right of Discount, add a column called Totalw/Dis. It is calculated as Total
– Discount.
g. Use conditional formatting tool and highlight the orders with Unit sold above
average unit sold. Select the green fill and dark green text color option if the cells
meet this rule.
5. (4 pts) On the sheet of Income Statements, complete all the green-colored cells using
Excel functions or formulae.
• For each product, 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
• Salaries is 12% of Total Revenue
• Advertising is 4% of Total Revenue
• Miscellaneous expenses are 1% of Total Revenue
• Total Operating Expense is the sum of Salaries, Advertising, and Miscellaneous
• Earning Before Taxes = Gross Profit – Total Operating Expense
• Calculate Taxes based on tax rate of 25% of Earning Before Taxes
• Net Profit = Earnings Before Taxes – Taxes
6. (10 pts) On the sheet of Pivot Tables, create a pivot table of your choice. 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 A3 to place the pivot table separately.
Here are some suggested ideas for pivot tables.
• Product sales by Salespersons
• Seasonal sales by Regions, etc.
Requirements
– Add meaningful title for your pivot table
– Use proper grouping fields and summary fields.
– Use proper structure of the pivot table to ensure readability – preferably one grouping
field in rows and one field in columns, and no more than that
– Use proper type and format for summarized values (e.g., show currency sign and
proper decimal places, etc.)
– Add a short paragraph below or on the side of the pivot table and describe 3 major
findings from this table.
7. (8 pts) On the sheet of Business Charts, create two charts using the summary data
provided. Suggested ideas are:
– Salesperson performance
– Monthly sales
– Regional sales, etc.
Requirements
– Use proper chart type based on your data and purposes. Your two charts should use
two different chart types
– Add meaningful and unique chart titles
– Use proper labels and/or legend to ensure readability
– Add a short paragraph below the charts and describe your major findings from each
chart.
8. (5 pts) On the sheet of Goal Seek, find the way to achieve your 2017 profit goals.
a. Go back to Income Statement sheet. It must be completed by now. Copy the
gray area (A3:G23). Go to the sheet of Goal Seek, and paste it at the cell A3.
b. At the blank Rows 1 & 2, merge some cells and add a title 2017 Sales Estimates.
c. 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.
d. Name the cell J4 as IncreaseRate.
e. Edit Revenue of each product and include the increase on the product unit price.
Make sure you use formula with reference to IncreaseRate at J4.
f. Use the Goal Seek tool and see if we want to achieve a $125,000 net profit, what
is the increasing rate of price we should apply in 2017?
9. (9 pts) 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 a separate worksheet.
a. Name a few cells, including the cells holding Prod B Price, Prod B Unit, Prod C
Price, Prod C Unit, Earnings Before Taxes and Net Profit
b. You will create 3 different scenarios by changing the product pricing mix in order
to determine their impacts to Net Profit.
– 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
c. Create a Scenario summary report, which will become a new and separate
worksheet named Scenario Summary. Move this sheet to the right of the sheet
of Goal Seek. Make sure the Results Cells include Earnings Before Taxes and Net
Profit.
d. Which scenario will bring the company the optimum outcome of 2017 sales?
Insert a circle and circle the optimum scenario on the Scenario Summary sheet.
10. (Bonus 10 pts) Solver:
a. Go back to Goal Seek sheet. It must be completed by now. Copy the gray area
(A3:G23). Go to the sheet of Solver Analysis, and paste it at the cell A3.
b. At the blank Rows 1 & 2, merge some cells and add a title 2017 Income
Statement Projections
c. Change the label Unit Sold (at the cell C3) to Units to Sell.
d. Within the Solver Analysis worksheet, input the following Solver Parameters:
– Objective cell: Net Profit to the value of $80,000.
– By changing variable cells: unit sold of all products
– 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.
Submission:
Submit the completed Excel file to the Blackboard Excel HW submission link.

The post MIS 303 Excel Assignment – Instructions appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms