MIS140-Management Information System

You are an area supervisor for MIS Hot Sauce Factory, a Hot Sauce company that has 4 areas (north, south, east & west). You are in charge of the North Area’s six stores. The owner of MIS Hot Sauce Factory has asked you to create a spreadsheet to keep track of sales. You may work with the partner you worked with on the Access Project. Here is our current product line: Hell's Rampage, Smoldering Garlic, Zippy Hot, Sweet Inferno, Zesty Blaze, Double Dare.

Requirements:

1)Start with “MIS 140 Data win20.xlsm.” (Do NOT use the file we used in lab) Save your file as a macro enabled workbook (LastNameFisrtName.xlsm)(Partner1Partner2.xlsm)

2)Your boss is a little picky about how the sales spreadsheet looks. Since you have to do this every week from the generic sales file, create a macro (name it “FormatSales20”) to format a worksheet with the following specifications (boldface: what that macro must do): 

a)Format all of the data  (Area, Store#, Sauce . . .) from each region as follows (use standard colors, not themed):

i)Text colors: North: orange, South: blue, East: green, West: red

b)Format all Sales text (data) for all stores to bold, Currency, 12pt 

c)Calculate a grand total for the company in cell C2 

i)Format it to Currency, 20pt, white font color, bold, fill cell with blue

ii)Stop recording the macro

3)(This is not part of the macro) In the column after the “Sales” column (column E), use VLOOKUP in a formula to insert the correct store manager name in each row on the “Sales 1-20” worksheet from “LookUp Info” worksheet. 

a)On the “LookUp Info” worksheet, change the Name of the store manager (storeMGR) for store 101 to your name (and 102 to your partner’s name).

b)Next, we want to create an easy way to enter a store number in a cell and get the store phone back. On the “Sales 1-20” worksheet, create a VLOOKUP to return the store phone (in cell F2) of a store when you enter in the store number (in cell F1) 

4)On the “Sales 1-20” worksheet,

a)Create a validation list of all the store numbers (no duplicates) in cell H2 

b)Create a validation list of all the Sauces (no duplicates) in cell I2 

c)Use SUMIFS in J2 to calculate the Total for all the Sales Data based on the Store and Sauce selected

5)On the “Sales 1-20” worksheet,

a)Create labels in column H for each cell listed below

i)H6: “Average”

ii)H7: “Min”

iii)H8: “Max”

b)Create formulas in column I to calculate the following for all the Sales Data 

i)I6: “Average”

ii)I7: “Min”

iii)I8: “Max”

6)Create (on a new worksheet) a pivot table that:

a)lists each of your stores and their sales for Sweet Inferno, Zesty Blaze and Double Dare only.

b)Includes a slicer based on Store#.

c)uses conditional formatting to highlight (change the default color) the top 15 store sales for each sauce (Sweet Inferno, Zesty Blaze and Double Dare). These stores will be getting a surprise visit for selling the most Sweet Inferno, Zesty Blaze and Double Dare. A store can be in the top 15 more than once for different sauces.

d)Label this worksheet “Pivot Table 2020”

7)Create (on a new worksheet) a pie chart that compares each area sales with the following specifications:

a)Use a Chart Title (change name to Your Name(s) Hot Sauce Company Etc…), Data labels, a picture (your choice)

b)Label this worksheet “Pivot Chart 2020”

8)We are giving ratings to each store manager: “A” “B” “C” “D” and “F” for each sauce based on the following criteria: 

“A”  (sales above $3,200) 

“B” (sales above $2,100) 

“C” (sales above $1,100) 

“D” (sales above $400) 

 “F” (sales equal to or under $400). 

a)On the “Sales 1-20” worksheet, create an “IF” statement (do not use VLOOKUP) that will generate the proper grade (A,B,C,D,F) based on each sauce sale for that store manager in Column F on the Sales worksheet. 

9)On a new sheet (Label this worksheet “Time Value of Money”)

a)Create a table that includes the following data and the calculation for the payment of a loan in the 4th column

i)Use columns labeled Rate, Nper (number of periods),PV (present value), PMT(payment)

a)Create a table that includes the following data and the calculation for the present value of a project with projected Future Cash Flows in the 5th column

i.Use columns labeled Rate, CF1 (future cash flow), CF2 (future cash flow), CF3 (future cash flow), NPV(net present value)

2)Turn in the file via D2L Submissions (emailed files will not be accepted)