Computer Science Follow the instructions on excel to complete the assignment
Syed Mohiuddin
3 years ago
50
files (2)Excel2021InPractice-Ch9IndependentProject9-5-SIMnet.pdf
PlacerHills-09.xlsx
Excel2021InPractice-Ch9IndependentProject9-5-SIMnet.pdf
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:
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.
PlacerHills-09.xlsx
Calculator
| Commission Split | |||
| Calculator | |||
| Price Group | Minimum Price | Maximum Price | Buyer Agent % |
| 1 | $ - 0 | $ 1,499,999 | 50% |
| 2 | $ 1,500,000 | $ 5,499,999 | 45% |
| 3 | $ 5,500,000 | $ 9,499,999 | 40% |
| 4 | $ 9,500,000 | $ 13,499,999 | 35% |
| Selling Price | $ 2,750,000 | ||
| Commission Rate | 5% | ||
| Total Commission | $ 137,500 | ||
| PHRE Share | $ 61,875 | ||
| Buyer Agent % | Amount | ||
| 25% | |||
| 30% | |||
| 35% | |||
| 40% | |||
| 45% | |||
| 50% | |||
| 55% | |||
| 60% |
Price Solver
| Commission Split | ||||
| Calculator | ||||
| Price Group | Minimum Price | Maximum Price | To Buyer Agent | Fees |
| 1 | $ - 0 | $ 1,499,999 | 50% | 1.25% |
| 2 | $ 1,500,000 | $ 5,499,999 | 45% | 1.30% |
| 3 | $ 5,500,000 | $ 9,499,999 | 40% | 2.00% |
| 4 | $ 9,500,000 | $ 13,499,999 | 35% | 2.15% |
| Selling Price | $ 4,500,000 | |||
| Listing Commission | 5% | |||
| Total Commission | $ 225,000 | |||
| To Buyer Agent | $ 101,250 | |||
| Fee Calculation | $ 1,266 | |||
| Net Commission | $ 99,984 | |||
Sales Forecast
| October 22 | |||||
| Address | City | Sq Ft | Closing Date | Sale Price | Community Donation |
| 3420 Milburn Street | Rocklin | 1900 | 7/24/23 | $650,000 | |
| 2128 Wedgewood | Roseville | 1184 | 7/31/23 | $575,000 | |
| 4336 Newland Heights Drive | Rocklin | 1840 | 8/7/23 | $485,000 | |
| 131 Aeolia Drive | Auburn | 1905 | 8/14/23 | $615,500 | |
| 12355 Krista Lane | Auburn | 2234 | 8/21/23 | $725,000 | |
| 1096 Kinnerly Lane | Lincoln | 1948 | 8/28/23 | $620,000 | |
| 272 Lariat Loop | Lincoln | 1571 | 9/4/23 | $485,000 | |
| 1255 Copperdale | Auburn | 1456 | 9/11/23 | $395,900 | |
| 324 Center Point | Roseville | 1480 | 9/18/23 | $410,000 | |
| 411 Marion Street | Auburn | 1950 | 9/25/23 | $615,000 | |
| 17523 Oleander | Sacramento | 2100 | 10/2/23 | $825,000 | |
| 1044 Lake Street | Elk Grove | 1755 | 10/9/23 | $810,000 | |
| 15802 Centennial | Davis | 1950 | 10/16/23 | $765,000 | |
| 14313 Clearview | Sacramento | 2200 | 10/23/23 | $899,000 | |
| 12222 South Ann Street | Elk Grove | 2100 | 10/30/23 | $835,000 | |
| 2330 West 120 Street | Davis | 1700 | 11/6/23 | $650,000 | |
| 7240 Westbrook Drive | Sacramento | 1900 | 11/13/23 | $715,000 | |
| 419 Harlem Avenue | Elk Grove | 2000 | 11/20/23 | $799,900 | |
| 9023 Evergreen Lane | Davis | 1850 | 11/27/23 | $820,000 | |
| 4230 Madison Street | Sacramento | 1900 | 12/4/23 | $735,900 | |
| 1330 Harrison Street | Elk Grove | 2100 | 12/11/23 | $915,000 | |
| 401 Lombard Avenue | Davis | 1650 | 12/18/23 | $585,000 |
image1.png
- Lifespan Developmental Psychology - Six Personalities Identified by John Holland
- read the attached five pages then write a reflection paper following the exact format that is provided in the description
- Lab -Intro to PHP
- milestone 2162
- Proj mgmt work to be done in 2 hrs...serious tutors only no time-wasters please
- week 1
- OPEN FOR ANYONE . EXCELLENT JOB OR REFUND MONEY
- question for John Mureithi
- Reflect upon the strengths, weaknesses, opportunities and threats associated with a business that you are familiar with (one you work at, one you completed your assignments on, or one you have just acquired knowledge about). Suggest two (2) strategic mark
- Individual Presentation Marking Guide This marking guide is aimed at assisting academic judgement. Mark out of 100. An ideal presentation will include the following attributes: Notes and Table of References (weighting 50%): • A clear cover page with al
