Please help complete these two step by step questions. I have 2 hours before it is due and I need help asap! the two data files are attached and step by step instructions are below. Please HELP!
Property Records [worth 90/200 points]
- Open the Property Records.xlsx file in the Final Exam area of Blackboard, save it to your computer or USB drive, and name it your last name Exercise 1. An example is Thompson Exercise 1.
- Rename the Data worksheet tab Property Data.
- Insert the title Rhode Island Properties at the top of the worksheet and the subtitle 2015 Property Records in the row beneath it.
- Insert four blank rows between the subtitle and the data.
- Format the title and subtitle so they appear larger and bolder, with a different color, than the rest of the text.
- Convert the data to a table. Use a table style of your choice and name the table RI_Records.
- Format the Parcel ID cells in the first column of the table as text so that Excel doesn't confuse them as numbers.
- Turn on the table's Total Row and display the sum for the market value column and the count for the City State column.
- The accounting department likes all dollar values formatted in Accounting format, so make sure the Market Value values are formatted appropriately (including in the Total Row). Decimal places are not needed.
- Sort the data first by City State in A to Z order, then by Zip in A to Z order, then by Market Value in largest to smallest order, then by Last Name in A to Z order. In the resulting Sort Warning dialog box, sort anything that looks like a number as a number.
- Change the column D width if necessary so that all information in that column is displayed and none of it is cut off.
- Copy the worksheet and name the new sheet Filtered Data. Move the new worksheet to the right of the Property Data sheet.
- In the Filtered Data worksheet, use the appropriate filter button to display only the Wakefield, RI and Saunderstown, RI properties.
- Return to the Property Data worksheet and create a PivotTable. Allow the PivotTable to be placed in a new worksheet.
- Name the new worksheet Average Market Values and locate it to the right of the Filtered Data worksheet. Then display the PivotTable data so that the City State information is arrayed down the left side of the table and the average of the market values for each City State is just to the right of the City State data. The PivotTable will be a very simple two-column table.
- Format the market values to be Accounting format with no decimal places, then turn off the display of the field headers. Select a PivotTable style of your choosing.
- Using the PivotTable, create a PivotChart. It must be a 3D clustered column chart that must be located below the PivotTable. Make sure you remove the legend in the chart since it's not necessary. Give the chart an appropriate title. Format the chart as you wish.
- Return to the Property Data worksheet and proceed to cell A1.
- Copy the Parcel ID header cell in A7 to cell A4. Then copy the Market Value header cell in H7 to cell B4.
- In cell A5, create a data validation drop-down list of all Parcel ID cells. Create an appropriate input message and error alert.
- Select a random Parcel ID in the list to verify the list works.
- In cell B5, create a VLOOKUP formula to display the correct Market Value for the selected Parcel ID in the drop-down list. Make sure the resulting Market Value has an accounting format with no decimal places, just like the values in column H.
- Save your work and proceed to the second exercise.
Eck-Sell Imports [worth 50/200 points]
- Open the Eck-Sell Imports.xlsx file in the Final Exam area of Blackboard, save it to your computer or USB drive, and name it your last name Exercise 2. An example is Thompson Exercise 2.
The Orders worksheet contains annual sales data for an import business. It is currently sorted first by Order Month then by Company Name.
- Merge and center A1 through E1. Format the text in the merged cell so it looks like a real title (larger, bolder, and colored font)
- Convert the sales data into a table.
- Conditionally format the order amounts so that if the amount is greater than $5,000, the cell will be highlighted with a green fill and dark green text (one of the built-in color schemes).
- Column G currently contains text pertaining to some basic calculations of the data. Create the appropriate calculations in column H. If the results are dollar values, format the values the same way the values in column E are formatted.
- In cell G10, type Jan. Then use the fill handle of G10 to auto-fill month names down to Dec.
- In cell H10, create a COUNTIF function to count the number of orders placed in January. When done, do the same for the other months.
- Type UK Bonus in cell F3. That will append a new column to the end of the table. Slightly widen the column width so that the words are totally visible. Format the cells in the column to be Currency.
- In cell F4, create an IF function to calculate the following.
If the Country is UK, then multiply the Order Amount by 3%, else, display nothing at all.
- In cell J3, type the words Total UK Bonus and then in K3, calculate the total UK bonuses that appear in the data table, making sure that value is also formatted as currency.
- Save your work to your computer or USB drive, and name it your last name Exercise 2. An example is Thompson Exercise 2.