excel project and need the explanation and answer to help me learn.
ISM Excel project 2.
Be sure you are uploading the file that ends with .xlsx or .xls.
Requirements:
Excel Assignment #2 – ISM3011 Ask before/after/during class or come into office/online hours if you have questions on any of this. Refer to the syllabus on Academic Dishonesty and group/individual work and allowable help for all projects – also remember it’s your responsibility to protect your work. Before you start — read this whole assignment and use an optional text and/or review the tutorials as necessary. A project overview is also available on Canvas. Part 1 – Create / Download / Video Tip: https://youtu.be/w5-d6jc_EM4 • Required: o Be sure you have downloaded and installed Excel from portal.office.com/home (top right ‘Install Apps’) o Then 1) open Excel and 2) open a blank document (in this order). This will ensure that the unique internal file information is kept. If this isn’t done, your project won’t be graded or if graded, will lose points. • Name your workbook using your Last name followed by your initial(s) and then underscore 2EX. For example: WarnerBL_2EX.xlsx. • Copy/paste the data from Excel2_Data_F23.docx , into the 2nd spreadsheet in your workbook. Name the tab Items. • Adjust the formatting and set the column widths so that any column headings with more than 1 word have each word wrapped on a separate line within the one cell (see below). These should stay wrapped even if the column width is increased. • Sort the data (do not sort or remove the title/heading rows) by Item Code in ascending order. Check the sort to be sure all is correct. • Add conditional formatting to this Item worksheet that highlights any sales in the 2022 Q1 through Q4 with sales greater than 600. Use an attractive fill color. If the sales number is then changed to a number less or equal to 600, the formatting should change automatically. The column header should not change color. • Using the named range feature of Excel, name all of the cells in this worksheet, ItemData. No additional data/formulas should be added to the spreadsheet. Part 2 – Set up your Look Up spreadsheet / Video Tip: https://youtu.be/4P1U0Vzce88 • Name the tab for the first spreadsheet, LookUp. Below is a sample of how I set up my spreadsheet. Use your own color scheme for your project – but include borders and backgrounds and include all of the components, as shown 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. Add a colored border to the title row(s), not black/dark blue, and be sure the border is visible on all 4 sides (you can add 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/dark blue). • Add a note / comment (using Excel’s feature) to your title and in the comment insert your name and email address. Component #2 – Input Area • Add an area to enter an Item Code. 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/dark blue and use Excel’s SHAPE feature (don’t make an arrow from the keyboard). Component #3 –Look up Information • Use the VLOOKUP function/formula and search the Item spreadsheet for the Item Code that the user entered in Component 2. • Display information from the Item sheet for the Item Code selected – use the same layout as in the example above. • Correctly use your named range (ItemData) and absolute cell referencing in your VLOOKUP formulas Component #4 – Calculations • Some fields require formulas /calculations. Calculate and display the following in the LookUp spreadsheet. Don’t add any new formulas to the Item spreadsheet. o Profit (per unit) o Markup % (per unit) – how much each unit is marked up based on the cost o Total for 2022 Sales (cell below the chart area) o Needed to Meet Sales Goal – Use an IF function to be sure that a negative number is not displayed if the goal has been met. o Sales Goal Met message – use an IF statement to display a message if the Sales Goal has been met. o Add a spark line cell to show the quarterly trend for the past year’s sales (cell i19 in my example above) o Display the Look up information & calculations in the same order as the example above. If you’re unsure of which cells should be VLOOKUPs and which should be formulas / calculations you create, see the overview video. Component #5 – Graph/Chart • Create the column chart displayed above: o Select only the data needed for the chart. Do not display any additional fields. o Display the data values on the chart for each column (above the column or inside the column) o The title should include the Item Name and should change each time new Item information is displayed. It should also be a larger font (greater than 12) and be a color other than blue or black. o Place the chart on your LookUp spreadsheet. o Use a 2-color (or more) gradient to format the columns. If this option isn’t available, you may have to change the shape of your columns to a shape that supports gradients. o Include colored fonts. Do not use dark blue/black for these colors. Component #6 – Protect Your Worksheet / Use IFERROR • Use the IFERROR function and if a user enters an Item Code that doesn’t exist, display ‘Item Code Not In Data’ for the Item Name and blanks for the rest of the below. You may have to be creative with some cells that don’t easily disappear with the IFERROR function (think about IF functions in general for those). • Protect the LookUp spreadsheet so that the only change a user can make is to enter a different Item Code. They shouldn’t be able to change any other cells in the spreadsheet. Don’t use a password, just leave that blank. Test it when you’re done to be sure we can open the
spreadsheet and enter a new Item Code and be sure we can’t change any other cells in the spreadsheet Part 3 – 3 Pivot worksheets / Video Tip: https://youtu.be/J68ec-6b3PU • Using the data in the Item worksheet, create 2 pivot worksheets o The first should display the pivot table shown (right). Add a title and format it so it looks nice. Name the worksheet tab Pivot 1. Sort by Department name. o The second should display the pivot table and chart seen below. The Sales numbers should have commas and no decimal places. Add a title and format it so it looks nice. Name the worksheet tab Pivot 2. o Add one more Pivot worksheet that shows some interesting analytics. Add a textbox to the worksheet to explain what you are showing. It should be something that can’t be seen by looking at the data alone – it should be some kind of analysis. Include a title and nice formatting. Name this tab Pivot 3. Part 4 – Filtering / Video Tip: https://youtu.be/ZfUJiVuOIcI • Create 3 worksheets and name their tabs Filter 1, Filter 2 and Filter 3. • Copy the Item worksheet data into each of the filter worksheets. Modify the column headers as needed so they are left aligned. • For Filter 1, show all of the items in Skin Care and Cleansers that have a Cost between $15 and $20 (‘between’ is one of your filtering options – use this ‘Number Filter’) . This should work even if additional data is added to the data set. • For Filter 2, show all of the items in Quarter 4 that are already highlighted for large sales. To do this, use the filter by color option. • For Filter 3, show some interesting analytics. Add a textbox to the worksheet to explain what you are showing. Show something that can’t be easily seen in the full data set (unfiltered). • Notes: 1) If you select a whole column and set / use the status bar at the bottom of your worksheet, the status bar can display the average, max, min, and sum of that column (nifty?). 2) Shortcut: Ctrl+Shift+L turns off and on Filtering. Part 5 – Finishing Up / Formatting / Checking / Video Tip: https://youtu.be/wqjwog7CiVg • Your worksheets should be in the following order: LookUp, Items, your 2 pivots and then your 3 filter worksheets. • Once a user enters a new Item Code in the LookUp worksheet, all of the data and chart should automatically change. • 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. • Check your formatting – currency should have a $ and 2 decimal places, percentages should be formatted with a % sign and 1 decimal place. • Check your formulas, be sure they are correct and make sense. For example, if you are subtracting 2 numbers don’t use the SUM formulas (sum is for adding). Excel may figure out what you mean, but we want the formulas to be used correctly (show that you understand how to use them). Project Submission Instructions / Notes:
• Office/online hours get busy as deadlines approach. If you procrastinate and wait until the last days to work on your project, you may not be able to get all the help you want. • The only way we can fairly grade the projects is if we check for each requirement. Please go through the instructions before you submit & be sure you have done each one correctly so you don’t miss out on points. Compare your solution to the project overview. • Submitting: o Remember to leave all of the internal file properties intact for your project, if they are modified or deleted, you project won’t be accepted (see syllabus for more on this). o Read and follow the instructions in the Assignments section of Canvas on uploading and checking your upload. If you follow these instructions you can ensure that your project is uploaded correctly (and is the correct project). Be sure that Access / Excel are closed before you try to upload your project files. o If your project doesn’t upload correctly before the due date, it will be considered late and be assessed the late penalty – even it was finished on time. This is the only way we can ensure that students check their Canvas submissions. • Technology problems relating to your home computer (Windows based or Mac), internet connection or slow Canvas access are not valid excuses for late/missing work, unless Canvas is down for 6+ hours on the due date. Computers at USF computer labs and the library are available; leave enough time to access them as needed. Also give yourself enough time that if a TA can’t answer a question, you’ll have time to contact me & I can either help you or make an allowance in your grade. If you wait until the last days, I may not be able to do either.