Excel 2 Assignment – ISM3011

Page 1 of 3
Excel 2 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 your optional text and/or review the overview and video tips on Canvas.

Part 1 Create / Download

Create a blank workbook. Name it using your Last name followed by your initials and then and underscore and 2EX. For
Example: WarnerBL_2EX.xlsx.

Copy/paste the data from Ex2 Data S16.docx , into the 2nd worksheet in your workbook.
Name the tab DVDs.

Adjust the column widths so the DVD data is not wrapped onto 2 lines or cut off.

Then adjust (wrap) the column headings so that each word of the heading is on a different line
within the cell. This wrapping should stay the same, even if the column width is increased.

Sort the data (do not sort or remove the title/heading rows) by DVD Code. Check the sort to

be sure all is correct. Your first DVD Code after the sort should be BQ101 with a YearEnd Sales Goal of 420.

Select all of the cells on that worksheet and name that range of data. Call it AllDVDs.

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

Part 2 Set up your Lookup worksheet

Name the tab for the first worksheet, LookUp. Below is a sample of how I set up my worksheet See the descriptions of the
components below for the details. Use your own color scheme for your project but include borders and backgrounds and
include all of the components.

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/dark blue) & 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).

o Add a comment or note feature (using Excel’s feature) to your title and in the comment insert the date the worksheet
was created and your email address.

Page 2 of 3
Component #2 Input Area

o Add an area to enter a DVD Code (Cells B5:C5 in my example). 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/dark blue. Use an arrow ‘shape’ from the shape /
illustration feature of Excel.

Component #3 Student Lookup Information

o Type in the labels (column B in my example)

o Then, use the VLOOKUP function/formula and search the DVDs worksheet for the DVD Code that the user entered
in Component 2.

o Display all information for the DVD Code selected use the same layout as in the example above. You’ll use
VLOOKUP formulas for all of the data except the calculations (see component 4 for these fields).

o Correctly use relative and absolute cell references and your named range (AllDVDs) in your VLOOKUP
formulas.

o Note: Be sure you have a valid code entered in the input area (component 2) when you start creating your
VLOOKUP formulas, so your formula can find data from the DVDs worksheet.

Component #4 Calculations

o Calculate and display the following in the Lookup worksheet. Don’t add any new formulas to the DVDs worksheet.

o Markup (Dollars)

o Markup (Percent)

o % of Sales Goal Met

o DVDs to be Sold (how many units need to be sold to reach the sales goal). Use an If function/formula and if the goal
has been met, display a zero.

o Inventory to be Ordered (how many units need to be ordered so the sales goal can be met). Use an IF
function/formula and if no additional inventory is needed, display a zero. **Hint: first calculate this without
worrying about values <0, once you have this formula, add it to an IF function.

o Inventory Order Message: Use an IF function/formula and if additional inventory is needed, display a message in a
bright color (see TIPS area on Bb for an example). If no additional inventory is needed do not display anything (not
a zero, not another message, etc.) **Hint: first calculate this without worrying about values <0 or no inventory
needed, once you have this formula, add it to an IF function.

o Again, display the Lookup information & calculations in the same order as the example above.

Component #5 Graph/Chart

o Create a column chart similar to the one 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 above each column

The title should include the DVD title and should change each time new DVD is displayed (experiment with the
data series name feature in the chart wizard). 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.

Use a 2color gradient to format the columns, a colored background on the chart, colored fonts on all numbers /
and colored axes and gridlines. Label both axes. Do not use dark blue/black for these colors.

Part 3 Pivot worksheets

Using the data in the DVDs worksheet, create 2 pivot worksheets

o The first should be a pivot table showing each Promotion Code / Color and the total current inventory. Format the table
so that your numbers have commas and no decimal places. Add a title and format it so it looks nice. Name the tab Pivot
1.

o The second should be a pivot chart showing each Promotion code and the sales to date as well as the yearend sales goal.
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 DVDs spreadsheet data into each one of the filter worksheets.