Excel work 3 hrs

-

08 . >< J. I A I B C D I E I F I G I H

1 I

F'I IRE: Conunission Split 2 Placer Hills - Real Estate

Cal~ulafor 3

4 I 5

Minimum Ma,cimum Comm ission

6 Price Tier Price Pri ce Split Fees 7 1 5 s 1,499,999 ,----- 15~) 9.0% 8 2 . $ ··-··-·· 1,500,000 • . $ 5,499,999 I __ __ 351 \ 11.0% 9 3 5 5,500,000 $ 9,499,999 45% 12.5% 10 4 $ 9,500,000 $ 13,499,999 55% 13.5% 11 - 13._ Sel ling Price s 4,500,000 Data Table ? X 11._ ~~!!~R Commission 6%

1~ Total Commission $ 270,000 Bow input cel l: I l;s.l

15 Pl-IRE Sp lit $ 94,500 r olumn in put ce ll: ~~ - 1~ Fee Calculation $ 10, 395 ~~ 17 Net Commission s 84,105 ~ 19 Possible Rates Solit Net Pav 20 $ 94,500.00 $ 84,105.00 21 25%

21 30%

23 35%

24 Tier 2 40% 25 45%

26 50%

27 55%

28 60%

29

USING MICROSOFT EXCEL 2016 Independent Project 8 5 (Mac)

Step 1: Download start file

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. a. 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. b. Select cell D20 and create a

reference to cell C17. Both formulas in the data table refer to cell C12, the one variable.

c. Use cell D8 as the column input for the data table. (Figure 8-110).

d. Decrease the decimal two times for the results in the data table.

e. Make a copy of the Tier 2 worksheet and name the copy Tier 1.

f. Edit the label in cell A23 to show Tier 1. g. Edit the selling price in cell C12 to

$1,000,000. h. Recalculate the data table with cell

D7 as the column input for Tier 1.

4. Name cell ranges. a. Click the Price Solver worksheet tab. b. Click cell C12 and name the range

Selling_Price. You cannot use spaces in a range name.

c. Name cell C14 as Total_Commission and cell C17 as PHRE_Commission.

5. Install the Solver Add-in and the Analysis ToolPak.

6. Use Solver to 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.

8-110 Data table setup for Tier 2

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 12/29/17 Page 1

-

Sale Price

IMean

Standard Error

Median

Mode

Standard Deviation

Sample Variance

Kurtosis

Skewness

Range

Minimum

Maximum

Sum

Count

Largest(!)

Smallest(!)

1 370593.2

19986.61

365000

#N/A

93745.5

8.79Ei09

-1.23251

0.01939

2.94100

2.2.8900

52.3000

8153050

22

52.3000

22.8900

8

USING 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. b. Create a Scenario summary report for cells C12, C14, and

C17.

8. Generate Descriptive Statistics for recent sales. a. Click the Sales Forecast sheet tab. b. Generate Descriptive Statistics for cells E4:E26 and note

that the label is in the first row. c. Select the Output Range button, click the entry box, and

select cell G4. d. Select the Summary statistics box and include the largest

and smallest (Kth values) in the report. e. AutoFit column G (Figure 8-111).

9. Create a forecast sheet for the date and price data on the Sales Forecast sheet using a column chart. End the forecast three months from the last date in the table. Position the chart object with its top-left corner in cell E1. Size the chart object to reach cell P20. Name the generated sheet as Forecast. NOTE: Mac 2016 and Office 2013 users skip this step and continue to step 10.

10. Save and close the workbook (Figure 8-112).

11. Upload and save your project file.

12. Submit project for grading.

-111 Descriptive statistics for sales

Step 3: Grade my Project

Step 2: Upload & Save

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 12/29/17 Page 2

-

Placer Hills Co mmissio a Split Calealator

Pf"iceTler

EsJ:ate,_ ________ _

Mini:rnu:m Price

Ma!li.r:nurn Commiuia n Price Spl it Fees

l s S ---· 1499,999 ----·-· 25% 1- 41.0% 5 ··-· 5,499,999 - ··-·-· S5% ,_ !l.Dl6 s ··-· Q,499,m -··-·-· ~~ ,_ ! l.51' S 13,4~9, 999 S.S.1/.. 13 .5~

·- ··- ·- 2 ··- ·- ·- · S ·- ·- ··-1,500,000 l S 5,500,000 4 I 9,500,000

Tier 1

Scllin1.Pr ic.c ·-·-··-•"~ ~ l~,00~0~,000=-i Listin_LCommi.,ion ·->---- ~ 6=,1' rotal commh.don 60,000

P HKE-Sp lit _________ >-'~--'5~,000---1 S:ee c.atcu ladon 1,550

Ne1 Comrnk;sion 13,650

Po~slbl~ Kate.s

s 25:>I s -s 3511 5 40,I s

··-·-·-··-·-· 45" 5 50% 5 55" s 6il,I 5

lf1~$llB,9XI lf2Af)tJ,1£ $lAS,COO

]ll:1111>1i Sil 1,WO l/lB/l!l,1£ Sl~COO l/&IJl)16 5210,U)CI

!/llf,!>Ui SB~{OO

:1/2~ s~~'°° J/27(.!>Ui SB7,{00 ttf]/"4)Ui SJ19,CD0

JD(JDW ,n,,coa ~7/"4>1& S--15.CDCI '2","l01£ $50SSXI 5/1,-,U $J.*O,CD0 5JBr.0-Ui $'-IQ.COO

5,1'15/»16 Sin.mo WlU10'16 S3 2Q.C00 5/29111>16 SUHDO 6/5/"4)Ui S50Q.COO

t(J~tma ~,7XI i(J!(.!>Ui S41~CD0 f(l 6/X)Ui.$A65,,CDQ

1/lf){J-1£ 5)9'(00

'"""""' 7/17/»Ui ,p.,.,,. 7/ll/»Ui

"'""' ..,.,... ..,,,.,,. ..,.,.,,. ,1•fm1t, ..,,,.,,. ..,.,..,,.

1f,ZS(,!>Ui

""'"'~ XJf1(.!>Ui

Split

15,00J

15,000 18,000 21,000 24,000 2) 000 30,00J 33,000 36,000

NH Pa.y

s 13,650 5 l.l,650 5 ]6,380 s 19,110 s 21,840 s ___ 2~10 5 5 s

27,300 30,000 3l,)60

$138,&3,t 5Ui,3 J9 ,,..,,,.. 514:)'11 51.n,s11 514:!,26' 5141,llQ.1 5143)'88

n •.566 5145.3S3 Sl4i,l51 Sl"i.9'5'9 $141,771

S111369

Scenario Summary

Chil nging Cells;

setll~Pr lce Re5Ull Cells;

Selli"L_Price

Total_Commis.sion

PH RE_commlsslon

PIIRI": Placer Hil1s Commissio a Split. Cal~ulalo r

e aJ Estat,...._ _________ ...

Pricene.r Minimum

Prh::e Ma14rnum

Prlc:e Comrnis.sio n

Split ~

,_ _ _ ________ s________________ s ____ l.499"'.9"''0.'+-- - 25~,;+--.,.9~.o,;==i· ,_ __________ S ________ _hS~OOO S _____ t499"',91>9'-'. ·=+---35="+--~' =L0~",

1----7' ---- S s.,sao,ooo $. 0='_'49'=.:9':' .... =-f-----;"ca';;;"t--~",::.5,;;;1" 4 ,------··9;soo.ooo s ·-·-1,.499.9~ ss,; n s%

Tier 2

Setlin.1 Prk e _________ s ____ ~Jso~ooo LktingComm~ion - ---.--- ------ - 6% TotDI Comrnh.iKlfl $ 270,000 PHRE Spl it ________ _ } ______ 9:4,5.00

Fee calc:U in ion S 10,3'95 Nr t Commi ~:iion 5 84,105

Possi.l>le Rate:i Spi'it Net Pay $ 94,500 S 84,1[],5

25" - 5 57.500 ,--·-60.irn- -----------·-· 30¾ i-? ---·-· 81,000 . S ___ -'itiioo

35" $ 9 t1,500 ) 8 4,1 05 1------40%-$ 108.000 S 96,120 ,_ ____ 45* s 1.21.soo s 10S:1ii"s ---·-·-·-·-·-· 509' J _____ 135,000 _ S __ l l ~ lSO

55% 5 1~11,500 S 132.155 r------- 60*~ 162,000 S 144,1BO

1111111 I

( llrl't'rtt Vi:lil u~ S';O ()O{) S 7':, 00:J $100,00)

2,616 ,431 s 2,516,431 s 3,924,647 5,232,862

2161&,43 1 s 2!616,431 s 3,9:M,64 7 5,2 32,862 156,986 s 156,986 5 235,479 313,972

50 ,000 s 50.000 s 75.000 100,000 Notes : CU-rent Values column represents values c f changilg ce lls al

tme .SC:enario Sumim r, Report was created. Chmgrng cells tor each scenario are hli:J,lghted n groy.

USING MICROSOFT EXCEL 2016 Independent Project 8 5 (Mac)

8-112 Completed worksheets for Excel 8-5

Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 12/29/17 Page 3