Excel Finance

profilefaiala2
finance_320_s15_class3.xlsx
Home>Business & Finance homework help>Excel Finance

Plan

Financial Planning Worksheet
If you do the things you need to do when you need to do them, then someday you
can do the things you want to do when you want to do them' - Zig Ziglar
Time 0 - 2 Years 2 - 4 Years 4 - 6 Years 6 - 8 Years 8 - 10 Years
Income Salary
Self
Gross 40,000.00 48,500.00 58,850.00 70,662.00 99,573.00
Net 28,000.00 33,950.00 41,195.00 49,463.40 69,701.10
Spouse
Gross - 0 - 0 - 0
Net - 0 - 0 - 0
Monthly 2,333.33 2,829.17 3,432.92 4,121.95 5,808.42
Self-employment
Investment (interest/dividends)
Rents/real estate
Other/specify
Total Income 2,333.33 2,829.17 3,432.92 4,121.95 5,808.42
Expense Four Walls *
Food
Grocery 200.00 250 300 350 400
Restaurants 50.00 75 100 125 150
Housing
Rent 700.00 800 900
Home 997.50 1,166.67
Real Estate Taxes 335.00 450.00
Capital Repairs (Roof, furnace,electrical, plumbing) 50.00 50.00
Homeowners/mortgage insurance 150.00 175.00
Maintenance 100.00 100.00
Utilities
Heat/air 75.00 100 150 175 175
Phone 50.00 75 100 125 150
Cable/Internet 40.00 50 60 75 100
Transportation
Car payment 396.00 396.00 396.00 - 0 792.00
Operating expense 160.00 184.00 211.60 243.34 279.84
Auto Insurance 173.00 173.00 173.00 173.00 173.00
Total Necessities 1,844.00 2,103.00 2,390.60 2,898.84 4,161.51
Base discretionary income 489.33 726.17 1,042.32 1,223.11 1,646.91
Clothing
Self 50.00 75.00 100.00 125.00 150.00
Spouse
Children
Medical/Dental
Medical 25.00 35.00 50.00 75.00 100.00
Dental 15.00 20.00 25.00 30.00 35.00
Eye care 15.00 20.00 20.00 20.00
Prescriptions 25.00 25.00 25.00
Personal
Hair care 15.00 20.00 25.00 30.00 30.00
Subscriptions/dues 25.00 35.00 50.00 65.00 65.00
Hobbies/misc. 25.00 35.00 60.00 80.00 100.00
Other
Gifts
Entertainment 50.00 75.00 100.00 125.00 250.00
Vacation - 0 75.00 90.00 100.00 150.00
Debt
Credit cards
Student Loan 170.78 170.78 170.78 170.78 170.78
Other
Insurance/Risk Management (other than auto)
Health Insurance 100.00 150.00 200.00 250.00 300.00
Disability
Term life
Total discretionary spending 475.78 705.78 915.78 1,095.78 1,395.78
'Operating' Net 13.56 20.39 126.54 127.33 251.14
* Dave Ramsey, The Lampo Group, 1994
0 - 2 Years 2 - 4 Years 4 - 6 Years 6 - 8 Years 8 - 10 Years
Investments
Savings
$1,000 Emergency Fund
Pay-off all debt (except house)
$10 -15,000 cash savings (3-6 months living expense)
Invest 15% of household income in IRA's
Pay off home early
Wealth-Building
Investments
Financial
Certificates of Deposit
Bonds/Notes
Stocks
Mutual Funds
Derivatives
Other
Non-financial
Real Estate
Other
Retirement
Educational
Tax Planning
Estate (Business Succession) Planning
Charitable Giving
* Dave Ramsey, The Lampo Group, 1994
0 - 2 Years 2 - 4 Years 4 - 6 Years 6 - 8 Years 8 - 10 Years
Assets
Current
Cash
Money Market
Other
Investment
Financial
Certificates of Deposit
Bonds/Notes
Stocks
Mutual Funds
Derivatives
Other
Non-Financial
Real Estate
Other
Retirement
401K
IRA
Other
Household/Personal
Automobiles
Furnishings
Antiques/Valuables
Other
Liabilities
Current
Credit Cards
Auto Loans
Other Loans
Long-term
Mortgage
Student Loan
Equity
Net Worth

Income

Professional Services (Accounting, Finance, Consulting)
Billing Hours Template
Years 0- 2 Years 2-4 Years 4-6 Years 6-8 Years 8-10
Hourly rate 60 70 75 80 85 90 95 110 125 125 140 165 165 180 200
Hours worked per year
Hours per week 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55
Total annual hours 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750 2,750
Realization rate 0.6 0.65 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.8 0.85 0.9
Total billable hours 1,650 1,788 1,925 1,925 1,925 1,925 1,925 1,925 1,925 1,925 1,925 1,925 2,200 2,338 2,475
Percentage of 2,000 hour base 0.825 0.894 0.962 0.962 0.962 0.962 0.962 0.962 0.962 0.962 0.962 0.962 1.100 1.169 1.238
Total revenues generated 99,000 125,125 144,375 154,000 163,625 173,250 182,875 211,750 240,625 240,625 269,500 317,625 363,000 420,750 495,000
Revenue/salary ratio 3 3 3 3 3 3 2.5 2.5 2.5 1.9 1.9 1.9 1.7 1.7 1.7
Estimated potential salary 33,000 41,708 48,125 51,333 54,542 57,750 73,150 84,700 96,250 126,645 141,842 167,171 213,529 247,500 291,176
Overhead/profit contribution 66,000 83,417 96,250 102,667 109,083 115,500 109,725 127,050 144,375 113,980 127,658 150,454 149,471 173,250 203,824
Billing Hours Template
Years 4-6 Years 6-8 Years 8-10
Hourly rate 95 125 125 135 150 165 165 180 200
Hours worked per year
Hours per week 50 82.333 50 50 52.5 52.5 55 55 55
Total annual hours 2,500 4,117 2,500 2,500 2,625 2,625 2,750 2,750 2,750
Realization rate 0.7 0.7 0.7 0 0.7 0.7 0.7 0 0.8 0.85 0.9
Total billable hours 1,750 2,882 1,750 1,750 1,837 1,837 2,200 2,338 2,475
Percentage of 2,000 hour base 0.875 1.441 0.875 0.875 0.919 0.919 1.100 1.169 1.238
Total revenues generated 166,250 360,207 218,750 236,250 275,625 303,187 363,000 420,750 495,000
Revenue/salary ratio 4 3.25 3.25 3.25 3.25 1.9 1.7 1.7 1.7
Estimated potential salary 41,563 110,833 67,308 72,692 84,808 159,572 213,529 247,500 291,176
Overhead/profit contribution 124,688 249,374 151,442 163,558 190,817 143,615 149,471 173,250 203,824

CVP

2.0 points this sheet
Sell 100.000 90.00
Cost 70.000 70.000
GM 30.000 20.000
GM%
Cost +
Convert from Gross Margin Percentage to Cost + Percentage
GM% 0.1000 0.1500 0.2000 0.2500 0.3000 0.3333 0.3500 0.4000 0.4500 0.5000
Cost + %
Formula: 1/(1-GM%)
Convert from Cost + % to Gross Margin Percentage
Cost + %
GM %
Formula: (Cost + % -1)/Cost + %
a. Selling price & GM
b. Markup %
c. Revenue, COGS, GM template
Custom Standard Deluxe Total
Sell
Cost 300.00 400.00 500.00
GM (300.00) (400.00) (500.00)
Cost + 1.4 1.50 1.85
Markup %
Units 110 165 125 400.00
Revenues - 0
Costs - 0
GM - 0 - 0 - 0 - 0
If Thunder cuts its price by 8% on each item, how much of a volume increase will be needed to restore the
original profit level?
Volume % increase
Custom Standard Deluxe
Sell - 0 - 0 - 0
Cost 300.00 400.00 500.00
GM (300.00) (400.00) (500.00)
Cost +
Markup %
Units - 0
Revenues - 0
Costs - 0
GM - 0 - 0 - 0 - 0
If Thunder increases its price by 12% on each item, how much of a volume decrease could it sustain
before falling before the original profit level?
Volume % decrease
Custom Standard Deluxe
Sell - 0 - 0 - 0
Cost 300.00 400.00 500.00
GM (300.00) (400.00) (500.00)
Cost +
Markup %
Units - 0
Revenues - 0
Costs - 0
GM - 0 - 0 - 0 - 0

AMT

2.0 points this sheet
Please construct the amortization schedules below to zero-out a 20-month note
Loan 100,000 Loan 100,000
Annual rate 0.12 Annual rate 0.12
Monthly rate Monthly rate
Periods 20 Periods 20
PVANF PVANF
Excel pmt Excel pmt
Date Interest Payment Amort Balance Date Interest Payment Amort Balance
0 100,000.00 0 100,000.00
1 1
2 2
3 3
4 $30,000.00 80,000.00 4
5 5
6 6
7 7
8 $15,000.00 60,000.00 8
9 9
10 10
11 11
12 $10,000.00 40,000.00 12
13 13
14 14
15 15
16 $10,000.00 20,000.00 16
17 17
18 18
19 19
20 $10,000.00 - 0 20

Pension

2.0 points this sheet
Your client turned 35 today and has asked you to help plan for retirement beginning at age 65. The client's goal is to fund a 25 year
retirement at the inflation-adjusted equivalent of his current salary of 70,000 per year to be paid at the beginning of each year.
Inflation during all periods is estimated at 3% and you are recommending an investment account which
will pay 5.5% during the next 50 years.
a. How much must your client have invested in the retirement account on his 65th birthday to fund it?
Age 35
Retire 65
Current Salary 70,000
Inflation 3%
Retirement 20
Salary Equiv
Rate 5.5%
Real Rate
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14 C15 C16 C17 C18 C19 C20
Pmts
Present Value
b. The senior partner on the client account has asked you to complete four schedules below: the first two to confirm your
figure above, and the second set to establish how much must be deposited at the end of each of the next 30 years in order
to accumulate the necessary amount.
Schedule A Level Payments (1.0) Schedule B Increasing Payments
Date Interest Pmt Amt Balance Date Interest Pmt Amt Balance
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
Schedule C Payment at beginning of year Beg cash
FVANF BYP
Schedule C : Construct schedule as serial annuity growing at 3% and earning 5.5%
Date Interest Pmt Balance Payment at end of year.
0
1 Date Interest Pmt Balance
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
11 10
12 11
13 12
14 13
15 14
16 15
17 16
18 17
19 18
20 19
21 20
22 21
23 22
24 23
25 24
26 25
27 26
28 27
29 28
30 29
30

Serial

3.0 points this sheet
1. Your client has received an offer to buy his dental practice for 875,000.00 and the buyer has proposed the
payment schedule shown below, seven payments of 75,000.00 at the end of each of the next
seven years and a final payment of 350,000.00
The client believes that 8% is a reasonable return on his money over the next eight years.
a. How much has the buyer 'really' offered your client?
b. How much must the final payment be at the end of Year 8 in order for the client to get his price?
Discount rate 0.08
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
-875000 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 350,000.00
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
-875000 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00
$0.00
Balloon =
2. After conducting the analysis above, the buyer has proposed a a schedule of 'serial' payments increasing by 3%
each year. The buyer will have paid your client the inflation-adjusted value of 875,000 after eight years
Your client has given his permission for you to help the buyer construct a series of payments that will produce the
the necessary cash flows. The buyer believes that 9% is a reasonable rate of return over
the same time frame.
a. Please construct the serial annuity that will increase by 3% annually and yield 9%
return to total the inflation-adjusted equivalent of 875,000 in eight years
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
Proof Schedule
Date Interest Pmt Bal
1
2
3
4 - 0
5
875,000.00 6
7
8
Return rate: 9%
Inflation rate 3%
Real rate
FVANF
BYP
EYP
2. After conducting the analysis above, the buyer has proposed a a schedule of 'serial' payments increasing by 3%
each year. The buyer will have paid your client the inflation-adjusted value of 875,000 after eight years
Your client has given his permission for you to help the buyer construct a series of payments that will produce the
the necessary cash flows. The buyer believes that 9% is a reasonable rate of return over
the same time frame.
a. Please construct the serial annuity that will increase by -3% annually and yield 9%
return to total the inflation-adjusted equivalent of 875,000 in eight years
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
Date Interest Pmt Bal
1
2
3
4
5
875,000.00 6
7
8
Return rate: 0.09
Inflation rate -3%
Real rate
FVANF
BYP
EYP

FV

1.0 point this sheet
Future Value Computation:
Your client has been offered an investment opportunity in which he/she will invest a fixed amount
at the beginning of next year and receive returns of 8, 9, 11, 12, and 7 % at the end of each of the next
five years. What is the average rate of return on this investment opportunity? Please complet proof table
as well as demonstrate your answer by both algebraic and Excel proofs
Date Rate Interest Investment Balance
0
1
2
3
4
5
Algebra proof
Excel proof

TVM

Present Value of a Single Amount
Rate 0.06
1 2 3 4 5
C0 C1 C2 C3 C4 C5
Cash flows - 0 - 0 - 0 - 0 1,000.00
Pv Formula - 0 - 0 - 0 - 0 - 0
C/(1+r)^n
Pv Excel
Rate 0.06
1 2 3 4 5
C0 C1 C2 C3 C4 C5
CF Principal - 0 - 0 - 0 - 0 1,000.00
CF Pmt 50.00 50.00 50.00 50.00 50.00
Total Cash 50.00 50.00 50.00 50.00 1,050.00
Pv Formula - 0
C/(1+r)^n
Pv Excel
Rate 0.06 0.06 0.06 0.06 0.06 0.06
1 2 3 4 5
C0 C1 C2 C3 C4 C5
CF Principal - 0 - 0 - 0 - 0 1,000.00
CF Pmt 50.00 50.00 50.00 50.00 50.00
Total Cash 50.00 50.00 50.00 50.00 1,050.00
Pv Formula
Pv Formula
Pv Excel
Rate 0.06 0.06 0.06 0.06 0.06 0.06
1 2 3 4 5
C0 C1 C2 C3 C4 C5
CF Principal - 0 - 0 - 0 - 0 1,000.00
CF Pmt 50.00
Total Cash 50.00 50.00 50.00 50.00 1,050.00
Pv Formula
Pv Formula
Pv Excel
Present Value of Ordinary Annuity
Rate 0.06 0.06 0.06 0.06 0.06 0.06
1 2 3 4 5
C0 C1 C2 C3 C4 C5
Cash flows 80 80 80 80 80
Formula: single 0
Annuity factor
Pv formula
Pv Excel
Present Value of Annuity Due
Rate 0.06 0.06 0.06 0.06 0.06 0.06
0 1 2 3 4
C0 C1 C2 C3 C4 C5
Cash flows 80 80 80 80 80
Formula: single 0
Annuity factor ERROR:#VALUE!
Pv formula
Pv Excel
Short cut
Ordinary * (1+r)
Future Value of Single Amount
Rate 0.10 0.10 0.10 0.10 0.10 0.10
1 2 3 4 5
C0 C1 C2 C3 C4 C5
Fv formula 100
C * (1+r)^n
FV Excel
Future Value of an Ordinary Annuity
Rate 0.10 0.10 0.10 0.10 0.10
4 3 2 1
C0 C1 C2 C3 C4 C5
100 100 100 100
0
Annuity factor
(1+r)^n -1/r
FV formula
FV Excel
Future Value of an Annuity Due
Rate 0.10
5 4 3 2 1
C0 C1 C2 C3 C4 C5
100 100 100 100
0
Annuity factor
(1_r)^n -1/r * (1+r)
FV formula
FV Excel
Serial Annuity
4 3 2 1
1 2 3 4 5
C0 C1 C2 C3 C4 C5
- 0 - 0 - 0 - 0
- 0
180,000.00
Date Interest Pmt Bal
1
2
3
4
5
Need $180,000 in todays dollars to be accumulated in serial annuity. Discount rate: .07, Inflation rate: .03
Nominal rate 0.07
Inflation rate 0.03
Real rate: (1+ nominal)/(1+inflation)
FVAF (1.0388349^5-1)/.0388349
FYP (beg) Beginning value/FVAF
FYP (end) FYP * (1+ inflation)
Iron-off' $180,000 @ 7% with payments increasing at 3% annually over five years:
Level Amortization Schedule @ Real Rate
Date Interest Pmt Amt Bal
0 180,000.0000
1
2
3
4
5
Serial Schedule:
Date Interest Pmt Amt Bal Proof
0 180,000.0000
1 $0.00
2 $0.00
3 $0.00
4 $0.00
5 $0.00
$0.00
1. Your client has received an offer to buy his dental practice for 875,000.00 and the buyer has proposed the
payment schedule shown below, seven payments of 75,000.00 at the end of each of the next
seven years and a final payment of 350,000.00
The client believes that 8% is a reasonable return on his money over the next eight years.
a. How much has the buyer 'really' offered your client?
b. How much must the final payment be at the end of Year 8 in order for the client to get his price?
Discount rate 0.08
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
-875000 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 350,000.00
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
-875000 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 75,000.00 - 0
$0.00
Balloon =
2. After conducting the analysis above, the buyer has proposed a a schedule of 'serial' payments increasing by 3%
each year. The buyer will have paid your client the inflation-adjusted value of 875,000 after eight years
Your client has given his permission for you to help the buyer construct a series of payments that will produce the
the necessary cash flows. The buyer believes that 9% is a reasonable rate of return over
the same time frame.
a. Please construct the serial annuity that will increase by 3% annually and yield 9%
return to total the inflation-adjusted equivalent of 875,000 in eight years
1 2 3 4 5 6 7 8
C0 C1 C2 C3 C4 C5 C6 C7 C8
Date Interest Pmt Bal
1
2
3
4
5
875,000.00 6
7
8
Return rate: 0.09
Inflation rate 0.03
Real rate
FVANF
BYP
EYP
Level Amortization Schedule @ Real Rate
Date Interest Pmt Amt Bal
0 875,000.00
1
2
3
4
5
6
7
8
Serial Schedule
Date Interest Pmt Amt Bal
0 875,000.00
1
2
3
4
5
6
7
8
Pension Liability Problem:
Assume you have turned 25 years old today and expect to retire when you reach age 60. In retirement, you wish to have an income of $70,000 per year (adjusted for inflation)
for a period of 15 years. Since inflation is expected to average 3% over the next 35 years, you must adjust your retirement income accordingly. You wish to receive
your retirement income at the beginning of each year, beginning on your 60th birthday. Additionally, you wish to accumulate the sum of $1,000,000 at the end of your 15 year
retirement period to pass along to your heirs. The discount rate is 9%
0 1 2 3 4 5 6 7 8 9 10
Increasing pmts
- 0
Level pmts
- 0
1. Inflation-adjusted equivalent of $70,000 in 35 years @ 3%:
2. Investment required @ age 65 to fund the 15 year stream of payments at the
beginning of each year @ 9%:
3. Amount required to fund a $1,000,000 residual at end of 15 years:
4. Total amount needed @ age 65:
5. Present value of pension liability:
6. Inflation Effect: ANF
7. PV annuity factor due
PV Proof PV Proof
Increasing Payments: Annuity Due Increasing Payments: Ordinary Annuity
Date Interest Payment Amortization Balance Date Interest Payment Amortization Balance
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
Level Payments: Annuity Due Level Payments: Ordinary Annuity
Date Interest Payment Amortization Balance Date Interest Payment Amortization Balance
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15
Annual Payment Proof
FVAF
Pmt
Pmt Excel
Date Interest Payment Balance
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Example adapted from: Contemporary Financial Management, Moyer, McGuigan, Kretlow, Thomson, 2005
The Thomas family plans to purchase a new house in three years for $250,000, at which time they will take out a traditional 30-year mortgage.
The mortgage payment may not exceed 25% of family income, which is expected to be $65,000 at the time of purchase. Mortgage rates are expected
to be 9%. Since the mortgate alone will not provide sufficient cash for purchase, a down payment will be required. The Thomas's currently have a bank account
which pays 6% compounded quarterly that has grown to $15,000, and plan to make quarterly deposits at the end of each quarter to date of purchase to
make up the difference. How much must each deposit be?
Purchase price 250,000.00 Interest Deposit Balance
Financed amount 1
25% of projected annual income 2
Maximum monthly payment amount 3
Pv annuity factor: 360 months @ 9% 4
5
Shortfall: 6
7
Current savings 15,000.00 8
Future value of current savings @ 6% 9
compounded quarterly 10
11
Shortfall to be covered by future savings 12
Quarterly deposit required:
Fv annuity factor: 12 periods @ 1.5%
Example adapted from: Lasher, William, R., Practical Financial Management, Thomson, 2005
Exeter Inc. has $75,000 invested in securities which earn 16% compounded quarterly. The company is developing a new product for launch
in (2) years for which it will need $500,000. The money currently invested will can be used for the launch. Exeter's bank has offered an account which
will pay 12% compounded monthly. How much must Exeter deposit each month to insure sufficient funds for the launch?
Funds needed in two years: 500,000.00 Interest Deposit Balance
1
Current investment 75,000.00 2
Accumulation in two years: 3
4
Difference: 5
6
Monthly deposit required to accumulate shortfall 7
invested @ 12% compounded monthly: 8
FV annuity factor 24 periods @ 1%: 9
Excel formula 10
11
Example taken from: Lasher, William, R., Practical Financial Management, Thomson, 2005 12
13
14
15
16
17
18
19
20
21
22
23
24