I don’t understand this Excel question and need help to study.
Part 1 – Create / Download • Create a blank workbook. Name it using your Last name followed by your initials and _ 1EX (underscore then 1EX). For Example: WarnerBL_1EX .xlsx or xls. Either extension is fine • Copy/paste the data from Excel 1- Music Data.docx , into the 2nd worksheet in your workbook. Name the tab MUSIC. • Adjust the YTD Units Sold and 2015 Units Sold columns so that their titles are wrapped onto 2 or 3 lines within one cell. Adjust the Title column so that titles can be wrapped on 2 or more lines within one cell as needed (so titles are not cut-off). See the sorted example below. • Sort the data (do not sort or remove the title/heading rows) by Code. Check the sort to be sure all is correct. • Add conditional formatting to this Music worksheet that highlights any selling prices of $9 or more with a green background. If the selling prices is lowered below $9, the formatting should change automatically. • Using the named range feature of Excel, name all of the cells in this worksheet, MusicData.
Part 2 – Set up your 1st worksheet • Name the tab for the first worksheet, LookUp. Below is a sample of how I set up my worksheet. 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 o Include a title with your name and any other information you think is appropriate. Merge and center it across all columns with data. o Below the title put the current date. Be sure you use the appropriate formula so that whenever your worksheet is opened, the current date is displayed (will change as the date changes). Again use the merge and center feature. o 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). o Add a comment (using Excel’s comment feature) to your title and in the comment insert the date the worksheet was created and your email address.
Component #2 – Input Area o Add an area to enter a music 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 worksheet. o Include an arrow in this section; make it a color other than black. Use the SHAPE feature in Excel to create the arrow.
Component #3 – Music Lookup Information o Use the VLOOKUP function/formula and search the Music worksheet for the code that the user entered in Component 2. o Display the information for the Code selected – use the same layout as in the example above. o Correctly use your named range (MusicData) and absolute cell referencing in your VLOOKUP formulas
Component #4 – Calculations o Calculate and display the following in the LookUp worksheet. Don’t add any new formulas to the Music worksheet. o Gross Margin (Markup $) o Markup Percent based on the cost o 2016 Goal %: enter 15% into this cell. The goal is a 15% increase from 2015 sales on all music. o 2016 Goal in Units: calculate the new goal (15% increase from 2015 sales). NOTE: You can’t sell partial units, so don’t display decimal places – instead use the INT function to round down to the nearest integer. o Units to meet 2016 goal. How many units need to be sold to meet the 2016 goal? Use an IF function/formula so that no negative numbers are displayed (if they have sold more than their goal). o Using another IF statement, display a message if the sales goal has been met. Use a bright colored font for this message. If the sales goal has not been met, do not display anything. o Display the lookup information & calculations in the same order as the example above.
Component #5 – Graph/Chart o Create the column chart displayed above: ▪ Select only the data needed for the chart (don’t select all data & delete items from the chart). Do not display any additional fields. ▪ Display the data values for each column ▪ The title should include the music title and should change each time new information is displayed. It should also be a larger font (greater than 12) and be a color other than blue or black. ▪ Place the chart on your LookUp worksheet. ▪ Format your chart & include: ▪ a 2-color gradient to format the columns ▪ a colored background on the chart ▪ colored fonts ▪ Do not use dark blue/black for these colors.
Part 3 – Pivot worksheets • Using the data in the Music worksheet, create 2 pivot worksheets o The first should be a pivot table showing each Event Name and the average Selling Price. Format the table so that your numbers have a dollar sign, two decimal places. Add a title and format it so it looks nice. Name the tab Pivot 1. o The second should be a pivot table and chart showing each grade level code and the units sold for both years. The numbers should have commas and no decimal places and include data labels. Add a title and format it so it looks nice. Name this 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. Include a title and nice formatting. Name this tab Pivot 3.
Part 4 – Filtering • Create 3 worksheets and name their tabs Filter1, Filter2 and Filter 3. • Copy the Music worksheet data into each one of the filter worksheets. • Filter 1 – display publisher ‘CFP’ records that are for any kind of brass event. • Filter 2 – display grade 4 and 5 events for Tubas. • Filter 3 – show some interesting analytics. Add a textbox to the worksheet to explain what you are showing.
Part 5 – Finishing Up • 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, percents should be formatted with a % sign and 1 decimal place. • **Experiment with the IFERROR function and if a user enters a DVD Code that doesn’t exist – display some appropriate messages instead of the #N/A. See the overview or Excel 1 sample for an example of this. • If any information is changed in DVDs worksheet, the changes should be automatically reflected in the other workbooks (you may have to ‘refresh’ your pivot sheets for changes to appear). • Once a user enters a new DVD Code in the LookUp worksheet, all of the data and chart should automatically change. • Your worksheets should be in the following order: LookUp, DVDs, your 3 pivots and then your 3 filter worksheets. • **Protect the LookUp worksheet so that the only change a user can make is to enter a different DVD Code. 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 DVD Code and be sure we can’t change any other cells in the worksheet.
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 Excel is 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.
