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

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.