Management Information Systems Homework 2 on Excel Lesson 1

Learning Objectives:

Frequency Distribution for Categorical Data using COUNTIF

Functions:

o SUMIF

o SUMIFS

Conditional formatting

IF Function

VLOOKUP

The worksheet Sales Data in the Excel File IS300 Fall 2021 Excel Homework 2 on Excel Lesson 1 moon Juice Adaptogens -this is the Student file add your name.xlsx provides information about sales for a supplement companynamed moon Juice. You will use the spreadsheet provided to perform necessary calculations. The following needs to be accomplished. Turn in your excel file into the drop box for Homework 2 on Beachboard. Add your name to the Excel file.

1. Provide the Item retail Price. Retrieve the Price from the Table provided.

2. Provide the Item discount. Retrieve the Item discount from the Table provided.

3. Provide the Discount Category. Retrieve the Category from the Table provided.

4. Calculate the retail revenue. For example, if an item is priced at $10.00 with a discount of .10, and the actual quantity sold is 100 units then your retail value would be: $ 900.00. Calculate this for all the records.

5. Determine the Sales Expectations. To determine this value, compare the actual units sold to the forecast of unit sales. If the Forecast of Units sales is greater than the Actual Units sold, then return the value of Below Target. If the actual is the greater or equal to the forecast of Unit sales leave it blank.

6. Use the IF function =IF to determine the Unit volume LOW, MEDIUM, or High. Check the Actual Units sold:

a. if the value is less than or equal to 100 then return the value of Low

b. if the value is between 101 and 299 then return the value of Medium

c. if the value is 300 or greater then return the value of High

7. Calculate Total Revenue. This is done by adding up all the values in the Retail Revenue column.

8. Frequency Distribution for Categorical Data Use the COUNTIF Function to determine how many of the Sales Expectations were Below Target.

9. Frequency Distribution for Categorical Data Use the COUNTIF Function to determine how many of the Sales Expectations were on target of above.

10. Use the SUMIFS Function to determine the total amount of Actual Units sold by Nordstrom for the product: Moon dust. (Note: Do not use SUMIF, use the SUMIFS)

Sales Channel		Product	Unit Sold
Nordstrom		Moon Dust	Calculate

11. Use the SUMIF function to determine the total of actual units sold by Nordstrom for all products.

Sales Channel		Product	Total Units Sold-All Products
Nordstrom		Total Quantity (AllProducts)	Calculate

12. Use the SUMIF Function to Calculate the Total Retail Revenue for Nordstrom.

Sales Channel		Product	Total Revenue - all Products
Nordstrom		Total Retail Revenue	Calculate

13. Add Conditional formatting. If the Sales Channel contains the text Nordstrom highlight with Yellow Fill with Dark Yellow Text.

14. Add Conditional formatting. If the Sales Expectations are Below Target highlight in Light Red fill with Dark Red Text.

February 2022 Moon Juice Fall Sale - Products Sold for Online and Retailers Item Retail Price Item Discount Retail Discount Category Revenue Unit Volume Sales Low, Medium Expectations or high 0.1 0.15 Forecast Actual of Units Units Sold LSales Product 150 135 Super You 450 444 SuperYou 240 300 Super You 525 425 Moon Dust 159 133 Moon Dust 105 110 Moon Dust 660 660 Ashwagandha 230 220 Ashwagandha 210 200 Brain Dust 75 100 Cordyceps 300 275 Cordyceps 125 120 Cordyceps 225 220 Cosmic Moon 50 60 Magnesi-OM 90 88 Magnesi-OM Product Super You Moon Dust Ashwagandha Brain Dust Cordyceps Cosmic Moon Magnesi-OM Price Discount $48.00 $50.00 $40.00 $44.00 $38.00 $35.00 $55.00 Sales Channel Nordstrom Online Store Sephora Online Store Nordstrom Sephora Sephora Online Store Online Store Nordstrom Online Store sephora Online Store Sephora Nordstrom 0.1 0.12 0.08 0.05 0.1 Discount Category O No discount 0.02 Promotion 0.08 Seasonal 0.15 Clearance Total Revenue Count of Sales Expections Count of Sales Expections Below Target On Target and Above Product Unit Sold Sales Channel Nordstrom Moon Dust Total Units Sold-All Products Sales Channel Nordstrom Product Total Quantity (All Products) Total Revenue - all Products Sales Channel Nordstrom Product Total Retail Revenue February 2022 Moon Juice Fall Sale - Products Sold for Online and Retailers Item Retail Price Item Discount Retail Discount Category Revenue Unit Volume Sales Low, Medium Expectations or high 0.1 0.15 Forecast Actual of Units Units Sold LSales Product 150 135 Super You 450 444 SuperYou 240 300 Super You 525 425 Moon Dust 159 133 Moon Dust 105 110 Moon Dust 660 660 Ashwagandha 230 220 Ashwagandha 210 200 Brain Dust 75 100 Cordyceps 300 275 Cordyceps 125 120 Cordyceps 225 220 Cosmic Moon 50 60 Magnesi-OM 90 88 Magnesi-OM Product Super You Moon Dust Ashwagandha Brain Dust Cordyceps Cosmic Moon Magnesi-OM Price Discount $48.00 $50.00 $40.00 $44.00 $38.00 $35.00 $55.00 Sales Channel Nordstrom Online Store Sephora Online Store Nordstrom Sephora Sephora Online Store Online Store Nordstrom Online Store sephora Online Store Sephora Nordstrom 0.1 0.12 0.08 0.05 0.1 Discount Category O No discount 0.02 Promotion 0.08 Seasonal 0.15 Clearance Total Revenue Count of Sales Expections Count of Sales Expections Below Target On Target and Above Product Unit Sold Sales Channel Nordstrom Moon Dust Total Units Sold-All Products Sales Channel Nordstrom Product Total Quantity (All Products) Total Revenue - all Products Sales Channel Nordstrom Product Total Retail Revenue