Good Evening Again Prof Patrick,Again I like working with you every week for doing great work with Excel

Spreadsheets.  I would like to ask for your help again. I have attached the directions along with the spreadsheet for completion.

• Attachment 1
• Attachment 2

 ATTACHMENT PREVIEW Download attachmentScenario/SummaryYou are an intern with the Regional Realty Association and are analyzing the claimmade by Luke Liberty Realty that “we get your price.” You have prepared a spreadsheetthat shows data relating to 3 months’ sales by Luke Liberty Realty. You are going todetermine the percent of asking price for each home sold and analyze the sales data todetermine the true track record of the company in selling homes for the asking price.your final assignment file should include seven worksheets (Documentation, Sales Data, SortData, AskvsSellbyAgent, Filtered Data, Conditional Format, Pivot Table), each with acorresponding example, as shown in the video tutorials.STEP 1: Open, Save, and Document the WorkbookYou must open a workbook that lists home sales for 3 months.A.Download the workbookWeek3_iLab_LibertyRealty_Student.xlsxB.Open the workbook in Excel, and save it according to the file naming convention above.C.Insert a documentation sheet that includes title, author, date, and purpose. Format itappropriately, including changing the Sheet Name to Documentation. At the end of this activity,you will be asked to provide a conclusion as to whether this realty company “really gets yourprice.”STEP 2: Add Calculations and Format Sales DataWorksheetA formula is used to calculate the percent of asking price, and this formula is applied toall listed sales. You will format the list in an attractive and useful manner.A.Calculate the percent of asking price for each sale (selling price divided by asking price).Enter the formula in Column F. Format Column F as a percentage with one decimal.B.Format Columns D and E as currency, with no decimals. Format Columns G and H asdates so just the day and month (e.g., 5-May) are visible. You will need to use the NumberFormat dropdown list, select More Number Formats, and select the Date category.C.Widen columns to make all data and headings visible. Format titles as appropriate. Boldand center the column headings.Your Sales Data worksheet will look something like this.

View the AnswerSTEP 3: Sort Data and Create Summary Report and ChartA.Before analyzing data, it is a good idea to copy the data to a new worksheet so that youhave a back-up copy of the original data. Right click on the Sales Data sheet tab and selectMove or Copy. Create a copy of the current spreadsheet and move it to the end. Name thesheet Sorted Data.B.In order to sort the data, you may want to convert them to a data table. Format the datatable to look attractive.C.Sort the data by selling agent (A to Z) and then by asking price (largest to smallest).D.Convert the sorted data back into a range, and create a summary report showing thetotal asking price and total selling price by selling agent.E.Create a column chart that shows the difference by agent between asking price andselling price.STEP 4: Filter the DataA.Create another copy of the original sales data. Rename the sheet Filtered Data.B.Convert the range to a table, and then filter to show only those properties sold in Miamiby agent Carey.STEP 5: Apply Conditional FormattingYou determine that one measure of how well the company “gets your price” is to set ascale. Excellent performance is at or above the asking price. Good performance isbetween 95% and 100%. Below 95% is average performance. You will createconditional formatting rules to display graphically the performance level for each sale.Make a copy of the original sales data chart. Name the new sheet Conditional Format.A.Select the data range and insert a table.B.Use Advanced Formatting to set up the following conditional formatting rules for the fieldPercent of Asking Price.Greater than or equal to 100% = GREEN95% to 99.9% = YELLOWLess than 95% = REDC.Use Percent of Asking Price as the Sort Key, and sort by color with green first, thenyellow, and then red.STEP 6: Create a Pivot Table

Show

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"