Financial Modeling for Renewable Energy M&A

Calculate target equity value using forward net income and a 25x PE, set a 50% premium offer value near 3.1 billion, and design stock-cash financing with debt refinancings and fees.

Model the sources and uses of funds for a renewable energy M&A deal, including new debt, refinancing, equity issuance, and fees, then calculate asset write-up, depreciation, and deferred tax liability.

Calculate the purchase price allocation by determining the target's net assets' fair market value and goodwill from the offer value, then adjust the target's balance sheet for the combined entity.

Construct the day 1 balance sheet for the combined entity by adjusting cash, PA write-up, and goodwill, then update liabilities and equity for debt refinancing and deal fees.

Calculate gross debt over EBITDA, net debt over EBITDA, and debt-to-equity ratios for the acquirer, target, and combined entity using balance sheets, cash, and equity values.

Run a sensitivity analysis on premium paid and stock-based consideration to show year-one EPS accretion or dilution under different financing structures.

Model the acquirer's revenue by calculating installed capacity, operating hours after maintenance, and net operating hours, then derive net generation after subtracting auxiliary power and compute capacity and energy revenue.

Model the acquirer's escalation factor by building a forecast period counter from the forecast period flag and a switch, then apply a 1.5% escalation to operating costs.

Model the O&M costs: compute variable costs at $3 per kWh based on total generation, and fixed costs using the acquirer escalation rate and forecast period flag.

Model the acquirer's equity by setting up paid in capital, retained cash, retained earnings, and dividends paid, with a zero PPA balance and placeholders awaiting financial statements.

Model taxes by calculating earnings before tax, taxable income, and net operating losses, then project NOL utilization and balance to assess tax benefits under a 21% rate.

Model tax paid, tax expense, and deferred tax liabilities using taxable income and tax rate, incorporate initial deferred tax assets, and placeholder earnings before tax for finalization.

model the term loan principal repayment and interest expense using the provided schedule and interest rates to derive the term loan balances and the scheduled debt service.

Model the debt service reserve account by deriving the target balance from future debt service, comparing it to the opening balance, and determining deposits or releases.

Model deposits into and withdrawals from the debt service reserve account using the cash opening balance and cash flow, applying target deposits and shortfall logic to cushion lenders.

Model the target's accounts receivable by mirroring the acquirer, computing cash collected from current and prior period revenue using 30 days outstanding, opening balance, and a pre forecast date flag.

Model the target's pp&e and depreciation by calculating opening balances, depreciation end dates, and quarterly expense, applying forecast-period flags to restrict depreciation to the forecast and the balance sheet.

Model the target's financial statements by building income statement, then link to the balance sheet and cash flow, converting gross margin to gross profit while including COGS, SG&A, and R&D.

Model pro forma financial statements for a merged entity by calculating the stub period percentage and days in the acquisition period, then apply post-acquisition and forecast period flags.

Model the pro forma revenue by applying the post acquisition forecast adjustment to acquirer and target revenues, using 33% during the acquisition period and 100% afterward, then sum the two.

Model the combined entity's operating costs by applying post acquisition adjustments to acquirer COGS, G&A, and R&D, and quantify COGS 5%, G&A 6%, and R&D 6% synergies.

Model a two-phase cost savings realization schedule after the acquisition end date: 25% in the first two post acquisition periods, then 100% from the third.

Model the realized cost savings from the acquirer’s operating costs and compute post acquisition operating costs with cost savings synergies in COGS, SG&A, and R&D.

Model the combined entity's working capital by applying the accounts receivable to revenue ratio and the accounts payable to operating costs ratio, with stub and post-acquisition adjustments.

Model the acquirer's working capital by tracing accounts receivable and accounts payable through acquisition and stub periods to compute the pro forma change.

Model the combined entity's property, plant and equipment after an acquisition, calculating depreciation, fair value write-ups (15%), and opening balances to derive post-acquisition depreciation.

Model the depreciation of the PPA write up for the combined entity by calculating the depreciation end date plus one, applying forecast period flags, and updating PPA write up balance.

Model the equity of the combined entity by incorporating the acquirer's paid in capital opening balance and planned equity issuance, while excluding the target's equity per purchase accounting rules.

Model the retained cash balance of the combined entity by combining acquirer and target cash, accounting for refinancing and deal fees, and tax savings.

Model the combined entity's retained earnings starting from the acquirer balance at acquisition, adjusting for post-tax deal and legal fees, with an acquisition date flag guiding opening versus closing balances.

Model the taxes of the combined entity by applying an asset sale step-up to fair value for tax depreciation and derive the preliminary pa balance.

Model the post acquisition forecast year counter to calculate tax depreciation of the pad write up, using the maker schedule and post acquisition forecast adjustment, and apply the if function.

Model the target's tax pad depreciation using a five-year maker schedule and macrs rates, applying vlookup on the post-acquisition forecast counter and adjusting for the stub period.

Model the combined entity's taxes by calculating EBT for tax purposes, taxable income, and Nol balance, detailing Nol created and utilized under Section 382 and sale-structure effects.

Model the tax paid for the combined entity by calculating the acquirer NOL utilized under Section 382, applying the 80% taxable income cap, and deriving tax from taxable income.

Model the target's new deferred tax liability in a stock sale, then compute the acquirer's DTL and the deferred tax impact of financing fees within the combined entity.

Model the refinancing facility to replace or assume the target's debt, size debt service from cash flow, and cover revenue, costs, working capital changes, and tax paid over ten years.

Model a refinancing facility for renewable energy mergers and acquisitions, size total and tranche one at 40%, and apply an eight-year quarterly tranche one tenor flag for principal repayments.

Model refinancing facility tranche one drawdown and balance, calculating scheduled principal repayments within cash flow constraints, using min and if functions to cap drawdown and repayments.

Model tranche one interest expense by calculating the all in rate per annum, converting to a quarterly rate, and adjusting for the stub period with post acquisition forecast adjustment.

Model tranche one upfront fees for the refinancing facility, compute cash upfront fee paid and amortization expense, adjust the tranche one balance, and determine tranche two size from the facility.

Model the acquisition facility size and repayments by calculating cash flow from revenue, operating costs, taxes, and debt service, applying the target dscr of 2 and a 75% drawdown cap.

Model the debt switch in a renewable energy merger by comparing refinancing versus assuming target debt, and recalculating interest, principal repayments, fees, and debt service.

Model the deductible interest expense for the combined entity under the 30% of tax ebit limit, incorporating revenue, operating costs, tax depreciation, and carry-forward of unused interest.

Model the interest expense carried forward balance at acquisition and its tax-book carryover under a stock deal, applying the long-term tax-exempt federal rate and stub-period adjustments.

Model the acquirer's DSRA balance by calculating the opening balance at acquisition, applying post-acquisition forecast adjustments, and deriving the closing balance at acquisition.

Model the uses of funds for an acquisition, including purchase price and refinancing, and compute equity value from forward net income using a 25 price-earnings ratio with a 50% premium.

Model the sources of funds for an acquisition by deriving excess cash for the acquirer and target, then finance uses with cash, debt, and equity.

Model sources of funds for renewable energy M&A by integrating refinancing facility drawdown with the acquisition facility, calculating uses of funds, equity issuance, and cash remaining after debt and fees.

Model macros to avoid circularity by linking live calculations to hardcoded values on a macro worksheet, using goal seek and copy-paste macros to align pro forma and transaction cash flows.

Automates refinancing and acquisition modeling by building goal seek and copy-paste macros, naming cells with a name manager to align hardcoded values with live calculations.

Create VBA macros for refinancing facility size and cash flow using Goalseek, then automate them with a do loop until the refinancing check confirms live and paste values match.

Develop and test the macros for the acquisition facility and refinancing facility, including a goalseek-based repayment macro, a copy-paste macro, and a master do-loop that updates calculations and data tables.

Model goodwill by subtracting the target’s net asset fair value from the purchase price, after considering asset and liability values and the pad write up deferred tax liability.

Model goodwill amortization over 15 years using the same methodology as PPA depreciation and tax depreciation, linking it to the group interest expense and tax paid calculations.

Model pro-forma financial statements for the combined entity, linking revenue, cogs, gross margin, depreciation, operating profit, earnings before tax, and the balance sheet impact of equity and debt.

Calculate the acquisition facility credit risk linked margin by computing the debt service coverage ratio for the combined entity, using data tables to avoid circularity in interest expense calculations.

Model annual pro forma financial statements for the combined entity by consolidating income statements, balance sheets, and cash flow statements using sumif and lookup for eps accretion and dilution analysis.

Analyze earnings per share accretion and dilution in a renewable energy m&a deal by modeling the post acquisition flag, acquirer shares outstanding, and the combined entity earnings per share.

Calculate the transaction IRR by identifying the equity investment from excess cash and issued equity, then assess the cash flow available for the dividends and the incremental cash flow.

Present value creation analysis: discount factor at 10% cost of capital, stand-alone value without synergies, and separate valuation of synergies and tax benefits.

Model the combined entity’s free cash flow by computing NOPAT from operating profit without synergies, applying tax expense (excluding tax benefits), then add depreciation and working capital changes.

Model the present value of incremental free cash flow by taking the difference between the combined entity and acquirer free cash flows, then apply the discount factor and estimate terminal value.

Model the present value of terminal value by using the last-year forecast flag, incremental free cash flow, and the perpetuity method with terminal growth and cost of capital.

Model the present value of synergies by converting synergies before tax to after tax, then discounting them with the discount factor to obtain the present value and terminal-year value.

Model the valuation items for value creation analysis by calculating the present value of DSRA cash flows and the excess cash from the refinancing facility.

Compute the target's equity value before and after the transaction, add synergies and tax benefits, subtract the purchase price and fees, and account for refinancing cash to determine value creation.

Model the unlevered irr and compare it to the weighted average cost of capital, by calculating invested capital, incremental operating cash flow, and total cash flow to reveal value creation.

Model the unlevered IRR without synergies and tax benefits to isolate the base return from leverage and business performance, using the combined entity’s operating cash flow.

Create a deal summary worksheet with an IRR breakdown and a chart, linking unlevered IRR, business performance uplift, and leverage uplift from the valuation worksheet.

Calculate value creation in the deal summary worksheet by combining target standalone equity value, present value of operating and non-operating synergies, premium, costs, and refinancing cash, then chart results.