Project Steps
FNCE 390
Project Steps
I Background
You have now been asked to continue with the financing plans around POPLAR Ltd.'s.'s "Growth Plans".
You had been waiting for some additional information from a fellow worker before proceeding with your
financial decisions around a new investments. You have also been advised that the maximum amount of
new investment funds - a combination of debt and equity is a total of $18,000,000.
Business conditions are expected to weaken slightly over the next year with higher interest rates and tax
rates expected to increase over the next two to three years. Market opportunities are expected to remain
for next three to five years with competitor mergers expected to increase to maintain market share.
Overall, the team continues to believe the time is right for growing the business and positioning it for sale
or maybe even considering going public in the future.
To complete the financial analysis, started by a junior member of the firm who a
uptly quit you are to do:
STEPS TO DO
A) Locate the Following Project Files:
Project Financials - 2B
- contains worksheets for Pro forma Balance Sheet
- Contains worksheets for Pro forma Income Statement
- contains worksheets to calculate WACC
- contains worksheets to calculate key ratios
B) Using the information provided and the sequence of steps outlined below, develop a proposal which
is financially responsible for the company and for your firm where the $18,000,000 is invested in some
combination of debt and/or equity.
NOTE:
As you are picking up someone else's work, you have color coded the steps to match to the areas within the file
you need to work on.
C STEPS:
1 Opening Financial Information
Using Board approved financial results for Year 1, add the Balance Sheet amounts and Income Statement
amounts in the co
ect accounts - they are color coded:
Note - cu
ent portion of debt = Term loan = $ 240,000 Mortgage = $ 115,000
To address the bank account overdraft a $3,000,000 share capital injection was made by the investment firm.
2 Project Financials - this file
a Develop Combined Results - Pro from a Balance Sheet
Using the appropriate column on Financing Balance Sheet spreadsheet add relevant accounting figures from the
accepted Project Equipment and Project Acquisition Tabs. As an example the capital equipment tab indicates that
additional investments in Accounts Receivable will occur. Place that value in the column for Equipment Project
on the Accounts Receivable line.
To balance - assume the net purchase amounts are addressed through the Venture Short Term loan.
The Mortgage assumed in the acquisition will just be added to the existing mortgage line with the same remaining term
Ensure that your Balance Sheet is Balanced - Total Assets = Total Liabilities and Equity
Develop Combined Results - Pro from a Income Statement
b Using the appropriate column on Financing Income Statement spreadsheet add relevant accounting figures from the
Project Equipment and Project Acquisition Tabs from Phase 2A. As an example the capital equipment tab indicates
that additional operating savings have been generated - reduce administrative expenses.
Unexpectedly a one time restructuring cost was incu
ed - $900,000 - impacts Administrative costs fo
business acquisition
For WACC - and future years tax rate is now 25% due to tax planning efforts
DO NOT change interest or income tax figures in file.
c Next, the three of you have met to decide what roles each of you will play in the new organization.
The positions have been set - but now you need to address compensation. You have two options to consider-
Cash compensation (Salary + bonus+ perks) and share issuance. Determine the compensation package
for each positon - the share price is the cu
ent market value - determined below - post split decision
TOTAL COMPENSATION should not exceed $870,000
Salary + Shares# Shares Value Compensation
President $ - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
VP Finance - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
VP Operations - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
VP Business - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
$ - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
The compensation expense is reflected 50% in admin and 50% in selling expenses
Admin ERROR:#DIV/0!
Selling ERROR:#DIV/0!
d Share Price Review - Business and Competitive Conditions impact Multiple
Management decides to complete a stock split to reduce price per share to $4.17 and venture of $3.00
Cu
ent market value of shares ERROR:#DIV/0!
EBITDA - Combined
Income before income taxes $ - 0
Add: Amortization - 0
Add: Interest - 0
$ - 0
Multiplier 7
Cu
ent market value $ - 0
Issued and outstanding shares - 0 Find in Key Input information - Phase 2A
Proposed Stock Split - 0 Select a figure which sets line 95 at venture capital target range
Issued and outstanding shares revised - 0
Market Value per share ERROR:#DIV/0!
Venture Capital Discount 28%
Venture Capital Share Price ERROR:#DIV/0!
Meets Criteria of
Share capital impact % Ownership 52% or greate
Issued an outstanding - above - 0
Issued an outstanding - stock compensation - 0
Issued and outstanding - 0 ERROR:#DIV/0! ERROR:#DIV/0!
New Issue
Venture Capital Value From above ERROR:#DIV/0!
New Shares Issued - 0 ERROR:#DIV/0! Issue shares
Total Shares - 0 ERROR:#DIV/0!
Share capital raised ERROR:#DIV/0!
If believed necessary - submit file to supervisor for review.
Weighted Average Cost of Capital - TAB
1) Review the weighted average cost of capital calculations for the Pro-forma position
Your objective is to refinance keeping long term target WACC as a goal - while balancing other objectives.
The WACC has been set up to calculate automatically - you need to verify if calculating co
ectly.
Refinancing Options
2) You are now prepared to consider your refinancing options to select one that works best for you
The infusion uses your decisions above and must total $ 18,000,000
Investment Mix
Refinancing Debt ERROR:#DIV/0!
Non-cash Executive Stock compensation ERROR:#DIV/0!
Share Capital ERROR:#DIV/0!
Total investment = $15,000,000 ERROR:#DIV/0!
Your task is to now allocate the $15,000,000 betweem the loans to address WACC and debt as a % of total
assets. Using the financing ratios tab - you can find the allowable maximums of the various loans and
increase balances of lowest debt first and paydown lower debt.
Application of investment - loan changes Funds Allocation
Venture loan (repay by negative figure) - 0 Check limits below
Mortgage (repay negative - add - positive) - 0 Check limits below
Term loan (repay negative - add - positive) - 0 Check limits below
Refinance term loan (repay negative - add postiive) - 0 Check limits below
Cash (positive figure) - 0 18,000,000
Application = $15,000,000 $ - 0 sum = (18,000,000) as refinancing debt
The mix of equity and debt must meet all criteria below and strive to maximize net income
Management Parameters Actual Target
Cu
ent Ratio ERROR:#DIV/0! 1.20> ERROR:#DIV/0!
Debt as % of Total Assets ERROR:#DIV/0! 45.00% ERROR:#DIV/0!
75% of A/R
Venture Loan $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Maximum
Mortgage $ - 0 - 0 - 0 Good
Maximum
Term loan 35% of Equipment
$ - 0 $ - 0 $ - 0 Good
Maximum
Refinance Loan ERROR:#DIV/0! 10,000,000 ERROR:#DIV/0!
Range
Hi Low
WACC - Ending ERROR:#DIV/0! 16.50% 14.50%
6) Balancing Figure
Once you have determined your optimal mix above, you will need to adjust cash to complete the Balance Sheet
The required balancing figure will be in cell F56
First - enter the remaining balance of the short term venture loan by entering up to the balance outstanding
Second - if still a balance in cell F56 and ventru loan = 0 - apply difference to Cash (reverse sign)
To complete - check the figure showing at the bottom of the Earnings Impact "Out of Balance" = $0
If ratios above all yes - you are complete and move on to Memorandum
7) Memorandum
Prepare a Memorandum which shows the following:
Investment Mix of $18,000,000 - debt, stock compensation, share issuance
Stock split decision
New shares issued and total proposed issued and outstanding by source
Founders ownership position and percentage of ownership compared against minimum percentage
Total long term debt outstanding versus limit by loan type and total unsued capacity of debt
Debt as a percentage of total assets and comparison against target and maximum percentage
Cu
ent Ratio and comparison against benchmark
Return on Asset
Return on Equity
Net income as a % of Revenue
WACC versus target
Recommendation on operational focus based on Turnover Ratios
Recommendation to proceed or not based on key criteria above
Concerns about future issue of shares
If you needed an additional $10 million what would you forecast as coming from earnings / debt / shares?
8) NOTE:
WACC will self calculate - you should test to ensure calculations are co
ect
Spreadsheet Ratios will self calculate - test for co
ect calculations
Only enter figures with color codes
Financing Spreadsheet BS
POPLAR LTD.
Balance Sheet Opening Cash Equipment Acquisition Combined Results Earnings Impact Refinancing Pro-forma
January 31, XXXXXXXXXX Balance Sheet Injection Project Project Decision Balance Sheet
Assets
Cu
ent assets
Cash $ - 0 $ - 0 $ - 0 - 0 $ - 0 $ - 0
Accounts receivable - 0 - 0 $ - 0 - 0 ERROR:#DIV/0! - 0 ERROR:#DIV/0!
Inventory - 0 - 0 - 0 - 0 ERROR:#DIV/0! - 0 ERROR:#DIV/0!
Prepaid expenses - 0 - 0 - 0 - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! - 0 ERROR:#DIV/0!
Property, plant and equipment
Land - 0 - 0 - 0 - 0 - 0 - 0
Buildings - 0 - 0 - 0 - 0 - 0 - 0
Equipment - 0 - 0 - 0 - 0 - 0 - 0 - 0
Vehicles - 0 - 0 - 0 - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Less: Accumulated amortization - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Trademarks, patents and goodwill - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
$ - 0 $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! $ - 0 ERROR:#DIV/0!
Liabilities and Shareholders' Equity
Cu
ent liabilities
Accounts payable $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! $ - 0 ERROR:#DIV/0!
Defe
ed revenue - 0 - 0 - 0
Income taxes payable - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Venture short term loan - due on demand - 0 - 0 - 0 - 0 - 0 - 0 - 0
Cu
ent portion of long term debt - 0 - - - 0 - ERROR:#DIV/0! ERROR:#DIV/0!
- 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Term loan - 0 - 0 - 0 - 0 - 0
Less: portion due within one year - 0 - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 - 0 - 0 - 0
Refinance term loan - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Less: portion due within one year - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
- 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Mortgage - 0 - 0 - 0 - 0 - 0
Less: portion due within one year - 0 - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 - 0 - 0 - 0
Total long term debt - 0 - 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Less: portion due in one year - 0 - 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
- 0 - 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Defe
ed income taxes payable - 0 - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Shareholders' Equity
Share capital - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Retained earnings - 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! - 0 ERROR:#DIV/0!
- 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
$ - 0 $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Out of Balance = must be 0 - adjust cash - 0 - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Financing Spreadsheet IS
POPLAR LTD.
Forecast Income Statement As Is Equipment Acquisition Combined Results Refinancing Pro-forma
Year ended January XXXXXXXXXX Basis Project Project Decision Income Statement
Revenues $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Cost of goods sold - 0 - 0 - 0 - 0 - 0
Gross profit - 0 - 0 - 0 - 0 - - 0
ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Expenses
Selling - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Administrative - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Amortization - 0 - 0 - 0 - 0 - 0
Interest - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
- 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Income before income taxes - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Income tax expense
Cu
ent - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Defe
ed - 0 - 0 - 0
Income tax expense - 0 - 0 - 0 - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Net income $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Retained earnings, beginning $ - 0 - 0 - 0 - 0 - 0 - 0
Less: Dividends - 0 - 0 - 0 - 0 - 0 - 0
Retained earnings, ending $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Earnings Per share
Shares Outstanding - 0 - 0 - 0
Earnings per Share ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
WACC
POPLAR LTD.
Acquisition Review
Weighted Average Cost of Capital
As Is Target
Capital Item $ Value Weighted Pre-Tax After-Tax Weighted Long-term
Average Return Return Component Objective
Pre-tax X XXXXXXXXXXtax rate) Weighted Average X After Tax Return
Tax Rate 0% see assumptions for rate
Venture Demand Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Term Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Refinance Term Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Mortgage Payable - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Share capital - 0 ERROR:#DIV/0! 0.00% ERROR:#DIV/0!
Retained earnings - 0 ERROR:#DIV/0! 0.00% ERROR:#DIV/0!
- 0 ERROR:#DIV/0! ERROR:#DIV/0! 15.5% + /- 1.0%
Debt as % of Total Assets ERROR:#DIV/0! 40.00%
Equity as % of Total Assets ERROR:#DIV/0! 50.00%
ERROR:#DIV/0! 90.00%
Weighted Average Cost of Capital
Combined Results
Capital Item $ Value Weighted Pre-Tax After-Tax Weighted
Average Return Return Component
Pre-tax X XXXXXXXXXXtax rate) Weighted Average X After Tax Return
Tax Rate 0%
Venture Demand Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Term Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Refinance Term Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Mortgage Payable - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Share capital - 0 ERROR:#DIV/0! 0.00% ERROR:#DIV/0!
Retained earnings - 0 ERROR:#DIV/0! 0.00% ERROR:#DIV/0!
- 0 ERROR:#DIV/0! ERROR:#DIV/0! 15.5% + /- 1.0%
Debt as % of Total Assets ERROR:#DIV/0! 40.00%
Equity as % of Total Assets ERROR:#DIV/0! 50.00%
ERROR:#DIV/0! 90.00%
Weighted Average Cost of Capital
Pro Forma
Capital Item $ Value Weighted Pre-Tax After-Tax Weighted Year 1
Average Return Return Component
Pre-tax X XXXXXXXXXXtax rate) Weighted Average X After Tax Return
Tax Rate 0%
Venture Demand Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Term Loan - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Refinance Term Loan ERROR:#DIV/0! ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Mortgage Payable - 0 ERROR:#DIV/0! 0.00% 0.00% ERROR:#DIV/0!
Share capital ERROR:#DIV/0! ERROR:#DIV/0! 0.00% ERROR:#DIV/0!
Retained earnings ERROR:#DIV/0! ERROR:#DIV/0! 0.00% ERROR:#DIV/0!
ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 15.5% + /- 1.0%
Debt as % of Total Assets ERROR:#DIV/0! 40.00%
Equity as % of Total Assets ERROR:#DIV/0! 60.00%
ERROR:#DIV/0! 100.00%
Financing Spreadsheet Ratios
POPLAR LTD.
Ratios As Is Modernization Business Combined Results Refinancing Pro-forma Target
January 31, Basis Project Acquisition Decision Balance Sheet Ratio
Cu
ent Ratio ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 1.20
Cu
ent Assets $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Cu
ent Liabilities $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Debt as % of Total Assets ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 45%
Total Debt $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Total Assets $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Equity as % of Total Assets ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 45%
Total Equity $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Total Assets $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! ERROR:#DIV/0!
Accounts Receivable
Accounts Receivable Collection Days ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Accounts