Answered>Order 23946

***I already submitted this, just wanted to see how this look the correct way****

Original

  1. Copy the original spreadsheet to new worksheets that are to be labeled: Tables, Filter Table, Subtotals, and Pivot Table. Make sure the spreadsheets are in that order and are followed by Original, What If Analysis, and Goal Seek. Insert your name on the header of Tables.

Tables

  1. On the Table tab, make this a table. Use Table Style Light 14. Remove Duplicates.
  2. Determine length of time the house was on the market and insert it as a column named On Market after the Sales Date. (You can subtract dates.) Make sure this result is formatted as a number.
  3. Subtract the Sell Price from the Ask Price. Label this column Spread and put just before the List Date. (It should be negative if the ask price is less than the sell price.) Conditionally format all numbers less than 600 to be in green fill with dark green text.
  4. Add a total row using Table Style Options. Find the average of the Spread and the average of the On Market.  Find the count of the number of houses sold and put it in the SQUARE FOOT
  5. Sort by using levels Project # then Lot #.
  6. Add three new rows (above the table). Enter the following column headings: Lot#, # Bathrooms, Sell Price.
  7. For Lot # 163in the appropriate columns:Use VLookup to determine the number of Bathrooms. An exact match is wanted.
  8. Use AverageIf to determine the average Sell price.

Filter Table

  1. Conditionally filter the table to only allow Project # 61.

Subtotals

  1. On the Subtotal, after sorting appropriately, determine the total sell price and square foot by Project #. Show only the subtotals and grand total.

Pivot Table

  1. Go to the Pivot Table Tab. Create a pivot table on a new sheet and label this tab Pivot Results.
  2. Project # on the rows, the columns are by List Date. Show the sum of the asking price in the values.
  3. Format in Pivot Style Light 15. Group the order dates by months.
  4. Create a pivot table bar chart. Filter the report to only show houses with 3

What-If Analysis

  1. The information has a , (comma) delimiter. Separate out the columns. On the row above the column headers, use a formula to give the proper capitalization to the columns. 
  2. Determine the monthly payment (hint: PMT), total payment, and interest paid for a home loan. Make sure all cells in the loan calculation (From Sales Price to Interest Paid) are named by their column headings.  
  3. Use Scenario manager summary to determine which bank is has the lowest monthly payment and total interest. Store the results on a different sheet (named Scenario Manager). In order of fields listed:

Advantage, 200000, 5%, 25

Bank of America, 200000, 5.23%, 30

Goal Seek

  1. Your cousin needs a profit of $5000 for her foreign language club trip, how much does the scarf need to sell for (Scarf Price)? She can at most make 104  The fixed price (cost to start up operation) is $43, the variable cost (cost per scarf) is $5.   
  2. In the Consolidation section: Find the SUM and Average of: B5 across all the worksheets.
 
"Not answered?"
Get the Answer
/by