Please see the attachemnt and make sure you KNOW it.

profilefaiala2
finance.xlsx
Home>Business & Finance homework help>Please see the attachemnt and make sure you KNOW it.

TVM

4.0 Points this tab
A. Time Value of Money
1. Your client booked a $1 million receivable on the sale of a small business to be paid in $60,000 installments at the beginning of years one through three
no payments in Years four and five, followed by $75,000 payments at the end of Years six through nine, followed by
a balloon payment at the end of Year ten to be determined by you.
a. Please determine the amount of the balloon payment: (1.0)
Return rate 0.075
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
60,000 60,000 60,000 75,000 75,000 75,000 75,000
b. Please construct the amortization schedule below to zero-out at the end of Year 10. (3.0)
Date Interest Pmt Amt Balance
0
1
2
3
4
5
6
7
8
9
10

Balloon Pmt

3.0 Points
Amortization
1. Please construct an amortization schedule which zero's out a $100,000 note over 25 years @ 4%
Arrange your payment schedule so that the balance falls by 20% every five years and that this is
accomplished through a balloon payments as specified below
Rate 0.04
Date Interest Pmt Amt Balance
0 100,000.00
1
2
3
4
5 $55,000.00 80,000.00
6
7
8
9
10 $25,000.00 60,000.00
11
12
13
14
15 $10,000.00 40,000.00
16
17
18
19
20 $10,000.00 20,000.00
21
22
23
24
25 $10,000.00 - 0

Serial Annuity

5.0 Points
Serial Annuity
Please construct an amortization schedule cleaning off a $40 monthly payment over 12 months at rates as show below:
Inflation
Nominal rate 0.07
Inflation rate 0.02 Level Payments
Real rate
1 2 3 4 5 6 7 8 9 10 11 12
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
Cash flows -300 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00
Formula: single
NPV
Date Interest Pmt Amt Balance
0
1
2
3
4
5
6
7
8
9
10
11
12
Nominal rate 0.07
Inflation rate 0.02
Rising Payments
1 2 3 4 5 6 7 8 9 10 11 12
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
Cash flows -300
Formula: single
NPV
IRR
Date Interest Pmt Amt Balance
0
1
2
3
4
5
6
7
8
9
10
11
12
Deflation
Nominal rate 0.07
Deflation rate 0.02
Real rate
Level Payments
1 2 3 4 5 6 7 8 9 10 11 12
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
Cash flows -300 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00
Formula: single
NPV
IRR
Date Interest Pmt Amt Balance
0
1
2
3
4
5
6
7
8
9
10
11
12
Nominal rate 0.06
0 -0.03
Real rate
Falling Payments
1 2 3 4 5 6 7 8 9 10 11 12
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
Cash flows -300
Formula: single
NPV
IRR
Date Interest Pmt Amt Balance
0
1
2
3
4
5
6
7
8
9
10
11
12

LP

5.0 Points
Use Solver to determine the optimal production schedule based on data below:
Note Cost of Goods Sold Discount Scheudle.
Demand (U's) 50 21 12 15 21 119
Bal Harbour Century Greenbrier Sun Crest Palms Total Constraints
Production(U's) 1 1 1 1 1 5.00000 <= 120
Selling price 1,280.00 1,125.00 1,695.00 950.00 850.00
COGS
Gross margin 1,280.00 1,125.00 1,695.00 950.00 850.00
Vinyl sq ft. 905.00 1,082.00 1,285.00 868.00 775.00 <= 75,000
Labor hrs/unit 4 4.6 5.5 3.7 3.25 <= 2,500
Machine hours 1.3333333333 1.5333333333 1.8333333333 1.2333333333 1.0833333333 <= 600
Contract 1 0 0 0 0 >= 40
Contract 1 0 0 0 0 <= 75
Contract 0 1 0 0 0 >= 12
Contract 0 1 0 0 0 <= 30
Contract 0 0 1 0 0 >= 8
Contract 0 0 1 0 0 <= 16
Contract 0 0 0 1 0 >= 11
Contract 0 0 0 1 0 <= 20
Contract 0 0 0 0 1 >= 5
Contract 0 0 0 0 1 <= 8
Cost of Goods Sold Volume Discount Schedule
Unit Volume Cost of Goods Sold Percentage
20 0.7 0.7 0.6 0.72 0.75
40 0.65 0.65 0.55 0.67 0.7
55 0.6 0.6 0.48 0.6 0.62

NPV Cash

NPV/IRR (4.0 Points)
On January 2, 2014, Thunder Corporation's board of directors considered the acquisition of a new line of equipment for its new Tower Division.
Details surrounding the proposed investment are shown below, opportunity cost is 14 % compounded continuously.
Depreciate SL to zero salvage over seven years. Sell at end of eighth year for 18,000
Tower's board has asked you to evaluate NPV and IRR on the project.
Cash 0
Accounts Receivable - 0 1,400 2,700 3,550 4,150 2,750 1,975 1,050 - 0
Inventory - 0 4,500 12,900 16,500 19,000 12,500 7,500 2,350 - 0
Equipment 65,000 65,000 65,000 65,000 65,000 65,000 65,000 65,000 - 0
Accumulated Depreciation*
Net Equipment 65,000 65,000 65,000 65,000 65,000 65,000 65,000 65,000 - 0
Total Assets 65,000 70,900.00 80,600.00 85,050.00 88,150.00 80,250.00 74,475.00 68,400.00 - 0
Accounts Pay - 0 1,050 1,700 3,575 2,900 1,200 850 475 200
Paid in capital 67,100 67,100 70,100 70,100 70,100 70,100 70,100 70,100 70,100
Retained earnings (2,100)
Tot Liab & Eq 65,000.00 68,150.00 71,800.00 73,675.00 73,000.00 71,300.00 70,950.00 70,575.00 70,300.00
Period 0 1 2 3 4 5 6 7 8
Capital Investment/salvage 65,000 18,000
Sales - 0 25,850 38,775 59,400 74,250 51,000 43,500 22,000
Cost of Goods Sold 14,734 17,449 26,730 33,413 25,500 23,925 13,200
Other Costs 3,500 2,500 2,750 3,000 3,500 2,900 2,400 1,675
Depreciation 9,286 9,286 9,286 9,286 9,286 9,286 9,285.71
Total costs 26,520 29,484 39,016 46,198 37,686 35,611 24,161
EBIT (3,500) (670) 9,291 20,384 28,052 13,314 7,889 (2,161) (47,000)
Tax @ 40% 0.4 (1,400) (268) 3,716 8,154 11,221 5,326 3,156 (864)
After-tax Profit (2,100) (402) 5,574 12,231 16,831 7,989 4,734 (1,296)
Capital Disposal
Change in Net Working Capital
Net Cash Flow (67,100.00)
PV Cash flows - 0
NPV
IRR
Annual rate 1.1502736905
0.1502736905
IRR - 0
EAC
PVANF - 0
Straight line Depreciation Schedule MTBF 8
To zero salvage λ 0.125
Year BV Depr
0 65,000.00 0 0 1
1 55,714.29 9,285.71 1 0.1175030974 0.8824969026
2 46,428.57 9,285.71 2 0.2211992169 0.7788007831
3 37,142.86 9,285.71 3 0.3127107212 0.6872892788
4 27,857.14 9,285.71 4 0.3934693403 0.6065306597
5 18,571.43 9,285.71 5 0.4647385715 0.5352614285
6 9,285.71 9,285.71 6 0.5276334473 0.4723665527
7 (0.00) 9,285.71 7 0.5831379803 0.4168620197
65,000.00 8 0.6321205588 0.3678794412
2 0.00 (0.00)
3 0.00 (0.00)
4 0.00 (0.00)
5 0.00 (0.00)
6 0.00 (0.00)
7 0.00 (0.00)
65,000.00

Pension

4.0 Points
2. 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 the 20 year retirement is estimated at 3% and you are recommending an investment account which
will pay 7% 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 7%
Real Rate
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14 C15 C16 C17 C18 C19 C20 C21 C22 C23 C24 C25
b. The senior partner on the client account has asked you to complete three schedules below: the first two to confirm your
figure above, and the second 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 (1.0)
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
20 20
21 21
22 22
23 23
24 24
Schedule C Payment at beginning of year (1.0) Beg cash - 0 - 0
FVANF 101.0730413664 BYP - 0 1
Schedule C : Construct schedule as serial annuity growing at 3% and earning 7% (2.0)
Date Interest Pmt Balance FVANF 1
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

CVP

3.0 Points
CVP
Please set up Cost/Volume Profit tables below.
Simulate demand (use average of 10 observations), interest rate
Selling Price 100
Variable Cost 75
Gross Margin 25
Forecast Acct Br Even Target profit Price Sensitivity
Units 1,100 1,100
SP
Revenues VC
Variable cost Unit CM
Gross Margin - 0 - 0 - 0 - 0
Marketing Exp 800 800 800 800
Administrative Exp 1,400 1,400 1,400 1,400
Depreciation 600 600 600 600
Earnings Before Interest & Tax (2,800) (2,800) (2,800) (2,800)
Interest Expense 0.10
Earnings Before Tax (2,800) (2,800) 25,000 25,000
Tax 0.40
Net (2,800) (2,800) 25,000 25,000
Target profit 15,000 15,000
Note Principal 80,000
Mean = 100
Std Dev = 2.00
Z value Value Probability Random Table Forecast
(6.00)
(5.75)
(5.50)
(5.25)
(5.00)
(4.75)
(4.50)
(4.25)
(4.00)
(3.75)
(3.50)
(3.25)
(3.00)
(2.75)
(2.50)
(2.25)
(2.00)
(1.75)
(1.50)
(1.25)
(1.00)
(0.75)
(0.50)
(0.25)
- 0
0.25
0.50
0.75
1.00
1.25
1.50
1.75
2.00
2.25
2.50
2.75
3.00
3.25
3.50
3.75
4.00
4.25
4.50
4.75
5.00
5.25
5.50
5.75
6.00
Rate Probability Cumulative Rand Table Forecast
0.01 0.05
0.02 0.08
0.03 0.09
0.04 0.11
0.05 0.13
0.06 0.15
0.07 0.13
0.08 0.12
0.09 0.10
0.10 0.07