Create a blank workbook. Name it using your Last name followed by your initials and _ 2EX (underscore then 2EX). ForExample: WarnerBL_2EX.xlsx or xls. Either extension is fine
•Copy/paste the data from Excel 2-Books Data.docx , into the 2nd worksheet in your workbook. Name the tab Books.• Adjust the Book Code, Sales Goal, Units Sold, In Stock and Selling Price so that the column titles are wrapped onto 2 lines withinone cell. Be sure that if you make the columns wider, the titles stay wrapped.
• Adjust the (book) Title column so that titles can be wrapped on 2 or more lines within one cell as needed (so titles are not cutoff).• Sort the data (do not sort or remove the title/heading rows) by Book Code. Check the sort to be sure all is correct.• Add conditional formatting to this Book worksheet that highlights any selling prices of $13 or more with a yellow background. Ifthe selling prices are lowered below $13, the formatting should change automatically.
• Using the named range feature of Excel, name all of the cells in this worksheet, AllBooks.
• No additional data/formulas should be added to the worksheet.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 colorscheme for your project – but include borders and backgrounds and include all of the components, as shown below.Component #1 – TitleInclude a title with your name and any other information you think is appropriate. Merge and center it across allcolumns with data.Below that add the current date formula, so that each time the file is opened, the current date is displayed. Merge andcenter this as well.Add a colored border to the title rows (not black/ dark blue) & be sure the border is visible on all 4 sides (put a blankrow above the title and a blank column to the left of the title so the whole border can be seen). Include a backgroundcolor and font color (besides black/ dark blue).Add a comment (using Excel’s comment feature) to your title and in the comment, insert the date the worksheet wascreated and your name.Component #2 – Input AreaAdd an area to enter a book 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.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 – Book Lookup Informationo Use the VLOOKUP function/formula and search the Book worksheet for the code that the user entered in Component2.o Display the information for the Book Code selected – use the same layout as in the example above.o Correctly use your named range (AllBooks) and absolute cell referencing in your VLOOKUP formulasComponent #4 – CalculationsCalculate and display the following in the LookUp worksheet. Don’t add any new formulas to the Book worksheet.
–ª Calculate and display the % of Sales Goal met.
–ª Using an IF statement, calculate the number of Books to Reorder. Consider the Books in stock, Sales Goal andamount sold to date. Display the number of Books to reorder if more units are needed to reach the Sales Goal.Display a 0 if additional books do not need to be ordered.
–ª Using another IF statement, display a message if it’s necessary to reorder. Use a bright colored font for thismessage. If there are enough units on hand to meet the sales goal, do not display any message. Note: Notdisplaying any message is the brain-teaser part of the project (and not worth many points). The idea is to playaround with the IF statement to get the desired results. You may have to think a little bit outside the box, but thereare a few ways to accomplish this.
–ª Display the Lookup information & calculations in the same order as the example above.Component #5 – Graph/Charto Create the column chart displayed in the previous screenshot.
–ª Select only the data needed for the chart (don’t select all data & delete items from the chart). Do not display anyadditional fields.
–ª Display the data values for each column
–ª The chart title should include the book title and should change each time new information is displayed. It shouldalso be a larger font (greater than 12) and be a color other than dark blue or black.
–ª Place the chart on your LookUp worksheet.
–ª Format your chart & include:–ª a gradient (2 or more colors) 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
• Create two pivot tables and one pivot table with a chart from the All Book Data. Each should be on a separate worksheet (3worksheets total).1. Create a pivot table to show Total Sales (Units sold) by Region.
• Name the worksheet tab SalesByRegion. Total Sales (Quantity) should be formatted with a comma (for anynumbers greater than 999) and no decimal places.2. Create a pivot table and chart to show Sales Goal by Region.
• The table and chart should both be on a worksheet named SalesGoalByRegion. Place the chart next to thepivot table.
• Format chart with a background color and a gradient on the columns.
• Include data labels and a descriptive title (not the default title).
• Adjust the data labels so all are readable.
• Only include a legend if it contains valuable information.3. Create one more pivot table to display some interesting data. Name the tab “˜Pivot 3′. Include a description ofwhat you are showing in a textbox next to your pivot table.
• Format all table data appropriately (currency or percentages as described in part 2).
• Format all tables with borders and background colors (don’t use the default formats)
• Change any headings or ending (total) labels that you can so that they are more descriptivePart 4 – Filtering
• Create 2 worksheets and name their tabs Filter1, and Filter2.• Copy the Book worksheet data into each one of the filter worksheets.• Filter 1 – display region “˜West’ records.• Filter 2 – show some interesting analytics. Add a textbox to the worksheet to explain what you are showing.Part 5 – Finishing Up
• **Use the IFERROR function and if a user enters aBook Code that doesn’t exist, display “˜Code Not Found’for the title and blanks for the rest of the cells below.
• **Protect the LookUp worksheet so that the onlychange a user can make is to enter a different BookCode. They shouldn’t be able to change any other cellsin the worksheet. Don’t use a password, just leavethat blank. Don’t guess how to do this, if you don’tknow – watch the Tips on it. Test it when you’re doneto be sure we can open the worksheet and enter anew Book Code and be sure we can’t change any othercells in the worksheet.
• Your worksheets should be in the following order: LookUp, Books, your 3 pivots and then your 2 filter worksheets.
• Once a user enters a new Book Code in the LookUp worksheet, all 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 thisfeature 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 1decimal place.
• Check your formulas, be sure they are correct and make sense. For example, if you are subtracting 2 numbers don’t use theSUM formulas (sum is for adding). Excel may figure out what you mean, but we want the formulas to be used correctly (show
the link provided below show all this information in a more ordered way if youre confused by the long wall of text above.