1
Excel HW1 Instructions
MIS303 Fall 2022
Introduction and Objectives:
(Individual Assignment 35 points)
You have been hired as a consultant to a retail store. The total revenue for the store comes
from the sales of a mixture of 3 different products (Products A, B and C). You are provided with
historical sales data for three products. You must first make some conclusions regarding sales
for the year 2019. Then, you need to decide how much of each product to order the year 2020.
Lastly, you will make a recommendation on the appropriate mixture and pricing of products to
stock for the coming year.
Spreadsheets are one of the most commonly used software tools in corporations because they
are so easy to work with and so adaptable to a regular work task. This assignment is intended
to use a spreadsheet application such as Microsoft Excel as a reporting, data analysis and
decision support system in a retail operation. You are to follow the instructions to complete and
submit the assignment as individuals. No collaboration or co-working is allowed.
Tasks:
Simply put, you must do the following:
1. (2 pts) Download the file Excel Assignment.xlsx from the Blackboard assignment link.
Make sure you use right click on the link, save target / link as, so you can rename the
file. Name it with your FirstName_LastName_ HW1.xlsx. For instance, a student named
John Doe should have the Excel file saved as John_Doe_ HW1.xlsx for his assignment.
2. Follow the instructions below and complete the data reporting tasks in worksheet tabs A,
B and C. Leave worksheet tabs D, E, F to the next HW please. Do not alter any
information on it yet, or it will cause incorrect data to start your HW2.
3. Submit the Excel file back to the Blackboard Excel HW1 link.
Detailed instructions for each section / worksheet tab are provided below.
A. Sales Records (21 pts, 3 points per question)
1. Format the sales records dataset as a table. Make sure the table columns are wide
enough to fit the column contents.
2. Sort the sales records by Region first in a custom order of Midwest, East, West, South.
In the same Region group, sort the records then by ProdID in alphabetical order.
3. Add a new column at the right of ProdID, and name it Price. Use vLookUp function in
the Price column. For each sales record, use the ProdID as lookup value to find and
return the product price. The product prices are provided in the B. Summary worksheet.
You can use the data range of B8:E10 from the B. Summary worksheet as the table
array in making the vLookUp function.
4. Add a new column at the right of UnitsSold, and name it SubTotal. The subtotal of each
sales record should be the product of price and units sold (Price * UnitsSold).
5. Add a new column at the right of SubTotal, and name it Discount. This company
provides 8%of the subtotal amount as discount to all sales made in the West region. In
the other
2
regions, the company offers 10% of the subtotal amount as discount to Holiday sales in
December. The other sales records that are not in West and not in December receive $0
discount. The two discounts cannot be stacked – none of the order can take both West
region and the holiday discounts at the same time. Use a nested IF function for the
Discount column to show the correct discount for each sales record. (Hint: you can make
separate IF function for the two conditions, and then apply one into the other as value if
false.)
6. Add a new column at the right of Discount, and name it Discount Amount. This is equal to
Discount*SubTotal.
7. Add a new column at the right of Discount Amount, named OrderTotal. The order total of
each sales record will be the difference between subtotal and the discount amount
(SubTotal -Discount Amount).
B. Summary (8 pts)
1. Enter your name, last 3 digits of student ID, and section number in the cells provided in
this worksheet.
Your name: Your name here!
Last 3 digits of Student Id: xxx
Section: DL2
2. The total number of units sold in 2019 for the three products can be found on your
B_Summary worksheet (C8:C10). You need to fill out all the yellow or green boxed cells
according to the requirements below:
– Calculate estimated number of units to be sold for all three products in 2020 (in D8:D10)
as a percentage increase from the 2019 numbers. We will assume that the 2020 sales
will keep the same increase rate as that of 2019, which is 14% in as specified in Cell D3.
– Calculate the revenues (G8:G10) and costs of goods sold (H8:H10) for each products
based on 2020 data (D8:D10). Where Revenue is Units Sold * Unit Price and COGS is
Units Sold * Unit Cost.
– Total Revenue is the sum of the revenues for all three products for 2020
– Total COGS is the sum of the COGS for all three products for 2020.
– 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 = Salaries + Advertising + 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
C. Business Graph (4 pts)
In this worksheet, create a Pie Chart showing the percentage of each product’s contributing to
the total profitability (based on Gross Profit, not Revenue).
You need to calculate the forecasted individual profits (Revenue – COGS) generated by selling
product A, B, and C for 2020, based on the data in the B. Summary worksheet. In the cells with
3
Yellow background color, use formulas that refer to the B. Summary worksheet. DO NOT
SIMPLY ENTER THE VALUES.
Requirements for the chart:
– The Business Graph should have a meaningful Chart Title.
– It should display percentages for each products’ profit contributing to the total company
profit on each pie section.
– There should be a proper legend below the chart.
Submission
Leave worksheet tabs D, E, and F alone here please!!! Do not alter any data or information on
them yet. These are the sheets we will work on in HW2.
When you are done with worksheet tabs A, B and C. Save the Excel file again, and close it as
well as the Excel program on your computer. Go to the Blackboard, upload and submit the
completed file back to the Excel HW1 link.