Excel 2021 In Practice - Ch 9 Independent Project 9-5 - Placer Hills Real Estate (Full answer 2025)

Independent Project 9-5
These instructions are compatible with both Microsoft Windows and Mac operating systems.
At Placer Hills Real Estate, commission is split with the buyer’s agency based on price groups. You create a one-variable
data table to display this information. Additionally, you create scenarios and a histogram.
[Student Learning Outcomes 9.1, 9.3, 9.4, 9.5, 9.7]
File Needed: PlacerHills-09.xlsx (Available from the Start File link.)
Completed Project File Name: [your name]-PlacerHills-09.xlsx
Skills Covered in This Project
Build a one-variable data table.
Use Solver.
Create and manage scenarios.
Build an array formula.
Create a histogram with a chart.
Steps to complete This Project
Mark the steps as checked when you complete them.
1. Open the PlacerHills-09 start file and click the Enable Editing button. The file will be renamed automatically to
include your name.
2. Review formulas for commission calculations.
a.
b.
Select cell C14 on the Calculator worksheet. Total commission is calculated by multiplying the selling price by
the commission rate.
Select cell C15. The IFS function checks the selling price (C12) to determine the buyer agent share (column
D).
3. Build a one-variable data table.
a. Select cell C18 and create a formula to multiply the total commission by the percentage in cell D7.
b. Create the data table using a column input (
Figure 9-102).
1/3
https://cuyamaca.simnetonline.com/sp/assignments/projects/details/9474985
10/27/24, 1:26 PM
Excel 2021 In Practice - Ch 9 Independent Project 9-5 - SIMnet
Figure 9-102 Data table for shared commissions
4. Install the Solver Add-in and the Analysis ToolPak.
5. Name cell ranges.
a.
b.
c.
Click the Price Solver worksheet tab.
Select cells B12:C12, B14:C14, and B17:C17.
Use the Create from Selection command.
6. Use Solver to find target PHRE net commission amounts.
a.
b.
c.
d.
Build a Solver problem with cell C17 as the objective cell.
Set the objective to a value of 50000 by changing cell C12. Use the GRG Nonlinear solving method. Save the
results as a scenario named $50,000.
Restore the original values and run another Solver problem to find a selling price for a PHRE commission of
75000. Save these results as a scenario named $75,000.
Restore the original values and run a third Solver problem to find a selling price for a net commission of
100000. Save these results as a scenario and restore the original values.
7. Manage scenarios.
a. Show the $50,000 scenario in the worksheet.
b. Create a Scenario summary report for cells C12, C14, and C17 (
Figure 9-103).
Figure 9-103 Scenario summary report
8. Build an array formula.
a.
b.
c.
d.
e.
Select the Sales Forecast sheet. A community donation equal to two times the price per square foot is made
for each sale.
Select cell F5, type =, and select cells E5:E26.
Type / for division and select cells C5:C26. The sale price divided by the square footage results in a price per
square foot.
Type *2 to multiply by two and press Enter.
Format the result array as Currency with no decimals.
9. Create a histogram for recent sales.
a.
b.
c.
d.
e.
f.
g.
Create a bin range of 10 values starting at 400000 in cell H13 with intervals of 50000, ending at 900000 in cell
H23.
Use the Analysis ToolPak to create a histogram for cells E5:E26. Do not check the Labels box and select the
bin range in your worksheet.
Select cell I4 for the Output Range and include a chart.
Position and size the chart to span from cell L4 to cell W16.
Edit the horizontal axis title to display Selling Price and edit the vertical axis title to Number of Sales.
Edit the chart title to display Sales by Price Group.
Select and delete the legend.
2/3
https://cuyamaca.simnetonline.com/sp/assignments/projects/details/9474985
10/27/24, 1:26 PM
Excel 2021 In Practice - Ch 9 Independent Project 9-5 - SIMnet
h.
10.
Hide column H.
Uninstall the Solver Add-in and the Analysis ToolPak.
11.
12.
13.
Save and close the workbook (
Figure 9-104). (Your dates are volatile.)
Upload and save your project file.
Submit file for grading.