Description
please follow the instruction in the attached doucmment and complete the project.
Unformatted Attachment Preview
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
• Open a blank workbook (this must be started with a BLANK, NEW 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://youtu.be/HwbXhRIeHOI
Part 2 – Set up your 1st
worksheet
•
Name the tab for the
first worksheet,
LookUp. To the right 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://youtu.be/2L0fLYcTzLY
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 (cell references match my example). Don’t add any new
formulas to the Music worksheet.
o C16: ‘Gross Margin (Markup $)’ – profit per unit
o C17: ‘Markup %’ – profit per unit compared to the cost, displayed as a % with one decimal place
o C20: ‘% Increase for this year’: enter 15% into this cell
o C21: ‘This Year’s Goal in Units’ – displays a 15% increase (use C20 in your formula) from the ‘Prior Year Units
Sold’ (C19) . NOTE: You can’t sell partial units, so don’t display decimal places – use the INT function to round
down to the nearest integer. You can use Excel help or Google to see how the INT function works.
o C22: ‘Units Needed to Meet Goal’ . How many units need to be sold to meet this year’s goal? Look at this year’s
goal (C21) and the YTD Units Sold (C18) . Use an IF function/formula so that no negative numbers are displayed if
the goal has been met or exceeded.
o B23:C23, merged: Using another IF statement to 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://youtu.be/O_KuPhXGPG4
Step #5 – Graph/Chart
o Create the column chart displayed from the video and to the
right:
▪ 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://youtu.be/ZsX116SlauI
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://youtu.be/4308_XVOCYU
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 of 3 or higher.
• Filter 3 – show some interesting analytics. Add a textbox to the worksheet to explain what you are showing.
➔ Part 4 Video: https://youtu.be/OrK2FGK0ubE
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://youtu.be/QMrjPD4hPHA
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.
Purchase answer to see full
attachment
