Template for the Module 1_Assignment 3
Firm’s Cost of Capital
Year
Annual Cash Flows
0
1
2
3
4
5
Part 1: Calculate the Net Present Value and Explain Results
YouTube Link on How to do NPV Calculations in Excel below. Copy and paste into your browser
PV of Savings
Initial Investment
Net Present Value
hover over cell to see comments
–
hint:
(NPV)
Explain results below
Part 2: Calculate the Internal Rate of Return and Explain Results
YouTube Link on How to do IRR Calculations in Excel below. Copy and paste into your browser
Internal Rate of Return
(IRR)
Explain results below
hint:
Part 3: Calculate the Payback Period and Explain Results
YouTube Link on How to do Payback Calculations in Excel below. Copy and paste into your browser
Annual Cash Flows
Payback Period
hint:
Balance Forward
Explain results below
hint:
Part 4: Calculate the Discounted Payback Period and Explain Results
YouTube Link on How to do Discount Payback Calculations in Excel below. Copy and paste into your browser
Annual Cash Flows
Firm’s Cost of Capital
8%
–
–
–
–
Explain results below
hint:
Part 5: Analyze Results and Make Recommendations
References:
Module 2_Assignment 2 (Template)
Question 1: What is the Beta Coefficient for Concordia?
Table-1:
Plant
Beta Coefficient
% of Concordia’s Income
South Town
0,85
55%
North Town
0,90
20%
East Town
1,25
15%
West Town
1,60
10%
Question 1: Beta Coefficient for Concordia
Explain your answer:
← hint
Solution
Question 2: What is the Required Rate of Return for Concordia?
Risk-free Rate
Market Risk
of Interest
Premium (RP)
(RF)
3,00%
4,00%
Explain your answer:
← hint
← hint
← hint
Concordia’s
Concordia Composit Beta (B) Required Rate of
Return (kreq)
← hint
Question 3a: What is the equation for the Security Market Line (SML)?
← hint
3b: Graph the equation
3c: Explain what the SML is telling you and the implications for the firm
← hint
Question 4: Based on the information provided in the case, if the new plant is
expected to return 12%, should Concordia make the investment? Explain your
answer and justify your calculations.
← hint
References:
Module 3_Assignment 2 Template
Cost of Capital
Year
0
1
2
3
4
5
PV of CF’s
NPV
IRR
$
$
$
$
$
$
8,0%
Project A
(8.000,00)
2.300,00
2.300,00
2.300,00
2.300,00
2.300,00
$
$
$
$
$
$
8,0%
Project A
(10.000,00)
3.000,00
3.000,00
3.000,00
3.000,00
3.000,00
$
$
$
$
$
$
8,0%
Project A
(9.000,00)
2.800,00
2.800,00
2.800,00
2.800,00
2.800,00
$
$
$
$
$
$
8,0%
Project A
(8.500,00) Initial Cost
2.100,00
2.100,00
Positive
2.100,00
Cash Flows
2.100,00
2.100,00
← Hint
Part 1a: Find NPV for each project
Project A
Project A
NPV
$0
$0
Project A
Project A
← Hint
$0
$0
Part 1b: Explain your findings
Part 2a: Calculate the IRR for each project
Project A
Project A
IRR
0,0%
0,0%
Part 2b: Explain your findings
Part 3: Which projects should be selcect and why?
← Hint
Project A
0,0%
Project A
0,0%
Assume for Parts 4 , 5 and 6 that if the projects meet the NPV and IRR test, Project A will be
implimented in Year 1, Project B will be implement in Year 2, Project C will be implemented in Year 3
and Project D will be implemented in Year 4.
Part 4: What will the total dividends and the external financing be if the current dividend per share
is maintained?
Year
After Tax Income
1
2
3
4
$
$
$
$
Dividend
Capital
Expenditure
External Funding
Required
6.000,00
8.000,00
5.000,00
7.000,00
Additional Funding Required
← Hints
← Hint
Part 5: What will the total dividends and the external financing be if the dividend payout ratio is
50% of After-Tax Income?
Year
After Tax Income
1
2
3
4
$
$
$
$
Dividend
Capital
Expenditure
External Funding
Required
6.000,00
8.000,00
5.000,00
7.000,00
Additional Funding Required
← Hints
← Hint
Part 6: What will the total dividends and the external financing be if the company uses a residual
dividend payout policy?
Year
After Tax Income
1
2
3
4
$
$
$
$
6.000,00
8.000,00
5.000,00
7.000,00
Dividend
Capital
Expenditure
External Funding
Required
← Hints
Additional Funding Required
← Hint
Part 7: Under which dividend policy would the external funding requirement be minimized? Be
sure to justify your answer.
References:
Module 4_Assignment 2 Template
Question 1a: What is the annual cost, before any tax considerations of the lease options?
Question 1b: Are there any tax considerations and if so, what is the after-tax annual cost of the lease?
annual lease
payments
year
net lease
payment
after-tax
annual tax
savings
← Hints
1
2
3
4
Total
← Hint
Question 2: What is the total cost of leasing the truck today?
Net lease
payment
after-tax
Cost of Capital
Number of
lease
payments
Total cost of
leasing truck
today
← Hints
← Hint
Question 3: What are the annual cash flows if the truck is purchased with debt financing?
Depreciable
Value of Truck
Year
Depreciation Depreciation
Rate using Write-off per
MACRS
Year
Annual Tax
Savings
← Hints
1
2
3
4
Total Tax-Savings
Year
Annual Cost of Annual TaxTruck
Savings
← Hint
Annual Cash
Outflow
← Hints
1
2
3
4
Cost to Purchase less Tax-Savings
Question 4: What is the cost of purchasing the truck with debt financing today?
Cost of Truck
less: TaxSavings
Net Cost of
Truck
Question 5: Make a recommendation to your boss as to whether the company should buy or lease the truck. Justify
your recommendation
References:
Module 5, Assignment 1
Template
Part 1: Collections and Payments
JAN
SALES (given)
162.000
%
FEB
168.000
MARCH
324.000
Collections
Month of Sale
Month after Sale
2nd Month after Sale
Total Recepts
Purchase of Labor and
Materials
Purchase of Labor and
Materials Payment
Part 2: Net Cash Flow for the Month
Total Receipts:
Payments:
Labor and Materials (from
above)
Administrative Salaries
Lease Payments
Income taxes
Miscellaneous Expenses
Boat Dock
Total Payments
Net Cash Surplus (Deficient)
Part 3: Surplus or Loan
Requirements
Cash at beginning of the month
Cumulative Cash
Less: Minimum Cash
Surplus/Loan Requirements
MARCH
APRIL
485.000
MAY
648.000
JUNE
325.000
JULY
325.000
AUG
80.000
SEPT
162.000
APRIL
MAY
JUNE
JULY
AUG
SEPT
Purchase answer to see full
attachment
Firm’s Cost of Capital
Year
Annual Cash Flows
0
1
2
3
4
5
Part 1: Calculate the Net Present Value and Explain Results
YouTube Link on How to do NPV Calculations in Excel below. Copy and paste into your browser
PV of Savings
Initial Investment
Net Present Value
hover over cell to see comments
–
hint:
(NPV)
Explain results below
Part 2: Calculate the Internal Rate of Return and Explain Results
YouTube Link on How to do IRR Calculations in Excel below. Copy and paste into your browser
Internal Rate of Return
(IRR)
Explain results below
hint:
Part 3: Calculate the Payback Period and Explain Results
YouTube Link on How to do Payback Calculations in Excel below. Copy and paste into your browser
Annual Cash Flows
Payback Period
hint:
Balance Forward
Explain results below
hint:
Part 4: Calculate the Discounted Payback Period and Explain Results
YouTube Link on How to do Discount Payback Calculations in Excel below. Copy and paste into your browser
Annual Cash Flows
Firm’s Cost of Capital
8%
–
–
–
–
Explain results below
hint:
Part 5: Analyze Results and Make Recommendations
References:
Module 2_Assignment 2 (Template)
Question 1: What is the Beta Coefficient for Concordia?
Table-1:
Plant
Beta Coefficient
% of Concordia’s Income
South Town
0,85
55%
North Town
0,90
20%
East Town
1,25
15%
West Town
1,60
10%
Question 1: Beta Coefficient for Concordia
Explain your answer:
← hint
Solution
Question 2: What is the Required Rate of Return for Concordia?
Risk-free Rate
Market Risk
of Interest
Premium (RP)
(RF)
3,00%
4,00%
Explain your answer:
← hint
← hint
← hint
Concordia’s
Concordia Composit Beta (B) Required Rate of
Return (kreq)
← hint
Question 3a: What is the equation for the Security Market Line (SML)?
← hint
3b: Graph the equation
3c: Explain what the SML is telling you and the implications for the firm
← hint
Question 4: Based on the information provided in the case, if the new plant is
expected to return 12%, should Concordia make the investment? Explain your
answer and justify your calculations.
← hint
References:
Module 3_Assignment 2 Template
Cost of Capital
Year
0
1
2
3
4
5
PV of CF’s
NPV
IRR
$
$
$
$
$
$
8,0%
Project A
(8.000,00)
2.300,00
2.300,00
2.300,00
2.300,00
2.300,00
$
$
$
$
$
$
8,0%
Project A
(10.000,00)
3.000,00
3.000,00
3.000,00
3.000,00
3.000,00
$
$
$
$
$
$
8,0%
Project A
(9.000,00)
2.800,00
2.800,00
2.800,00
2.800,00
2.800,00
$
$
$
$
$
$
8,0%
Project A
(8.500,00) Initial Cost
2.100,00
2.100,00
Positive
2.100,00
Cash Flows
2.100,00
2.100,00
← Hint
Part 1a: Find NPV for each project
Project A
Project A
NPV
$0
$0
Project A
Project A
← Hint
$0
$0
Part 1b: Explain your findings
Part 2a: Calculate the IRR for each project
Project A
Project A
IRR
0,0%
0,0%
Part 2b: Explain your findings
Part 3: Which projects should be selcect and why?
← Hint
Project A
0,0%
Project A
0,0%
Assume for Parts 4 , 5 and 6 that if the projects meet the NPV and IRR test, Project A will be
implimented in Year 1, Project B will be implement in Year 2, Project C will be implemented in Year 3
and Project D will be implemented in Year 4.
Part 4: What will the total dividends and the external financing be if the current dividend per share
is maintained?
Year
After Tax Income
1
2
3
4
$
$
$
$
Dividend
Capital
Expenditure
External Funding
Required
6.000,00
8.000,00
5.000,00
7.000,00
Additional Funding Required
← Hints
← Hint
Part 5: What will the total dividends and the external financing be if the dividend payout ratio is
50% of After-Tax Income?
Year
After Tax Income
1
2
3
4
$
$
$
$
Dividend
Capital
Expenditure
External Funding
Required
6.000,00
8.000,00
5.000,00
7.000,00
Additional Funding Required
← Hints
← Hint
Part 6: What will the total dividends and the external financing be if the company uses a residual
dividend payout policy?
Year
After Tax Income
1
2
3
4
$
$
$
$
6.000,00
8.000,00
5.000,00
7.000,00
Dividend
Capital
Expenditure
External Funding
Required
← Hints
Additional Funding Required
← Hint
Part 7: Under which dividend policy would the external funding requirement be minimized? Be
sure to justify your answer.
References:
Module 4_Assignment 2 Template
Question 1a: What is the annual cost, before any tax considerations of the lease options?
Question 1b: Are there any tax considerations and if so, what is the after-tax annual cost of the lease?
annual lease
payments
year
net lease
payment
after-tax
annual tax
savings
← Hints
1
2
3
4
Total
← Hint
Question 2: What is the total cost of leasing the truck today?
Net lease
payment
after-tax
Cost of Capital
Number of
lease
payments
Total cost of
leasing truck
today
← Hints
← Hint
Question 3: What are the annual cash flows if the truck is purchased with debt financing?
Depreciable
Value of Truck
Year
Depreciation Depreciation
Rate using Write-off per
MACRS
Year
Annual Tax
Savings
← Hints
1
2
3
4
Total Tax-Savings
Year
Annual Cost of Annual TaxTruck
Savings
← Hint
Annual Cash
Outflow
← Hints
1
2
3
4
Cost to Purchase less Tax-Savings
Question 4: What is the cost of purchasing the truck with debt financing today?
Cost of Truck
less: TaxSavings
Net Cost of
Truck
Question 5: Make a recommendation to your boss as to whether the company should buy or lease the truck. Justify
your recommendation
References:
Module 5, Assignment 1
Template
Part 1: Collections and Payments
JAN
SALES (given)
162.000
%
FEB
168.000
MARCH
324.000
Collections
Month of Sale
Month after Sale
2nd Month after Sale
Total Recepts
Purchase of Labor and
Materials
Purchase of Labor and
Materials Payment
Part 2: Net Cash Flow for the Month
Total Receipts:
Payments:
Labor and Materials (from
above)
Administrative Salaries
Lease Payments
Income taxes
Miscellaneous Expenses
Boat Dock
Total Payments
Net Cash Surplus (Deficient)
Part 3: Surplus or Loan
Requirements
Cash at beginning of the month
Cumulative Cash
Less: Minimum Cash
Surplus/Loan Requirements
MARCH
APRIL
485.000
MAY
648.000
JUNE
325.000
JULY
325.000
AUG
80.000
SEPT
162.000
APRIL
MAY
JUNE
JULY
AUG
SEPT
Purchase answer to see full
attachment
