MIS 140 Arakawa
Excel Project
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.xlsm.” (Do NOT use the file we used in lab, “MIS 140 Practice.xlsm.”) 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 “FormatSales”) 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) North: purple, South: orange, East: green, West: blue
b) Format all Sales text (data) for all stores to underlined, Currency, 12pt
c) Calculate a grand total for the company in cell B2
i) Format it to Currency, 16pt, white font color, bold, fill cell with green
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-15” 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) We want to create an easy way to enter a store number in a cell and get the store phone number back. On the “Sales 1-15” worksheet, create a VLOOKUP to return the store phone number (in cell F2) of a store when you enter in the store number (in cell F1)
c) On the “Sales 1-15” worksheet, create labels in column G for each cell
i) G1: “Enter store number”
ii) G2: “Store phone number”
4) On the “Sales 1-15” worksheet,
a) Create labels in column G for each cell listed below
i) G6: “Average”
ii) G7: “Min”
iii) G8: “Max”
b) Create formulas in column H to calculate the following for all of the Sales Data
i) H6: “Average”
ii) H7: “Min”
iii) H8: “Max”
5) 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 6 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 6 more than once.
d) Label this worksheet “Pivot Table”
6) 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 something like Your Name(s) Hot Sauce Company Etc…), Data labels, a picture (your choice)
b) Label this worksheet “Pivot Chart”
7) 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,000)
• “B” (sales above $2,000)
• “C” (sales above $1,000)
• “D” (sales above $600)
• “F” (sales equal to or under $600).
a) On the “Sales 1-15” worksheet, create an “IF” statement that will generate the proper grade (A,B,C,D,F) based on specific sauce sales for that store manager in Column F on the Sales worksheet.
8) 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
i) Use columns labeled Rate, Nper (number of periods),PV (present value), PMT(payment)
Rate (format %) Nper PV (format currency)
15% 12 $10,000
13% 24 $10,000
10% 48 $10,000
a) Create a table that includes the following data and the calculation for the present value of a project with projected Future Cash Flows
i) Use columns labeled Rate, CF1 (future cash flow), CF2 (future cash flow), CF3 (future cash flow), NPV(net present value)
Rate CF1 CF2 CF3
11% $10,000 $12,000 $14,000
12% $13,000 $13,000 $13,000
13% $12,000 $17,000 $17,000
2) Turn in the file via D2L Dropbox (emailed files will not be accepted)