Project 2 Cutting Edge

pdf

School

University Of Denver *

*We aren’t endorsed by this school

Course

4500

Subject

Finance

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by PresidentWolf2618

FIN 4500 - 1: Financial Modeling Project 2 – Pro-Forma Valuations Using the financial statements templates for the Cutting Edge B2B Corporation, please construct pro forma balance sheets, income statements, and free cash flows for each year 2016-2019. Then find the value of the firm as of the end of 2015. Make a new version of your base model for each of the assignments below. For the first two scenarios below assume the following: • COGS/Sales=60% each year, per a new plan to reduce costs • No new common stock will be issued • Annual sales growth rate 19.54% for each year • Interest rate on notes payable debt = 8.3% • Interest rate on long-term debt = 7.1% • Total interest expense is the sum of the 2-year average interest expense for both notes payable and long-term debt • Notes payable is constant at the 2015 level • Growth rate in FCF after 2019 = 5% • WACC = 18% • Common dividends will be $45.3 million each year • Tax rate = 40% • Marketable securities are constant at the 2015 level. They are also a non-operating asset • Preferred stock, and preferred stock dividends, are constant at 2015 levels • Include formulas to remove the possibility of negative debt on your pro forma balance sheets. 1. Base Case Model . Use the percent of sales method to project the financial statements mentioned above. Use a 3-year-average percentages of sales for your forecasts, except as otherwise noted in this assignment (that means calculate the rates based on the past financial statements and average them as an assumption going forward). Both annual depreciation expense and net fixed assets vary with sales . Accumulated depreciation each year equals accumulated depreciation from the prior year plus depreciation expense for the current year. Fixed assets at cost equals net fixed assets plus accumulated depreciation. Let Long-term debt be your plug figure. Use the FCF method to find the projected price per share for Cutting Edge under this first set of planning assumptions. 2. Now make two copies of your spreadsheet from #1 above. In the first copy, construct Data Table and perform a sensitivity analysis of price per share to changes in Costs/Sales. In the second copy, construct Data Table and perform a sensitivity analysis of price per share to changes in NFA/Sales. Let Costs/Sales step from 40% to 70% in

steps of 5%, and let NFA/Sales step from 20% to 100% in steps of 10%. Make plots of price per share versus each input variable and comment on your results. 3. Return again to the base case model. Assume now that Cutting Edge has a required long- term debt repayment schedule set by its long-term lenders with the following provisions: • The amount of debt shown on the 2015 balance sheet must be repaid in equal installments of principal over the planning period. • No dividends can be paid until all long-term debt is repaid. • No new equity will be issued. • Net fixed assets will remain constant over the planning period, and fixed assets at cost grow by an amount equal to annual depreciation (that is, there will be zero “net investment” in the firm over the next four years). Your boss came to you with these requests. Your deliverable is the excel worksheet containing the analysis, as well as a memo comparing your results for each model with the base case and explaining differences (you can include the memo as a first description page in your excel spreadsheet or as a separate word document). Include numbered and labeled exhibits containing the balance sheets, net income statements, and your valuation calculation for each scenario. You are fairly new, so you don’t want to keep going back asking for more information (that means feel free to make assumptions if you feel certain information is missing – just make sure you identify these assumptions in your memo).

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version