I've done more critical research in warehouse logistics than I thought I'd ever do in my life after building this financial model. We are talking about 3rd party logistics (3PL). The template does a great job at capturing seasonality, warehouse sizing economics, activity-driven revenues / expenses, and showing a financial projection based on bottom-up assumptions.
After purchase, the template will be immediately available to download. It is also included in the industry-specific and real estate model bundles as well as the Super Smart Bundle.
This model is geared towards operators (meaning the user is paying rent to occupy the warehouse). At its core, there are two ways to build the projection. You can base it solely on pallets (up to 5 types) or you can base it on occupied volume (cubic feet/meters). I've put a simple toggle to select one of those two options and there are two separate, mutually exclusive, assumption frameworks the user can fill out for each. They have their own sections for revenues and direct labor / materials.
The model is focused on occupancy of storage space and the activity therein to calculate up to 7 revenue streams and dynamic direct costs accordingly. You'll see inputs such as inventory turnover, orders per pallet, units per order, orders per cubic foot, cubic foot per bin, average cubic foot per unit, and more.
Template Features:
- Model up to 10 years of time.
- Includes 3-statement modeling (income statement, balance sheet, and cash flow statement).
- Monthly and annual views.
- Executive Summary.
- DCF Analysis, IRR, cash flow projection.
- Seasonality for occupancy, order volumes, and units per order.
- Driven off warehouse sizing (square footage and height).
- Includes separate sections for direct labor, direct consumables, fixed overheads, full-time employees, CAPEX, and debt assumptions.
- All formulas are editable and the spreadsheet is entirely unlocked.
- Easy color-coding for assumption cells vs. formulas.
- Total Warehouse Sq. Ft.
- % of Warehouse Usable for Storage
- Total Pallet Zone Usable Area (output)
- Pallet / Usable Area Utilization
- Actual Footprint Area for Pallets
- Ceiling Height (ft.)
- Clearance Needed (ft.)
- Usable Height (ft.) (output)
- Pallet Types (up to 5)
- Storage Pricing (monthly)
- Base Orders per pallet / Month (output)
- Base Units per Order
- Average Units/Pallet
- % of Footprint
- Sq. Ft. Available (output)
- Units per Carton
- Average Annual Inventory turns
- Pallet Dimensions
- Length
- Width
- Sq. Ft.
- Height (including load)
- Clearance Between Levels
- Per Level Height (output)
- Total Levels (rounded down) (output)
- Floor Pallet Positions (output)
- Total Pallet Positions (output)
- Growth Rates for orders, units/order, pricing.
- Pick and Pack Fees (flat fee/order, per unit, and materials/order)
- Receiving Fees
- Value-added Services (up to 5) based on % applied per driver
- Relabel Fees
- Per Kit / Bundle Fee
- Return Processing Fee
- Pallet Rework Fees
- Insert Fees
- Define pallet occupancy rates over time by type and seasonality factor per month over time. The seasonality percentages should add up to 100% when summing the entire year. That section is essentially saying how much of the total 'usage' of space is happening in each month of the year, constrained by max pallet spaces available. You can't have more pallet spaces used in a month than what the max spaces available is. The cell will highlight red of you do go above that and it is based on year 1 occupancy percentages, but to test it you can change the occupancy to see at what point you would be over the limit of spaces available for a given pallet type.
- Orders per pallet index and units per order index to show that over the course of a given year, those variables can change against a defined base number.
- Monthly Price per Cubic Foot
- Pick and Pack Fees
- Receiving Fees
- Value-added Services (up to 5)
- Seasonality (percentage of sellable volume sold each month of the year over time)
- Growth assumptions for orders, units per order, and pricing.
- Base Orders per Occupied Cubic Foot /mo.
- Base Units per Order
- Average Cubic Feet per Unit
- Order Cubic Feet (output)
- Average Carton Size (cubic feet)
- Fill Efficiency
- Effective Carton Capacity (cubic feet) (output)
- Average Cartons per Order (output)
- Seasonality index for orders and units/order.
- This works similarly between both options. There are 8 activities and the user can define the expected average minutes for completely a unit of the given activity. The pallet-based model does let you define various times a given pallet type takes as they could vary. Then it will take a weighted average for the total of each activity.
- You can define the average wages, including overtime assumptions for each activity. Essentially, defining the wages for the labor types that perform each activity. So, the model calculates the total hours spent on each activity and the resulting wages are used to figure out the direct labor costs.
- I've put notes in cells that are not as straight forward for how calculations are happening.
- Both options set these costs against the volume (orders / units) and replenishments that are happening. The pallet-based model also has a spot for costs if the warehouse is owning / shipping the pallets off or renting or if the pallets are fully customer owned.
- Note, for the volume-based option (option 2), you can define bin purchases that may be required on the 'CAPEX' tab. The depreciation expense from those purchase will be considered direct costs and hit above gross profit even though it is a non-cash item. The model zeros it back out in the cash flow row.
