Verizon 8:14 AM 100% coursehero.com MICRO 1 of 3 Independent Project 8 5 (Mac) Independent Project 8-5 (Mac 2016 Version) At Placer Hills Real...
View the step-by-step solution to:

Question

I need help on doing Simnet excel Ch8 indipendent project 8-5, more instructed steps on where to go and how to

solve the project. 5A4E8137-5061-40BF-BC46-C39386AA6CEF.png277283FB-C358-4A92-9065-B93949F59234.png

277283FB-C358-4A92-9065-B93949F59234.png

Verizon
8:14 AM
100%
coursehero.com
MICRO
1 of 3
Independent Project 8 5 (Mac)
Independent Project 8-5 (Mac 2016 Version)
At Placer Hills Real Estate, the commission and fee split between the listing and selling agents depends on price
tiers (levels). You create a one-variable data table to display commissions and net earnings for various split rates.
You copy the sheet and edit it to show results for another price tier. Additionally, you create scenarios for selling
price and commission, generate summary statistics, and create a forecast sheet.
Skills Covered in This Project
Build a one variable data table.
Generate descriptive statistics.
Use Solver.
Create a forecast sheet.
Create and manage scenarios.
1.
Open the PlacerHills-08 workbook and click the Enable Editing button. The file will be renamed
automatically to include your name.
2.
Review formulas.
Select cell C14 on the Tier 2 worksheet. The total commission is calculated by multiplying the
selling price by the listing rate.
b .
Select cell C15. The nested IF function checks the selling price (C12) to determine the
commission split percentage (column D).
C.
Select cell C16. The nested IF function checks the selling price (C12) to determine the fee
percentage (column E) and multiplies the commission (C15) to calculate the fee in dollars.
d. Select cell C17. The net commission is calculated by subtracting fees from the PHRE amount.
3. Build one-variable data tables.
a. Select cell C20 and create a
reference to cell C15.
I X V &
Select cell D20 and create a
N -L
G
reference to cell C17. Both formulas in
the data table refer to cell C12, the
PHRE
Placer Hills
Commission Split
one variable.
Real Estate
Calculator
C.
Use cell D8 as the column input for the
data table. (Figure 8-110).
Minimum
Maximum
Commission
d. Decrease the decimal two times for
Price Tier
Price
Price
Split
Fees
the results in the data table.
1,499,99
25% 9.0%
1,500,000 $
5,499,999
35%i 11.0%
e.
Make a copy of the Tier 2 worksheet
5.500.000 S
9,499,999
45% 12.5%
9,500,000 $
13,499,999
55% 13.5%
and name the copy Tier 1.
f.
Edit the label in cell A23 to show Tier 1.
Selling Price
4,500,000
Data Table
X
Listing Commission |
6%
Edit the selling price in cell C12 to
Total Commission
270,000
Row input cell:
$ 1,000,000.
PHRE Split
94,500
Column input cell: SDS8
Fee Calculation
10,395
h.
Recalculate the data table with cell
Net Commission
84,105
OK Cancel
D7 as the column input for Tier 1.
Possible Rates
Split
Net Pay
S
94,500.00 | $ 84,105.00
4. Name cellranges.
2596
a. Click the Price Solver worksheet tab.
30%
b. Click cell C12 and name the range
Tier 2
35%
40%
Selling_Price. You cannot use spaces
45%
50%
in a range name.
53%
C.
Name cell C14 as Total_Commission
60%
and cell C17 as PHRE_Commission.
8-110 Data table setup for Tier 2
5. Install the Solver Add-in and the Analysis
ToolPak.
6. Use Solverto find target PHRE net commission amounts.
a. Build a Solver problem with cell C17 as the objective cell. For the first solution, set the objective
to a value of 50000 by changing cell C12. Save the results as a scenario named $50,000.
016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 12/29/17 Page 1
MICROCAPT FUSEI 2016
m

5A4E8137-5061-40BF-BC46-C39386AA6CEF.png

Verizon
8:14 AM
100%
a coursehero.com
reference to cell C17. Both formulas in
the data table refer to cell C12, the
N
PHRE
Placer Hills
Commission Split
one variable.
2. CSF Cell D8 as the column input for the
Real Estate
Calculator
data table. (Figure 8-110).
Minimum
Maximum
Commission
d. Decrease the decimal two times for
Price Tier
Price
price
Split
Fees
the results in the data table.
1,499,999
25% 9.0%
1,500,000 $
5,499,999
35%1 11.0%
e. Make a copy of the Tier 2 worksheet
5.500.000 S
9.499,999
45% 12.5%
9,500,000 $ 13,499,999
55% 13.5%
and name the copy Tier 1.
f.
Edit the label in cell A23 to show Tier 1.
Selling Price
4,500,000
Data Table
X
Listing Commission
Edit the selling price in cell C12 to
Total Commission
270.000
Row input cell:
94,500
Column input cell: SDSS
$ 1,000,000.
PHRE Split
.S
Fee Calculation
$
10,595
h.
Recalculate the data table with cell
Net Commission
84,105
OK Cancel
D7 as the column input for Tier 1.
Possible Rates
Split
Net Pay
94,500.00 $ 84,105.00
4. Name cellranges.
25%
a. Click the Price Solver worksheet tab.
30%
b. Click cell C12 and name the range
Tier 2
35%
40%
Selling_Price. You cannot use spaces
45%
509%
in a range name.
53%
60%
C.
Name cell C14 as Total_Commission
and cell C17 as PHRE_Commission.
8-110 Data table setup for Tier 2
5. Install the Solver Add-in and the Analysis
ToolPak.
6. Use Solverto find target PHRE net commission amounts.
a. Build a Solver problem with cell C17 as the objective cell. For the first solution, set the objective
to a value of 50000 by changing cell C12. Save the results as a scenario named $50,000.
016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 12/29/17 Page 1
MICROSOFT EXCEL 2016
Independent Project 8 5 (Mac)
b. 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.
C.
Restore the original values and run a third Solver problem to find a selling price for a net
commission of $ 100,000. Save these results as a scenario.
7. Manage scenarios.
a. Show the $ 50,000 scenario in the worksheet.
Sale Price
b. Create a Scenario summary report for cells C12, C14, and
C17.
Mean
370593.2
8.
Generate Descriptive Statistics for recent sales.
Standard Error
19986.61
a. Click the Sales Forecast sheet tab.
Median
365000
b. Generate Descriptive Statistics for cells E4:E26 and note
Mode
#N/A
that the label is in the first row.
C.
Select the Output Range button, click the entry box, and
Standard Deviation
93745.5
select cell G4.
Sample Variance
8.79E+09
d.
Select the Summary statistics box and include the largest
Kurtosis
-1.23251
and smallest (Kth values) in the report.
Skewness
0.01939
e. AutoFit column G (Figure 8-111).
Range
294100
9. Create a forecast sheet for the date and price data on the
Minimum
228900
Sales Forecast sheet using a column chart. End the forecast
three months from the last date in the table. Position the chart
Maximum
523000
object with its top-left corner in cell E1. Size the chart object to
Sum
8153050
reach cell P20. Name the generated sheet as Forecast. NOTE:
Count
22
Mac 2016 and Office 2013 users skip this step and continue to
Largest(1)
523000
step 10.
Smallest(1)
228900
10. Save and close the workbook (Figure 8-112).
8-111 Descriptive statistics for sales
11. Upload and save your project file.
12. Submit project for grading.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Let our 24/7 Finance tutors help you get unstuck! Ask your first question.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes
A+ icon
Ask Expert Tutors