The writer is very fast, professional and responded to the review request fast also. Thank you.
Book/movie review
22/08/2022
10/22/23, 4:55 PM Excel 2021 In Practice – Ch 9 Independent Project 9-5 – SIMnet
https://lonestarup.simnetonline.com/sp/assignments/projects/details/8403931 1/2
Excel 2021 In Practice – Ch 9 Independent
Project 9-5
COURSE NAME Fine BCIS 1305 6003 Fall 2023 | Fine BCIS 1305 6003 Fall 2023
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. Select cell C14 on the Calculator worksheet. Total commission is calculated by multiplying the selling price by
the commission rate.
b. 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).
4. Install the Solver Add-in and the Analysis ToolPak.
5. Name cell ranges.
a. Click the Price Solver worksheet tab.
b. Select cells B12:C12, B14:C14, and B17:C17.
c. Use the Create from Selection command.
6. Use Solver to find target PHRE net commission amounts.
a. Build a Solver problem with cell C17 as the objective cell.
Start Date:08/28/202312:00 AMUS/Central Due Date:10/22/202311:59 PMUS/Central End Date:10/22/202311:59 PMUS/Central
Print Info
Student Name: Mohiuddin, Syed
Student
ID:
[email protected]
Username: [email protected]
10/22/23, 4:55 PM Excel 2021 In Practice – Ch 9 Independent Project 9-5 – SIMnet
https://lonestarup.simnetonline.com/sp/assignments/projects/details/8403931 2/2
Figure 9-102 Data table for shared
commissions
Figure 9-103 Scenario summary report
b. 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.
c. 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.
d. 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).
8. Build an array formula.
a. Select the Sales Forecast
sheet. A community donation equal
to two times the price per square
foot is made for each sale.
b. Select cell F5, type =, and select
cells E5:E26.
c. Type / for division and select
cells C5:C26. The sale price
divided by the square footage
results in a price per square foot.
d. Type *2 to multiply by two and
press Enter.
e. Format the result array as Currency with no decimals.
9. Create a histogram for recent sales.
a. Create a bin range of 10 values starting at 400000 in cell H13 with intervals of 50000, ending at 900000 in cell
H23.
b. 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.
c. Select cell I4 for the Output Range and include a chart.
d. Position and size the chart to span from cell L4 to cell W16.
e. Edit the horizontal axis title to display Selling Price and edit the vertical axis title to Number of Sales.
f. Edit the chart title to display Sales by Price Group.
g. Select and delete the legend.
h. Hide column H.
10. Uninstall the Solver Add-in and the Analysis ToolPak.
11. Save and close the workbook (Figure 9-104). (Your dates are volatile.)
Figure 9-104 Excel 9-5 completed
12. Upload and save your project file.
13. Submit file for grading.
The writer is very fast, professional and responded to the review request fast also. Thank you.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more