Project Steps FNCE 390 Project Steps IBackground 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 abruptly 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. CSTEPS: 1Opening Financial Information Using Board approved financial results for Year 1, add the Balance Sheet amounts and Income Statement amounts in the correct accounts - they are color coded: Note - current portion of debt = Term loan = $ 240,000Mortgage = $ 115,000 To address the bank account overdraft a $3,000,000 share capital injection was made by the investment firm. 2Project Financials - this file aDevelop 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 bUsing 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 incurred - $900,000 - impacts Administrative costs for 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. cNext, 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 current market value - determined below - post split decision TOTAL COMPENSATION should not exceed $870,000 Salary +Shares#Shares ValueCompensation President$ - 0- 0ERROR:#DIV/0!ERROR:#DIV/0! VP Finance- 0- 0ERROR:#DIV/0!ERROR:#DIV/0! VP Operations- 0- 0ERROR:#DIV/0!ERROR:#DIV/0! VP Business- 0- 0ERROR:#DIV/0!ERROR:#DIV/0! $ - 0- 0ERROR:#DIV/0!ERROR:#DIV/0! The compensation expense is reflected 50% in admin and 50% in selling expenses AdminERROR:#DIV/0! SellingERROR:#DIV/0! dShare 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 Current market value of sharesERROR:#DIV/0! EBITDA - Combined Income before income taxes$ - 0 Add: Amortization- 0 Add: Interest- 0 $ - 0 Multiplier7 Current market value$ - 0 Issued and outstanding shares- 0Find in Key Input information - Phase 2A Proposed Stock Split- 0Select a figure which sets line 95 at venture capital target range Issued and outstanding shares revised- 0 Market Value per shareERROR:#DIV/0! Venture Capital Discount28% Venture Capital Share PriceERROR:#DIV/0! Meets Criteria of Share capital impact% Ownership52% or greater Issued an outstanding - above- 0 Issued an outstanding - stock compensation- 0 Issued and outstanding- 0ERROR:#DIV/0!ERROR:#DIV/0! New Issue Venture Capital ValueFrom aboveERROR:#DIV/0! New Shares Issued- 0ERROR:#DIV/0!Issue shares Total Shares- 0ERROR:#DIV/0! Share capital raisedERROR:#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 correctly. 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 DebtERROR:#DIV/0! Non-cash Executive Stock compensationERROR:#DIV/0! Share CapitalERROR:#DIV/0! Total investment = $15,000,000ERROR:#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 changesFunds Allocation Venture loan (repay by negative figure)- 0Check limits below Mortgage (repay negative - add - positive)- 0Check limits below Term loan (repay negative - add - positive)- 0Check limits below Refinance term loan (repay negative - add postiive)- 0Check limits below Cash (positive figure)- 018,000,000 Application = $15,000,000$ - 0sum = (18,000,000) as refinancing debt The mix of equity and debt must meet all criteria below and strive to maximize net income Management ParametersActualTarget Current RatioERROR:#DIV/0!1.20>ERROR:#DIV/0! Debt as % of Total AssetsERROR:#DIV/0!45.00%ERROR:#DIV/0! 75% of A/R Venture Loan$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Maximum Mortgage$ XXXXXXXXXX0Good Maximum Term loan35% of Equipment $ - 0$ - 0$ - 0Good Maximum Refinance LoanERROR:#DIV/0!10,000,000ERROR:#DIV/0! Range HiLow WACC - EndingERROR:#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 Current 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 correct Spreadsheet Ratios will self calculate - test for correct calculations Only enter figures with color codes Financing Spreadsheet BS POPLAR LTD. Balance SheetOpeningCash EquipmentAcquisitionCombined ResultsEarnings ImpactRefinancingPro-forma January 31, 2024-2025Balance SheetInjectionProjectProjectDecisionBalance Sheet Assets Current assets Cash$ - 0$ - 0$ - 0- 0$ - 0$ - 0 Accounts receivable- 0- 0$ - 0- 0ERROR:#DIV/0!- 0ERROR:#DIV/0! Inventory XXXXXXXXXX0ERROR:#DIV/0!- 0ERROR:#DIV/0! Prepaid expenses XXXXXXXXXX XXXXXXXXXX0ERROR:#DIV/0!- 0ERROR:#DIV/0! Property, plant and equipment Land XXXXXXXXXXBuildings XXXXXXXXXXEquipment XXXXXXXXXX0 Vehicles XXXXXXXXXX XXXXXXXXXX- 0- 0 Less: Accumulated amortization XXXXXXXXXX XXXXXXXXXXTrademarks, patents and goodwill XXXXXXXXXX0- 0 $ - 0$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!$ - 0ERROR:#DIV/0! Liabilities and Shareholders' Equity Current liabilities Accounts payable$ - 0$ - 0$ - 0ERROR:#DIV/0!$ - 0ERROR:#DIV/0! Deferred revenue XXXXXXXXXXIncome taxes payable- 0- 0ERROR:#DIV/0!ERROR:#DIV/0! Venture short term loan - due on demand XXXXXXXXXX0 Current portion of long term debt XXXXXXXXXXERROR:#DIV/0!ERROR:#DIV/0! XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Term loan XXXXXXXXXXLess: portion due within one year XXXXXXXXXX XXXXXXXXXXRefinance term loan XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Less: portion due within one year XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Mortgage XXXXXXXXXXLess: portion due within one year XXXXXXXXXX XXXXXXXXXXTotal long term debt XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Less: portion due in one year XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Deferred income taxes payable XXXXXXXXXX XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Shareholders' Equity Share capital XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Retained earnings XXXXXXXXXX0ERROR:#DIV/0!- 0ERROR:#DIV/0! XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! $ - 0$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Out of Balance = must be 0 - adjust cash XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Financing Spreadsheet IS POPLAR LTD. Forecast Income StatementAs Is EquipmentAcquisitionCombined ResultsRefinancingPro-forma Year ended January 31 2025BasisProjectProjectDecisionIncome Statement Revenues$ - 0$ - 0$ - 0$ - 0$ - 0 Cost of goods sold XXXXXXXXXXGross profit XXXXXXXXXXERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Expenses Selling XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Administrative XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Amortization XXXXXXXXXXInterest XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Income before income taxes XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Income tax expense Current XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Deferred XXXXXXXXXXIncome tax expense XXXXXXXXXX0ERROR:#DIV/0!ERROR:#DIV/0! Net income$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Retained earnings, beginning$ XXXXXXXXXXLess: Dividends XXXXXXXXXXRetained earnings, ending$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Earnings Per share Shares Outstanding XXXXXXXXXXEarnings per ShareERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! WACC POPLAR LTD. Acquisition Review Weighted Average Cost of Capital As IsTarget Capital Item$ ValueWeightedPre-TaxAfter-TaxWeightedLong-term AverageReturnReturnComponentObjective Pre-tax X (1-tax rate)Weighted Average X After Tax Return Tax Rate0%see assumptions for rate Venture Demand Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Term Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Refinance Term Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Mortgage Payable- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Share capital- 0ERROR:#DIV/0! 0.00%ERROR:#DIV/0! Retained earnings- 0ERROR:#DIV/0! 0.00%ERROR:#DIV/0! - 0ERROR:#DIV/0!ERROR:#DIV/0!15.5% + /- 1.0% Debt as % of Total AssetsERROR:#DIV/0!40.00% Equity as % of Total AssetsERROR:#DIV/0!50.00% ERROR:#DIV/0!90.00% Weighted Average Cost of Capital Combined Results Capital Item$ ValueWeightedPre-TaxAfter-TaxWeighted AverageReturnReturnComponent Pre-tax X (1-tax rate)Weighted Average X After Tax Return Tax Rate0% Venture Demand Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Term Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Refinance Term Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Mortgage Payable- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Share capital- 0ERROR:#DIV/0! 0.00%ERROR:#DIV/0! Retained earnings- 0ERROR:#DIV/0! 0.00%ERROR:#DIV/0! - 0ERROR:#DIV/0!ERROR:#DIV/0!15.5% + /- 1.0% Debt as % of Total AssetsERROR:#DIV/0!40.00% Equity as % of Total AssetsERROR:#DIV/0!50.00% ERROR:#DIV/0!90.00% Weighted Average Cost of Capital Pro Forma Capital Item$ ValueWeightedPre-TaxAfter-TaxWeightedYear 1 AverageReturnReturnComponent Pre-tax X (1-tax rate)Weighted Average X After Tax Return Tax Rate0% Venture Demand Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Term Loan- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Refinance Term LoanERROR:#DIV/0!ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Mortgage Payable- 0ERROR:#DIV/0!0.00%0.00%ERROR:#DIV/0! Share capitalERROR:#DIV/0!ERROR:#DIV/0! 0.00%ERROR:#DIV/0! Retained earningsERROR:#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 AssetsERROR:#DIV/0!40.00% Equity as % of Total AssetsERROR:#DIV/0!60.00% ERROR:#DIV/0!100.00% Financing Spreadsheet Ratios POPLAR LTD. RatiosAs IsModernizationBusinessCombined ResultsRefinancingPro-formaTarget January 31,BasisProjectAcquisitionDecisionBalance SheetRatio Current RatioERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!1.20> Current Assets$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Current Liabilities$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Debt as % of Total AssetsERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!45% Total Debt$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Total Assets$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Equity as % of Total AssetsERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!45% Total Equity$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Total Assets$ - 0$ - 0$ - 0$ - 0ERROR:#DIV/0!ERROR:#DIV/0! Accounts Receivable Accounts Receivable Collection DaysERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Accounts
project2-1ao3u3bu.xlsx
project2-opening-financials-jdiuewum.xlsx