Reference no: EM133585846

Assignment

Instructions

Introduction and Objectives:

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.

Task:

Simply put, you must do the following:

1. 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.

I. Sales Records

A. Format the sales records dataset as a table. Make sure the table columns are wide enough to fit the column contents.

B. 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.

C. 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.

D. 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).

E. 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 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.)

F. Add a new column at the right of Discount, and name it Discount Amount. This is equal to Discount*SubTotal.

G. 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).

II. Summary

A. Enter your name, last 3 digits of student ID, and section number in the cells provided in this worksheet.

B. 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:

1. 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.

2. 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.

3. Total Revenue is the sum of the revenues for all three products for 2020

4. Total COGS is the sum of the COGS for all three products for 2020.

5. Gross Profit = Total Revenue - Total COGS

6. Salaries is 12% of Total Revenue

7. Advertising is 4% of Total Revenue

8. Miscellaneous expenses are 1% of Total Revenue

9. Total Operating Expense = Salaries + Advertising + Miscellaneous

10. Earning Before Taxes = Gross Profit - Total Operating Expense

11. Calculate Taxes based on tax rate of 25% of Earning Before Taxes

12. Net Profit = Earnings Before Taxes - Taxes

III. Business Graph

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 Yellow background color, use formulas that refer to the B. Summary worksheet.