Accounting,Algebra,Applied Sciences,Architecture and Design,Art & Design,Biology,Business & Finance,Calculus,Chemistry,Communications,Computer Science,Economics,Engineering,English,Environmental science,Essay writing Hide all
Teaching Since:
Jul 2017
Last Sign in:
304 Weeks Ago, 1 Day Ago
Questions Answered:
15833
Tutorials Posted:
15827
Education
MBA,PHD, Juris Doctor
Strayer,Devery,Harvard University Mar-1995 - Mar-2002
Experience
Manager Planning
WalMart Mar-2001 - Feb-2009
Category > Computer SciencePosted 21 Nov 2017My Price10.00
company that specializes in flipping houses for profit is entering
Help please not the best at spreadsheets. My excel is not the most updated either...
CMPSC 203 Week 4 House Flip A company that specializes in flipping houses for profit is entering a new market and has asked you to provide sales analysis for the new market. They have provided you a full list of sales based on location, house type, square footage, sale price, and sale date. Subtotals You need to identify the locations and housing type that have the highest Sale Price. a. Open e05_exam_chap_data and save as e05_exam_chap_LastnameFirstname. b. Click the Totals worksheet and sort the data by Location in alphabetical order and then by House Type in alphabetical order. c. Apply the Subtotal feature to find the average Sale Price by House Type. d. Insert a new cell in A2 and shift all cells down. e. Type Downtown in the new A2 cell. f. Collapse the data to only show subtotals and grand total rows. g. Set the print area for the populated rows and columns to fit to one page. Create a PivotTable You need to determine if some locations may have larger square footage. You discovered that housing prices seem to be very similar regardless of location in the steps above. Now we will look for locations that may have smaller square footage, which will typically require less renovation costs. h. On the Sales worksheet, create a new blank PivotTable on a new worksheet. i. Rename the new worksheet to Square Footage and move the worksheet to third in the tab order. j. Add House Type to the columns, Location to the rows, and Average of Square Feet to the Values. k. Change the Value Field Settings to show the square feet as a number with 0 decimal places. l. Change the name of the PivotTable to Square Feet by Location. Create a Calculated Column You need to add the Average Sale Price to see if there are locations that have smaller square footage and higher sales prices. m. Add Average Sales Price to Values. n. Change the Value Field Settings to show % of Column Total. Filter and Apply a Style The company has asked that you rule out any Condo that is lower than floor 10 because they are harder to sell. o. Add a filter to only show listings that are N/A or floor 10 and higher. p. Apply style Pivot Style Light 16. q. Display banded rows. Add a Slicer and Timeline To quickly analyze the data, you will need to add a Slicer and Timeline. r. Add a Slicer with the following criteria: Field = Location Style = Slicer Style Dark 5 Height = 3.0” Width = 1.5” s. Add a Timeline with the following criteria: Field = Sale Date Style = Timeline Style Dark 5 Height = 1.5” Width = 6.0” Filter = 2016 Q3 through 2017 Q4 Create a PivotChart To visualize the sales price by House Type and Location, you need to add a PivotChart. t. Copy the Square Footage worksheet and create a new worksheet named Sales Chart. u. Remove the Slicer and Timeline. v. Make the following changes to the PivotTable: Remove all filters Remove Square Feet from Values Change the Value Field Settings for Average Sale Price to No Calculation and Currency w. Add a PivotChart with the following criteria: Type = Clustered Bar Style = Style 8 Height = 3.0” Width = 6.5” x. Hide the Average Sale Price field. y. Add the chart title Average Sale Price at the top of the chart. z. Save and close the file. Based on your instructor’s directions, submit e05_exam_chap_LastnameFirstname.
