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