Export Tariff, FX-impact & Strategic Investment (Real Estate + M&A) Model of a hypothetical exporting company

Description
You are presented with a financial model of a hypothetical manufacturing company, 50% of whose revenue is generated from exports. The company has a significant investment plan, which depends on whether it upgrades its existing facilities or acquires a new building. The company can purchase the building in Year 1 or Year 2 (controlled in cell P8). The impact on the capital plan can be observed on the Investment Schedule sheet, lines 12 & 13. The building acquisition would introduce mortgage debt, the key assumptions for which are outlined on the Speculative Projects sheet.

Another investment option is M&A, involving the acquisition of an external business generating revenue and positive EBITDA. Note that synergies with the existing business are also incorporated. M&A assumptions are also provided on the Speculative Projects sheet. Both investment options can be toggled on and off on the Output sheet and can be executed in Year 1 or Year 2 (controlled in cells P8 & P9).

The model outputs the key financial metrics of the business: revenue, EBITDA, debt, as well as the ability to comply with the debt covenant (Net Debt / EBITDA not exceeding 4.0x). Covenant breaches above 4.0x are highlighted in red.
The model includes three growth scenarios for the core business. These are standard: upside, downside, and baseline. You can adjust the projected revenue growth rates in cells E4–J6 on the Output tab.

The model also incorporates macroeconomic shocks:

1. The introduction of import tariffs on the company’s products, affecting export revenue. It is assumed that the company fully absorbs the tariffs without passing them on to customers. The tariff rate can be adjusted in cell O20 on the Output sheet. The model does not allow selecting the year of introduction — the tariff either occurs or does not occur in Year 2.
2. A market disruption in Year 3 driven by the development of AI. The timing is fixed, and the scenario is modeled as a 10% loss in sales.
   
   

A distinctive feature of the model is its treatment of FX impact on financial performance. As an exporter, the company benefits from depreciation of the domestic currency. Revenue growth scenarios are calibrated at a base FX rate of 1.5. A long-term appreciation of the domestic currency by 2 cents per year is assumed. However, in the case of import tariffs, the domestic currency is assumed to depreciate by 10 cents, partially offsetting the tariff impact. FX assumptions can be found on the Output sheet (Q27–Q31).

Other model features:

• A productivity assumption measured as revenue per employee. In the base case, real revenue per employee grows at 0.5% annually. This drives the required headcount each year (see Model sheet, lines 86–90).
• As macroeconomic shocks reduce revenue, the required headcount declines accordingly. If the reduction exceeds a defined threshold (default: 10, see Output, cell Q46), the model triggers severance costs, assuming layoffs rather than natural attrition.
• Finally, the model calculates interest expense based on the average expected debt balance during the period, rather than the prior period closing balance, which provides greater accuracy. A workaround has been implemented to resolve the circularity issue, where ending debt is affected by current-period interest payments (see Model sheet, lines 93–103).