5. Add Calculations to Your Profit and Loss Projections
In this video, you will use functions to calculate your total revenue, expenses, and net profit in your “twelve-month profit and loss projection” worksheet.
Net profit is your profit after subtracting all expenses and taxes.
Estimating how much your business will earn and spend in its first year helps you predict when you might earn a profit.
This allows you to make decisions about how your business should operate, what to invest in, and when to seek additional funding for growth.
To begin, use the “SUM” function to add up the totals for your first month in the “total revenue” row.
Select the cell for total revenue below your first month.
Type “equals sign” then “s-u-m."
Select the “SUM” function from the suggested functions.
Then specify the range of cells to add together.
Total revenue for the next month.
Change the range to match the column you’re working in.
To quickly apply the sum function to each month column, drag it across your “total revenue” row.
This applies the function to each cell in the row.
Next, total revenue for your category in the “yearly” column.
Drag the function down to apply it to the rest of the revenue categories.
Repeat this process to total each month and category in your “cost of sales” and “expenses."
Next, use a subtraction formula to calculate “gross profit."
A formula performs a calculation for a range of cells, such as subtracting one cell value from another or multiplying the values in two cells.
In this case, “gross profit” subtracts “cost of sales” from “revenue."
Drag your subtraction formula across the row to calculate it for each month and the year.
Next, use a multiplication formula to calculate your “reserve for contingencies,” or unexpected expenses.
A safe operating reserve for contingencies is about twenty percent of total expenses, or enough to cover three-to-six months of operating expenses.
Multiply your subtotal expenses by the percentage that you want to reserve.
Write the percentage as a decimal, so twenty percent would be zero-point-two.
Drag your formula across the row to apply it to the rest of the columns.
Next, use the “SUM” function to add your subtotal expenses to your reserve for contingencies.
Drag your formula across the row to add the subtotal and contingency reserve in each column.
Then subtract “total expenses” from “gross profit” to calculate “net profit before taxes."
Drag the formula across the row to calculate monthly and yearly totals.
Add your taxes together.
Apply the function to each tax type.
Subtract your taxes from net profit to find your “net operating income."
Apply your subtraction formula to the entire row to complete your table.
As you have more information, go back and edit specific categories or months.
Your functions and formulas will automatically update your sheet totals.
Now, it’s your turn: Calculate your monthly totals, totals for each category, and total expenses, Subtract “total expenses” from “gross profit” and find “net profit,” Calculate your “reserve for contingencies,” And subtract taxes from “net profit” to find your “net operating income."
- Calculate your monthly totals, totals for each category, and total expenses.
- Subtract total expenses from gross profit and find net profit.
- Calculate your reserve for contingencies.
- Subtract taxes from net profit to find your net operating income.