attachment_2 (3).docx Code Composer/Arrange

Excel 1 Tutorial Assignment – 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.

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 (this represents current sales) and Prior Year 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 the
cells in this
worksheet,
MusicData.

• No additional data/formulas should be added to the worksheet.

➔ Part 1 Video: https://www.youtube.com/watch?v=-zw4Sdn70xQ

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 steps,
as shown below.

Step #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 add the current date formula. 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 or note (using the comment or note feature) to your title and add your name and your email address.

Step #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.

➔ Part 2, Steps 1-2 Video: https://www.youtube.com/watch?v=Np7Mtzynh60

Step #3 – Music Lookup Information
o Use the VLOOKUP function/formula and search the Music worksheet for the code that the user entered in Step 2.
o Display the information for the Code selected – use the same layout as in the example above.

o Note that in the video link below, cell C12 is displayed as currency, it should not be currency.
o Correctly use your named range (MusicData) and absolute cell referencing in your VLOOKUP formulas

Step #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? Look at the 2016 goal and the

YTD Units Sold. 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.

➔ Part 2, Steps 3-4 Video: https://www.youtube.com/watch?v=qN4ABFlXY3o

Step #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 2, Step 5 Video: https://www.youtube.com/watch?v=0CVGdWyFq5A

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 3 Video: https://www.youtube.com/watch?v=BlkSrPBzDDI

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 all publishers with the letter K in their code and any events with the word ‘Brass’ in the event name.

• Filter 2 – display Tuba music for grades higher than 3.

• Filter 3 – show some interesting analytics. Add a textbox to the worksheet to explain what you are showing.

➔ Part 4 Video: https://www.youtube.com/watch?v=p_6RvvyvPm0

Part 5 – Finishing Up

• **Use the IFERROR function and if a user enters a MUSIC Code that doesn’t exist, display ‘Code Not Found’ for the title and
blanks for the rest of the cells below. You can let the 15% display, if you’d like (cell C20 in my example).

• **Protect the LookUp worksheet so that the only change a user can make is to enter a different MUSIC 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 MUSIC
Code and be sure we can’t change any other cells in the worksheet.

• Your worksheets should be in the following order: LookUp, Music, your 3 pivots and then your 3 filter worksheets.
• Once a user enters a new MUSIC 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).

➔ Part 5 Video: https://www.youtube.com/watch?v=3Q7Wkq_sQPA

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 during office hours or online hours & I can either help you or make an
allowance in your grade. If you wait until the last weekend, I may not be able to do either.