Create a blank workbook. Name it using your Last name followed by your initials and _ 2EX (underscore then 2EX). For Example: 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 within one 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. If the 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 color scheme for your project – but include borders and backgrounds and include all of the components, as shown below. Component #1 – Title Include a title with your name and any other information you think is appropriate. Merge and center it across all columns with data. Below that add the current date formula, so that each time the file is opened, the current date is displayed. Merge and center 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 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). Add a comment (using Excel’s comment feature) to your title and in the comment, insert the date the worksheet was created and your name. Component #2 – Input Area Add 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 Information o Use the VLOOKUP function/formula and search the Book worksheet for the code that the user entered in Component 2. 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 formulas Component #4 – Calculations Calculate 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 and amount 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 this message. If there are enough units on hand to meet the sales goal, do not display any message. Note: Not displaying any message is the brain-teaser part of the project (and not worth many points). The idea is to play around with the IF statement to get the desired results. You may have to think a little bit outside the box, but there are a few ways to accomplish this. ▪ Display the Lookup information & calculations in the same order as the example above. Component #5 – Graph/Chart o 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 any additional 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 should also 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 (3 worksheets 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 any numbers 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 the pivot 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 of what 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 descriptive Part 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 a Book 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 only change a user can make is to enter a different Book 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 Book Code and be sure we can’t change any other cells 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 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 the link provided below show all this information in a more ordered way if youre confused by the long wall of text above.