Chat with us, powered by LiveChat 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 - Writingforyou

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.

A. Sales Records

ProdID Salesperson SaleMonth Region UnitsSold
A Charlene March East 6
A Hong March East 40
A Jae June East 14
A Jae October East 4
A Jason November East 20
A Jason August East 8
A Jason June East 6
A John September East 16
A John October East 2
A Peter February East 10
A Vivian July East 6
A Charlene March Midwest 28
A Jae April Midwest 18
A Jae April Midwest 18
A Jason June Midwest 6
A John December Midwest 6
A Julio January Midwest 14
A Julio September Midwest 18
A Peter April Midwest 30
A Peter May Midwest 12
A Peter January Midwest 16
A Peter October Midwest 6
A Samuel February Midwest 12
A Vivian June Midwest 14
A Vivian April Midwest 6
A Vivian August Midwest 6
A Charlene May South 8
A Charlene December South 6
A Damon March South 18
A Jae October South 4
A Jae December South 18
A Jason November South 4
A John June South 6
A Julio June South 34
A Julio November South 16
A Samuel January South 14
A Vivian August South 14
A Vivian April South 8
A Charlene January West 20
A Charlene March West 14
A Charlene June West 18
A Charlene August West 12
A Jae November West 8
A Jason March West 18
A Jason October West 14
A Jason February West 2
A Jason May West 18
A Julio November West 14
A Julio October West 2
A Julio January West 8
A Peter April West 12
A Samuel February West 30
A Vivian June West 14
A Vivian February West 14
A Hong October East 14
A Hong January East 8
A Jason April East 8
A Jason March East 14
A Julio March East 10
A Julio May East 8
A Peter December East 6
A Damon January Midwest 14
A Hong January Midwest 6
A Jae July Midwest 4
A Jason November Midwest 20
A Julio April Midwest 14
A Julio December Midwest 8
A Julio March Midwest 8
A Peter August Midwest 12
A Samuel November Midwest 6
A Vivian October Midwest 6
A Vivian February Midwest 16
A Charlene August South 6
A Charlene January South 10
A Damon August South 12
A Hong March South 18
A Hong June South 6
A Hong April South 6
A Jae April South 6
A Jae August South 10
A Jason April South 16
A John April South 16
A Julio September South 18
A Peter August South 12
A Vivian April South 16
A Charlene January West 14
A Charlene April West 6
A Charlene February West 6
A Damon September West 4
A Damon May West 16
A Damon September West 20
A Hong March West 14
A Jae September West 4
A Jae September West 6
A Jason June West 18
A Julio November West 8
A Julio September West 14
A Peter February West 12
A Samuel May West 18
A Vivian February West 18
A Charlene October East 8
A Damon December East 16
A Hong June East 6
A Jae November East 16
A Jae January East 10
A Jae July East 10
A Jason February East 10
A Jason August East 4
A John January East 12
A John May East 14
A John December East 16
A Peter May East 12
A Peter May East 12
A Samuel August East 12
A Samuel April East 6
A Vivian February East 4
A Charlene March Midwest 4
A Damon December Midwest 16
A Damon January Midwest 4
A Damon September Midwest 6
A Hong January Midwest 10
A Hong July Midwest 14
A Hong November Midwest 18
A Jason August Midwest 6
A John May Midwest 14
A John August Midwest 18
A John March Midwest 18
A Peter April Midwest 16
A Peter December Midwest 12
A Peter February Midwest 14
A Samuel May Midwest 2
A Vivian May Midwest<