CAPEX Planning for Startups
You do not spend tens of hours researching the best spreadsheet setup for your capital expenditure (CAPEX for short) plan. Pry has the most uncomplicated and straightforward modeling tool that allows you to set up CAPEX quickly and flow it through all of your financial statements in a few clicks.
In this tutorial, we will be booking the CAPEX for an equipment purchase at a fictional company.
A long-term asset such as equipment has a useful life, usually counted in months or years. Therefore, the asset's value on book will keep decreasing throughout its useful life until it reaches zero, gets sold off, or is written off. This process is known as depreciation. An asset is considered fully depreciated when its value on book reaches zero. The easiest way to account for depreciation is using the straight-line depreciation method: Where the decrease in the asset's value is the same every month during the asset's useful line (assuming the asset will be fully depreciated).
To simplify things, we will assume the equipment will be fully depreciated using straight-line depreciation and thrown away at the end of its useful life.
We can divide the CAPEX forecast for this equipment purchase into the following general steps:
- First, in the equipment purchase month, debit the Equipment account on the Assets side of the Balance Sheet for the equipment purchase amount and credit the Cash account for the same amount.
- Simultaneously, start the depreciation by debiting the Depreciation Expense account under Expense on the Income Statement for the monthly depreciation amount and crediting Accumulated Depreciation, a Counter-Asset account on the Assets side of the Balance Sheet, the same amount.
- Finally, after the equipment is fully depreciated, debit Accumulated Depreciation and credit Equipment by the equipment purchase amount.
Based on these steps, we will make three assumptions for the CAPEX forecast:
- Equipment Purchase Price: $500,000
- Equipment Purchase Date: June 2022
- Equipment Useful Life in Months: 12
Let us create a new model in Pry and name it "CAPEX - Equipment." In this new model, create three inputs matching the assumptions above. To create an input, click on the "+" sign in the top-right corner of the Inputs column and select the appropriate input type for each assumption.
The assumptions will look like the following in Pry:
This is the first step of the three in creating a CAPEX forecast we identified in the previous section. Next, we need to debit the Equipment account on the Assets side of the Balance Sheet.
Make sure you have an Equipment account under Asset on the Balance Sheet before following along. To create the Equipment account, go to Financials, then scroll down to Financial Report. In the Assets section, just below Net Income, hover over the Assets parent account (the bolden "Assets" with the collapsable arrow next to it), click on the three horizontal dots, select "+ Add Sub-Account," and rename it to "Equipment."
To debit Equipment with the purchase price on the purchase date, we should use the following logic for the calculation:
- If the month selected is equal to the equipment purchase date, then the formula outputs the equipment purchase price; otherwise, the formula outputs zero.
We will create a formula in the formula column by clicking on the "+" sign in the column and naming it "Equipment - Balance Sheet." And then we translate the logic into Pry formula like such:
We can click on the date (Jun 2022) at the top right corner of this formula box and switch it to a different month to check if this formula works - for any month besides June 2022, the formula should output zero.
Let us hold off mapping this Equipment account output into the Balance Sheet since we have one more Equipment account output to take care of in the next two steps.
Pry will automatically take care of the crediting Cash part of this step once we map the finalized outputs to the Equipment account under Asset. We will go over it shortly.
Now for the second step, we need to calculate the outputs for Depreciation Expense on the Income Statement and Accumulated Depreciation on the Assets side of the Balance Sheet.
First, the logic to calculate monthly Depreciation Expense outputs using straight-line depreciation goes:
- The last month of the equipment's useful life is that Equipment Purchase Date + Equipment Useful Life in Months - 1 (We subtract one at the end because the equipment will start depreciating immediately after purchase, so the month when equipment purchase happens also counts towards its useful life and we need to subtract it back).
- If the selected month is equal to or past the Equipment Purchase Date and is also equal to or before the last month of the equipment's useful life, the monthly depreciation amount is Equipment Purchase Price/Equipment Useful Life in Months.
Per its namesake, Accumulated Depreciation is an accumulation of past depreciation expenses on the equipment. Therefore, we just need to keep adding the monthly depreciation this account until the equipment reaches the last month of its useful life. Also, since this is a Counter-Asset account, we will just need to put a negative sign in front the Depreciation Expense outputs to create the outputs for Accumulated Depreciation.
The formulas to produce these outputs using the same logic will look like this in Pry (Equipment Depreciation Expenses - Income Statement for Depreciation Expense, and Accumulated Depreciation - Balance Sheet for Accumulated Depreciation):
Now, we just have one step left to adjust these outputs before mapping them to the appropriate financial statement accounts.
When the equipment reaches the last month of its useful life, the Equipment and Accumulated Depreciation total balances will be reversed next month (On the Balance Sheet, we will see zeros for both accounts next month). This assumes that we throw away or give away the equipment after the end of its useful life, which is usually the case.
Given the formula outputs we have already created for Equipment and Accumulated Depreciation, we will adjust them using the following logic to reflect this step:
- If the selected month is between or on the Equipment Purchase Date and the last month of the equipment's useful life, then return the outputs we have calculated in steps 1 and 2 for Equipment and Accumulated Depreciation, respectively.
- Otherwise, if the selected month is the month immediately after the last month of the equipment's useful life, then return the opposite amount of the total account balance for the account (for Equipment, the amount returned is - Equipment Purchase Price; for Accumulated Depreciation, the amount returned is Equipment Purchase Price).
- For any other month, the output should be zero.
Now, we can create these adjusted outputs in Pry. We will name these formulas Equipment (Fully Depreciated and Throw Away) - Balance Sheet and Accumulated Depreciation (Fully Depreciated and Throw Away) - Balance Sheet.
At last, we have all the outputs ready to map to their prospective financial statements.
To map a formula output to a financial statement account, click on the "+" sign in the top right corner of the Outputs column. The mapping goes as the following:
- Equipment (Fully Depreciated and Throw Away) - Balance Sheet > Equipment (Reminder: Equipment is a Balance Sheet account on the Assets side we have created in the first step).
- Equipment Depreciation Expenses - Income Statement > Depreciation Expense (Depreciate Expense is a default Income Statement account, if you do not see it on your Financial Report before the mapping, do not worry, it will appear after we finish mapping).
- Accumulated Depreciation (Fully Depreciated and Throw Away) - Balance Sheet > Accumulated Depreciation (Depending on how your book is set up, this might be an account you need to create under Assets, just follow the same steps for creating the Equipment account).
For each output, put the formula (the one before the ">" sign in the mapping above) in the first dropdown and the financial statement account (the one after the ">" sign) in the second dropdown.
All left to do is check whether the mapping has been implemented correctly. First, we switch to the Financials page and go to Balance Sheet (if you do not have one, here is how you can have Pry automatically generate one for you in seconds). Next, let us scroll to the Assets section and check Equipment and Accumulated Depreciation. Based on our assumptions, the Equipment balance would be $500,000 from June 2022 to May 2023 and zero for the rest of the months. Accumulated Depreciation would keep adding up the monthly depreciation amount ($41,667) until May 2023 when it reaches -$500,000 and be zero for the rest of the months.
The Balance Sheet accounts look correct here. Let us move on to Income Statement and Cash Flow.
As previously mentioned, Pry will automatically adjust the cash flow once we map the output to the other financial statements. Here is how: Unlike other tools, Pry uses the indirect method to report cash flow. Therefore, every time we change an account balance affecting Net Income, Assets, Liabilities, or Equity, the cash flow will be automatically adjusted to reflect the inflow (+) or outflow of cash.
The cash outflow from adjusting Assets balance changes is $458,333 in June 2022 and the cash outflow from depreciation expense, which is grouped into Net Income (not visible from the screenshot above), is $41,667. So the net cash outflow from this equipment purchase in June 2022 is $500,000. And each of the next eleven-month, the net cash outflow is zero.
Note that this will be more obvious on your Financial Report if you have planned nothing besides this equipment purchase. You can quickly validate the automatic cash flow adjustment when you create your first account and workspace.
This is all for our tutorial on CAPEX planning. You can always apply the same method for different types of CAPEX smoothly.