WEBVTT
Kind: captions
Language: en
00:00:00.220 --> 00:00:06.720
In this video, you will use functions to calculate
your total revenue, expenses, and net profit
00:00:06.720 --> 00:00:10.170
in your “twelve-month profit and loss projection”
worksheet.
00:00:10.170 --> 00:00:14.210
Net profit is your profit after subtracting
all expenses and taxes.
00:00:14.210 --> 00:00:18.960
Estimating how much your business will earn
and spend in its first year helps you predict
00:00:18.960 --> 00:00:21.130
when you might earn a profit.
00:00:21.130 --> 00:00:25.970
This allows you to make decisions about how
your business should operate, what to invest
00:00:25.970 --> 00:00:28.279
in, and when to seek additional funding for
growth.
00:00:28.279 --> 00:00:34.520
To begin, use the “SUM” function to add
up the totals for your first month in the
00:00:34.520 --> 00:00:35.990
“total revenue” row.
00:00:35.990 --> 00:00:39.180
Select the cell for total revenue below your
first month.
00:00:39.180 --> 00:00:42.810
Type “equals sign” then “s-u-m."
00:00:42.810 --> 00:00:46.590
Select the “SUM” function from the suggested
functions.
00:00:46.590 --> 00:00:51.950
Then specify the range of cells to add together.
00:00:51.950 --> 00:00:53.820
Total revenue for the next month.
00:00:53.820 --> 00:00:57.140
Change the range to match the column you’re
working in.
00:00:57.140 --> 00:01:02.350
To quickly apply the sum function to each
month column, drag it across your “total
00:01:02.350 --> 00:01:03.350
revenue” row.
00:01:03.350 --> 00:01:06.390
This applies the function to each cell in
the row.
00:01:06.390 --> 00:01:13.740
Next, total revenue for your category in the
“yearly” column.
00:01:13.740 --> 00:01:18.260
Drag the function down to apply it to the
rest of the revenue categories.
00:01:18.260 --> 00:01:32.900
Repeat this process to total each month and
category in your “cost of sales” and “expenses."
00:01:32.900 --> 00:01:46.100
Next, use a subtraction formula to calculate
“gross profit."
00:01:46.100 --> 00:01:51.450
A formula performs a calculation for a range
of cells, such as subtracting one cell value
00:01:51.450 --> 00:01:55.390
from another or multiplying the values in
two cells.
00:01:55.390 --> 00:02:07.010
In this case, “gross profit” subtracts
“cost of sales” from “revenue."
00:02:07.010 --> 00:02:12.440
Drag your subtraction formula across the row
to calculate it for each month and the year.
00:02:12.440 --> 00:02:18.870
Next, use a multiplication formula to calculate
your “reserve for contingencies,” or unexpected
00:02:18.870 --> 00:02:22.260
expenses.
00:02:22.260 --> 00:02:30.890
A safe operating reserve for contingencies
is about twenty percent of total expenses,
00:02:30.890 --> 00:02:36.000
or enough to cover three-to-six months of
operating expenses.
00:02:36.000 --> 00:02:40.120
Multiply your subtotal expenses by the percentage
that you want to reserve.
00:02:40.120 --> 00:02:44.319
Write the percentage as a decimal, so twenty
percent would be zero-point-two.
00:02:44.319 --> 00:02:48.530
Drag your formula across the row to apply
it to the rest of the columns.
00:02:48.530 --> 00:02:55.770
Next, use the “SUM” function to add your
subtotal expenses to your reserve for contingencies.
00:02:55.770 --> 00:03:02.610
Drag your formula across the row to add the
subtotal and contingency reserve in each column.
00:03:02.610 --> 00:03:07.660
Then subtract “total expenses” from “gross
profit” to calculate “net profit before
00:03:07.660 --> 00:03:19.490
taxes."
00:03:19.490 --> 00:03:25.390
Drag the formula across the row to calculate
monthly and yearly totals.
00:03:25.390 --> 00:03:27.120
Add your taxes together.
00:03:27.120 --> 00:03:35.430
Apply the function to each tax type.
00:03:35.430 --> 00:03:47.430
Subtract your taxes from net profit to find
your “net operating income."
00:03:47.430 --> 00:03:54.690
Apply your subtraction formula to the entire
row to complete your table.
00:03:54.690 --> 00:03:59.600
As you have more information, go back and
edit specific categories or months.
00:03:59.600 --> 00:04:03.420
Your functions and formulas will automatically
update your sheet totals.
00:04:03.420 --> 00:04:07.599
Now, it’s your turn:
Calculate your monthly totals, totals for
00:04:07.599 --> 00:04:13.300
each category, and total expenses,
Subtract “total expenses” from “gross
00:04:13.300 --> 00:04:19.150
profit” and find “net profit,”
Calculate your “reserve for contingencies,”
00:04:19.150 --> 00:04:23.250
And subtract taxes from “net profit” to
find your “net operating income."