advanced spreadsheet use
DESCRIPTION
Advanced Spreadsheet Use. Advanced Spreadsheet Use Head/analysis Heart/passion Gut check/faith. Advanced Spreadsheet Use Measuring returns Project return Invest $100,000,000 Profit $15,000,000 15% pa. Advanced Spreadsheet Use ROE Case 1 Invest $100,000,000 Finance 75%, 4.5%, - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/1.jpg)
Advanced Spreadsheet Use
![Page 2: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/2.jpg)
Advanced Spreadsheet Use
Head/analysis Heart/passion Gut check/faith
![Page 3: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/3.jpg)
Advanced Spreadsheet Use
Measuring returns
Project return Invest $100,000,000 Profit $15,000,000 15% pa
![Page 4: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/4.jpg)
Advanced Spreadsheet Use
ROE
Case 1 Invest $100,000,000 Finance 75%, 4.5%,
15 years Profit ≠ $15,000,000 EBITDA
http://www.old.dramatispersonae.org/images/roe-case-1.xls
ROE, Case 1
Project investment $ 100,000,000.00
Finance $ 75,000,000.00 75% 4.50% 15 yrs
Equity $ 25,000,000.00
EBITDA $15,000,000 per yr
Debt financing ($6,983,535.61) per yr
Profit $8,016,464 per yr
ROE 32.1% pa
![Page 5: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/5.jpg)
Advanced Spreadsheet Use
ROE
Case 2 Invest $100,000,000 Finance 75%, 9%,
15 years Profit ≠ $15,000,000 EBITDA
ROE, Case 2
Project investment $ 100,000,000.00
Finance $ 75,000,000.00 75% 9.00% 15 yrs
Equity $ 25,000,000.00
EBITDA $15,000,000 per yr
Debt financing ($9,304,416.20) per yr
Profit $5,695,584 per yr
ROE 22.8% pa
http://www.old.dramatispersonae.org/images/roe-case-2.xls
![Page 6: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/6.jpg)
Advanced Spreadsheet Use
ROE
Case 3 Invest $100,000,000 Finance 75%, 9%,
8 years Profit ≠ $15,000,000 EBITDA
ROE, Case 3
Project investment $ 100,000,000.00
Finance $ 75,000,000.00 75% 9.00% 8 yrs
Equity $ 25,000,000.00
EBITDA $15,000,000 per yr
Debt financing ($13,550,578.34) per yr
Profit $1,449,422 per yr
ROE 5.8% pa
http://www.old.dramatispersonae.org/images/roe-case-3.xls
![Page 7: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/7.jpg)
Advanced Spreadsheet Use
ROE
Target range Large cos 20% to 24% Students 20% to 30% (architects 14%) Credit card cos 18% to 39% VCs 30%+ Vultures 35%+ Maximum allowed under CDN Criminal Code = 60% pa
including extra charges or service fees
![Page 8: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/8.jpg)
Advanced Spreadsheet Use
ROE
Owners of Winnipeg payday lending co charged Investigation reveals people paying criminally high
interest Det-Sgt Terlinski says co allegedly charging rates of
several thousand per cent http://www.cbc.ca/news/canada/loan-company-charged-i
n-criminal-interest-rate-case-1.601087
![Page 9: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/9.jpg)
Advanced Spreadsheet Use
ROE
Decreases with age
![Page 10: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/10.jpg)
Advanced Spreadsheet Use
ROE
Canada housing
![Page 11: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/11.jpg)
Advanced Spreadsheet Use
ROE
Canada housing
Compound Growth Rate in Average Canadian House Prices 1980-2012
Year Avg Price Compound Growth
1980 $60,000.00 2012 $370,000.00 5.85% p.a.
32 $310,000.00
E&OE/Approx
![Page 12: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/12.jpg)
Advanced Spreadsheet Use
ROE
Canada housing Simple Housing Example
2011 $ 349,551.25 5.85% pa housing inflation in Canada
2012 $ 370,000.00
Delta $ 20,448.75 5.85% pa check
Debt $ (262,163.44) 75%
Equity $ 87,387.81 25%
ROE 23.40% pa
http://www.old.dramatispersonae.org/images/simple-housing-example-roe.xls
![Page 13: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/13.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
great HR using OPM forced savings innovation capital equipment
![Page 14: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/14.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
location network effects biz models/marketing channels reduce problem from
one to many to one to a few branding, co-branding, co-opetition and co-creation inflation
![Page 15: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/15.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
HR/hire up
![Page 16: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/16.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
OPM/as long as cost of OPM < project returns
![Page 17: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/17.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Forced savings Forced Savings
Lab space 20,000 sf
Rental cost $27 per sf per yr gross*
Rental cost $540,000 per yr
HST $70,200 13%
Total rental cost $610,200 per yr
http://www.old.dramatispersonae.org/images/forced-savings.xls
![Page 18: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/18.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Forced savings
Purchase price $200 per sf
Purchase price $4,000,000
Debt $4,000,000 100% 4.50% 15 yrs
Equity $0 0%
Debt payment ($372,455.23) per yr
PPMT
1 ($192,455.23)
2 ($201,115.72)
3 ($210,165.93)
Forced savings $603,736.88
* Including operating costs, admin, prop taxes, utilities
![Page 19: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/19.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Forced savings Principal v interest curve/bank term limit may reset
clock
![Page 20: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/20.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Forced savings P v I
Amortization Made Simple
Loan to prof $ 1,000.00
Interest rate 15%
Annual repayment by prof $ 298.31
Year Interest Principal (PPMT) Principal Outstanding
1 $ (150.00) $ 148.31 $ 851.69
2 $ (127.75) $ 170.56 $ 681.13
3 $ (102.17) $ 196.14 $ 484.99
4 $ (72.75) $ 225.56 $ 259.43
5 $ (38.91) $ 259.40 $ 0.04
![Page 21: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/21.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Financial engineering Treat investors at least
as well as promoter
BMC Financing
Corp Promoter Total Capital Investor
1 $5,000,000 51% $9,803,921.57 $4,803,921.57
2 $9,803,921.57 51% $19,223,375.62 $9,419,454.06
3 $19,223,375.62 51% $37,692,893.38 $18,469,517.76
4 $37,692,893.38 51% $73,907,634.08 $36,214,740.70
5 $73,907,634.08 51% $144,916,929.57 $71,009,295.49
6 $144,916,929.57 51% $284,150,842.30 $139,233,912.73
7 $284,150,842.30 51% $557,158,514.31 $273,007,672.01
8 $557,158,514.31 51% $1,092,467,675.12 $535,309,160.81
9 $1,092,467,675.12 51% $2,142,093,480.63 $1,049,625,805.51
10 $2,142,093,480.63 51% $4,200,183,295.36 $2,058,089,814.73
http://www.old.dramatispersonae.org/images/bmc-financing.xls
![Page 22: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/22.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Financial engineering Treat investors at least as well as promoter Sweat equity 10% to 25% All shareholders equal Otherwise, it’s a form of debt
![Page 23: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/23.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation
All of life experiment until proven otherwise Try something, works, do more Try something else, doesn’t work, stop As soon as implementation begins, experimentation
starts/change happens
![Page 24: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/24.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/list of 30 accidental discoveries changed world
a. penicillin b. post-it-notes c. strikeable match d. the lever e. plastic f. velcro
![Page 25: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/25.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/list of 30 accidental discoveries changed world
g. law of gravity h. radioactivity i. microwave j. mauve color k. dynamite l. pacemaker m. anesthesia
![Page 26: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/26.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/list of 30 accidental discoveries changed world
n. teflon o. saccharin p. x-ray q. vaseline r. vulcanized rubber s. chicken cholera vaccine
![Page 27: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/27.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/list of 30 accidental discoveries changed world
t. nuclear fission u. LSD v. safety glass w. making fire x. cave painting ochre y. cellophane
![Page 28: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/28.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/list of 30 accidental discoveries changed world
z. chirality (right and left-handed chemical molecules) aa. gunpowder (blasting gelatin) bb. nylon cc. DNA dd. density
Luck plays role—scientific, business, artistic even political but harder you work, luckier you get…
![Page 29: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/29.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
business models, whether they work as planned or require modification
beta product launches, what reaction markets have battle plans, whether they work as planned or require
modification catalysts, whether they speed things along or not weather, too unpredictable to be meaningfully forecast
![Page 30: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/30.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
weather, too unpredictable to be meaningfully forecast
![Page 31: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/31.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
Canada’s 2013 federal budget finance minister Jim Flaherty announced additional $248
million over five years to strengthen Canada’s meteorological services
billions spent on weather forecasting weather channel huge hit apparently, watching floods, fire, tornadoes, hurricanes,
other disasters profitable and fun (as long as happening somewhere else)
![Page 32: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/32.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
Overall success rates generally 77% better than flipping coin accuracy in Palm Beach higher than for northern shelf
city like Ottawa where much more variability in climate differences small, perhaps 75% versus 79% so far, so good
![Page 33: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/33.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
but real issue not whether Meteorological Service of Canada accurately predicts high temp tomorrow (within three degrees, generally accepted range) or whether it will rain or snow or hail or flood…
real issue is can they predict when a change will occur?
![Page 34: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/34.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
freakonomics blog, JD Eggleston, in study of local weather for Kansas City, found forecaster who always and arbitrarily predicted never rain right 86.3%
3 of KC’s local TV channels 1-day out success rates— 87% difference unlikely to be statistically significant
http://profbruce.tumblr.com/post/58779699201/how-accurate-are-weather-forecasts-in-canadas
![Page 35: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/35.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
economic policies, whether the law of unintended consequences invoked
children, how they respond to discipline and incentives motivation, how to get a team to work together HR, who to recruit taste, what you like/don’t like in clothes, fashion, food,
film, music, people
![Page 36: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/36.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Innovation/things you can only learn by doing
sports, how to ski, snowboard, hang glide, windsurf, golf, sail, ride a bike, play tennis, skateboard…
party and events, what works, what doesn’t YouTube video, what goes viral, what doesn’t film, music, novel, what hits/what misses…
Stan Jaffe
![Page 37: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/37.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Capital equipment/hand looms to automated ones
![Page 38: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/38.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Location/by occupying one location, automatically exclude everyone else
![Page 39: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/39.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Network effects/law of large nos
USD Telephone Fax Email Browser Google Skype Amazon
Network effects/community forms through SM
Reddit TechCrunch Twitter Facebook Digg (before iterations) YouTube Kiva LooseButton Khan Academy
![Page 40: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/40.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Biz models/marketing channels reduce problem from one to many to one to a few
Project Wonderful/reverse out pricing work to advertisers Virtual homebuilder/reverse out design to consumers,
bidding/scheduling to trade contractors Grade A Techs/matching suppliers (techies) with
consumers Apple stores Amazon resellers
![Page 41: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/41.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Branding, co-branding, co-opetition and co-creation
Apple macbook pro $3,500 Windows machine $900 Threadless consumers vote on t-shirt designs
![Page 42: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/42.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Branding, co-branding, co-opetition and co-creation
well respected brand creates opportunity to sell successfully
work hard (for years) to establish reputation for good work, high ethical standards and trustworthiness
trust creates environment for you where clients send you more and more work
trust creates environment where clients refer other clients
![Page 43: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/43.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Branding, co-branding, co-opetition and co-creation
trust gives breathing room when make a mistake trust creates personal brand for you individually,
independent of firm change firms, many clients follow trust creates brand brand creates opportunity to turn market into sales
![Page 44: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/44.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Branding, co-branding, co-opetition and co-creation
Marketing Branding Trust Sales Profits Marketing …
![Page 45: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/45.jpg)
(2)Your Reputation and Brand build Trust in you
(3)Trust in you creates an
opportunity for you to Sell in a separate Sales Process
‘TRUST POWERS SALES’
Sales provides additional resources for Marketing
(1)Marketing through a
Marketing Process builds your Reputation
and Brand
![Page 46: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/46.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Branding, co-branding, co-opetition and co-creation
Clarica Life Insurance (now a Sunlife Financial member)
![Page 47: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/47.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Inflation/go where all boats are rising
Nano tech AI Digital data Green PB4L 3-d printing Entertainment
Inflation/go where all boats are rising
Power Transportation/hyperloop
http://www.youtube.com/watch?v=IlnlkMbukmA Farm-in-a-box Mega-cities Continuing ed Entrepreneurship Curation
![Page 48: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/48.jpg)
Advanced Spreadsheet Use
Leverage– ten main sources
Inflation/go where all boats are rising
Customer service Authenticity Land Financial planning ESL Self-help Coaching
Inflation/go where all boats are rising
Mentoring Elder care Yoga/physiotherapy Marijuana/wine/foodie Design Fashion/clothing Future will surprise us
![Page 49: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/49.jpg)
Advanced Spreadsheet Use
Cap Rate
Most real estate professionals use Cap Rates to compare one project with another
Cap Rate (Capitalization Rate) is approximate measure All financial measures are
![Page 50: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/50.jpg)
Advanced Spreadsheet Use
Cap Rate
Cap Rate determined by dividing Operating Income of property by Selling Price
Inverse of Cap Rate is approximation for number of years to earn back capital
Widely used in commercial real estate sector Higher Cap Rate, better for Buyer/worse for Seller
![Page 51: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/51.jpg)
Advanced Spreadsheet Use
Cap Rate Cap Rate Simplified Case 1
Purchase Price $1,320,000 10 unit apartment building
Rent $1,100 per apartment per mth
Rent $132,000 per annum
Property taxes ($19,800) 1.50%
Water ($14,500)
Insurance ($9,800)
Property management ($7,920) 6%
Utilities ($15,000.00)
NOI $64,980
http://www.old.dramatispersonae.org/images/cap-rate-simplified.xls
![Page 52: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/52.jpg)
Advanced Spreadsheet Use
Cap Rate Contingencies ($3,249.00) 5%
Repairs and maintenance ($1,949.40) 3%
Vacancy allowance ($2,599.20) 4%
Adjusted NOI $57,182
Cap rate 4.3% p.a.
Debt $0 0%
Equity $1,320,000 100%
Years to get capital back 23.1
Years to get capital back 23.1 check
![Page 53: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/53.jpg)
Advanced Spreadsheet Use
Cap Rate Cap Rate Simplified Case 2
Purchase Price $1,320,000 10 unit apartment building
Rent $1,100 per apartment per mth
Rent $132,000 per annum
Property taxes ($19,800) 1.50%
Water ($14,500)
Insurance ($9,800)
Property management ($7,920) 6%
Utilities $0.00 tenant pays utilities
NOI $79,980
http://www.old.dramatispersonae.org/images/cap-rate-simplified-case-2.xls
![Page 54: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/54.jpg)
Advanced Spreadsheet Use
Cap RateContingencies ($3,999.00) 5%
Repairs and maintenance ($2,399.40) 3%
Vacancy allowance ($3,199.20) 4%
Adjusted NOI $70,382
Cap rate 5.3% p.a.
Debt $0 0%
Equity $1,320,000 100%
Years to get capital back 18.8
Years to get capital back 18.8 check
![Page 55: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/55.jpg)
Advanced Spreadsheet Use
Cap Rate
Cap Rate measures only cash on cash returns Ignores forced savings Ignores inflation NOI often misstated
![Page 56: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/56.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Best measure so far Can result in > 1 answer/need commonsense
![Page 57: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/57.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of ReturnIRR Made Simple
Loan to prof $ 1,000.00
Interest rate 15%
Annual repayment by prof $ 298.31
Year Interest Principal (PPMT) Principal Outstanding
1 $ (150.00) $ 148.31 $ 851.69
2 $ (127.75) $ 170.56 $ 681.13
3 $ (102.17) $ 196.14 $ 484.99
4 $ (72.75) $ 225.56 $ 259.43
5 $ (38.91) $ 259.40 $ 0.04
$ 999.96 check
http://www.old.dramatispersonae.org/images/irr-made-simple.xls
![Page 58: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/58.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Lender
0 $ (1,000.00)
1 $ 298.31
2 $ 298.31
3 $ 298.31
4 $ 298.31
5 $ 298.27 $ 1,491.51
irr 15% pa
![Page 59: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/59.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of ReturnIRR Made Simple Case 2
Loan to prof $ 1,000.00
Interest rate
Annual repayment by prof $ 350.00
Lender
0 $ (1,000.00)
1 $ 350.00
2 $ 350.00
3 $ 350.00
4 $ 350.00
5 $ 350.00 $ 1,750.00
irr 22.1% pa
http://www.old.dramatispersonae.org/images/irr-made-simple-case-2.xls
![Page 60: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/60.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of ReturnIRR Made Simple Case 3
Loan to prof $ 1,000.00
Interest rate
Annual repayment by prof $ 225.00
Lender
0 $ (1,000.00)
1 $ 350.00
2 $ 225.00
3 $ 225.00
4 $ 225.00
5 $ 225.00 $ 1,250.00
irr 8.6% pa
http://www.old.dramatispersonae.org/images/irr-made-simple-case-3.xls
![Page 61: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/61.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Apple’s iPhonePhones Sold
1 2,320,849 units sold 2 13,727,740 units sold 3 25,103,770 units sold
4 35,000,000 units sold est. 5 46,391,461 units sold est. 6 57,332,809 units sold est. 7 68,274,157 units sold est.
![Page 62: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/62.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Apple’s iPhone Regression Statistics
Multiple R 0.999436215R Square 0.998872748Adjusted R Square 0.998309123Standard Error 581160.6385Observations 4
ANOVAdf SSRegression 1 5.98566E+14Residual 2 6.75495E+11Total 3 5.99241E+14
Coefficients Standard ErrorIntercept -8315281 711773.5115X Variable 1 10941348.3 259902.9387
![Page 63: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/63.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Apple’s iPhone
No iPhones sold per annum = -8,315,281 + 10,941,348.3 x (Year – 2006)
![Page 64: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/64.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Apple’s iPhone
![Page 65: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/65.jpg)
Advanced Spreadsheet Use
IRR, Internal Rate of Return
Apple’s iPhone
IRR Cash Out Cash In Cashflow2006 $ (1,150,000,000.00) $ (1,150,000,000.00)2007 $ (1,150,000,000.00) $ 1,521,123,592.60 $ 371,123,592.62008 $ (1,150,000,000.00) $ 8,997,392,414.17 $ 7,847,392,414.172009 $ (1,150,000,000.00) $ 22,939,590,529.53 $21,789,590,529.532010 $ 22,939,590,529.53 $22,939,590,529.532011 $ 37,576,890,222.28 $37,576,890,222.282012 $ 44,748,034,503.51 $44,748,034,503.51 IRR 288% p.a.
![Page 66: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/66.jpg)
Advanced Spreadsheet Use
Assignment
Server Farm Analysis Iowa v Luleå, Sweden
![Page 67: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/67.jpg)
Advanced Spreadsheet Use
Assignment
Server Farm Analysis
http://www.old.dramatispersonae.org/images/server-farm-analysis.xls
Hand in your spreadsheet assignment next lecture
![Page 68: Advanced Spreadsheet Use](https://reader035.vdocuments.us/reader035/viewer/2022062423/568144de550346895db1aa05/html5/thumbnails/68.jpg)
QCC
Reminders! Have a good study week! Slides:
http://www.old.dramatispersonae.org/images/advanced-spreadsheet-use.ppt