14 May MIS 140 ARAKAWA
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 Areas 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: Hells 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 partners 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)
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.