ISM3011 USF Information Systems in Organizations
Question Description
Required files and instructions attached. Check final results against this video: https://www.youtube.com/watch?v=2i2-mBWF6rI&feature=youtu.be
Part 1 – Create / Download
Create a blank workbook.Name it using your Last name followed by your initials and _ 2EX (underscore then 2EX). For Example: WarnerBL_2EX .
Copy/paste the data from Ex2-Bicycles.docx , into the 2nd worksheet in your workbook.Name the tab Bicycles.
Adjust the column widths so that none of the data is wrapped onto 2 lines.
Sort the data (do not sort or remove the title/heading rows) by Bike ID.Check the sort to be sure all is correct.No additional data/formulas should be added to the Bicycles worksheet.
Using the data in the Bicycles worksheet, create 3 pivot worksheets
Create 3 worksheets and name their tabs Filter1, Filter2 and Filter 3.
Copy the Bicycle worksheet data into each one of the filter worksheets.
Filter 1 – display bicycles in the “˜children’ category with selling prices greater than $100.00
Filter 2 – display bicycles with names that start with “˜classic’ and “˜tea’.Add a textbox that explains how the status bar can be used to find the average cost and average selling prices of the displayed bicycles.Note the averages for these 2 fields for the bicycles displayed.
Filter 3 – show some interesting analytics.Add a textbox to the worksheet to explain what you are showing.
**Use the IFERROR function and if a user enters a Bike ID that doesn’t exist, display “˜Code Not Found’ for the title and blanks for the rest of the cells below,
**Protect the LookUp worksheet so that the only change a user can make is to enter a different Bike ID. They shouldn’t be able to change any other cells in the worksheet.Don’t use a password, just leave that blank.Don’t guess how to do this, if you don’t know – watch the Tips on it.Test it when you’re done to be sure we can open the worksheet and enter a new Bike ID and be sure we can’t change any other cells in the worksheet.
Your worksheets should be in the following order: LookUp, Bicycles, your 3 pivots and then your 3 filter worksheets.Delete any other (blank) worksheets.
Once a user enters a new Bike ID in the LookUp worksheet, all of the data and chart should automatically change.If any information is changed in Bicycle worksheet, the changes should be automatically reflected in Look Up worksheet.
Check your worksheet and be sure there are no errors or error symbols in your finished worksheet.If you don’t have this feature come into the lab to do this step.
Part 2 – Set up your 1st worksheet
Name the tab for the first worksheet, Look Up.Below is a sample of how I set up my worksheet.Use your own color scheme for your project – but include borders and shading as shown – be sure to include all of the components, as described below.
Component #1 – Title
Include a title with your name and any other information you think is appropriate.Merge and center it across all columns with data.
Below the title put the current date.Be sure you use the appropriate formula so that whenever your spreadsheet is opened, the current date is displayed (will change as the date changes).Again use the merge and center feature.
Add a colored border to the title and date rows (not black/ blue) & be sure the border is visible on all 4 sides (put a blank row above the title and a blank column to the left of the title so the whole border can be seen).Include a background color and font color (besides black/ blue).
Add an area to enter a Bike ID.Try to make it obvious to the user that this is the data entry area.Use placement, borders, and/or background colors to distinguish it from the rest of the spreadsheet.
Include an arrow in this section; make it a color other than black.Use the SHAPE feature in Excel to create the arrow
Use the VLOOKUP function/formula and search the Bicycle worksheet for the Bike ID that the user entered in Component 2.
Display all information for the Bike ID selected – use the same layout as in the example above.
Understand how relative and absolute cell references work and use them correctly in your VLOOKUP formulas(there is a TIP demonstrating this).
**Be sure you have a valid code in the Bike ID input area before creating your VLOOKUP formulas – or your formulas will display error messages.
Calculate the Markup % on the bike displayed (how much the price has been marked up from the cost – in % form).
Calculate and display the YTD (Year to Date) Bikes Sold (all four regions combined) – you may want to do this formula after you display the sales for each region (below).
Calculate and display the % of Sales Goal already met.Use conditional formatting on this cell so that if the % of Sales Goal Met is greater than 100%, a bright color font is used.
Using an IF statement, calculate and display the number of bicycles that need to be sold to meet the sales goal.Display a 0 if the sale