Trucking / Transportation Business: Scaling Financial Model

5-year Financial Projections, Accounting, Amortization, Budget, Budgeting, Business Valuation, Cap Table, Cash Flow Analysis, Cash Flow Projections, Cash-on-Cash Multiple, CFO, DCF Model, Debt Service Coverage, Discount Rate, Excel, Financial Analysis, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Ratios, Financial Statements, Forecast, Forecasting, Income Statement, Investment Multiple, Investor Cash Flows, IRR (Internal Rate of Return), KPIs (Key Performance Indicators), LBO (Levered Buyout), NPV (Net Present Value), Pricing, Private Equity, Pro-Forma, Profitability Analysis, Return on Invested Capital (ROIC), Return on Investment (ROI), Revenue Projections, Scenario Analysis, Sensitivity Analysis, Startup Financial Models, Terminal Value, Three Statement Model | Three Financial Statements

Video Overview:

I was looking at building a model for the freight and shipping industry. The exact business I ended up landing on was a trucking company. It had some of the cleanest scaling dynamics I’ve done, where there were major variable expenses and revenue streams that could easily be tied to the number of trucks in use over time and their activity.

I modeled for up to three truck types (or technically, it could be three transportation types) that allow for the configuration of:

# of Monthly Deliveries
Growth of Deliveries
Average monthly delivery capacity per truck type
# of Purchased Trucks (if you select the ‘lease’ option for trucks, then this area will zero out, and there is a separate input for the monthly lease costs per truck.
Average purchase price per truck

Note, the model runs off delivery count, and based on the assumptions above, it will tell you how many trucks are needed to meet the estimated demand. You will then enter the trucks purchased accordingly, and there is a calculation that automatically tells you if you have enough coverage or not each month (it will highlight in green if you are good and red if you don’t have enough trucks to meet the estimated defined demand)

The revenue is simple. You can simply define an average monthly delivery fee you charge for each truck type, and that will automatically calculate total revenues. The fee is adjustable over 5 years.

For variable costs, the following are configurable over 5 years:

Average Miles Driven per Delivery – Type 1, Type 2, Type 3
Average Miles per Gallon per Vehicle Type – Type 1, Type 2, Type 3
Average Cost per Gallon
Repair / Maintenance per Truck per Month – Type 1, Type 2, Type 3
Average Monthly Driver Wages – Type 1, Type 2, Type 3
Average Monthly Insurance per Truck per Month – Type 1, Type 2, Type 3
Other Monthly Costs per Truck per Month – (applied to total owned trucks and 5 slots)
Other Monthly Costs per Truck per Month – (applied to total used trucks and 5 slots)
Other Costs per Delivery – Type 1, Type 2, Type 3

There are input sections for other fixed monthly expenses as well as other capex purchases and other startup costs not counted anywhere else.

There is an exit value option that can be included, and it is based on a defined EBITDA multiple.

After all these assumptions are entered in, you will see automatically generated monthly and annual financial statements (Income Statement, Balance Sheet, and Cash Flow Statement) as well as monthly and annual cash flow detail / EBITDA that shows a detail of how all the assumptions come together to form revenues, cost of sales, operating expenses, and down to cash flow.

There is an annual DCF Analysis, IRR, NPV, ROI, and Equity Multiple, as well as an option for a joint venture with inside and outside investor options.

The model ultimately solves for the minimum equity required to keep the cash position from ever going below 0 based on all startup costs and any net cash burn.

You will find many visualizations as well. The visualizations tab has a nice flow to it, with each row of images pertaining to the same financial basis. For example, total revenue, revenue by type or total EBITDA, EBITDA per truck, and EBITDA per delivery.

The model comes with all the inputs blanked out, so you can go in and enter your own. Only adjust cells in yellow, and you can follow along with the video that goes through a tutorial about entering in all the assumptions.

You must log in to submit a review.