WEBVTT
Kind: captions
Language: en
00:00:00.160 --> 00:00:04.160
In the previous video, you used functions to
calculate the costs in your data tables to
00:00:04.160 --> 00:00:08.960
tell you more about how much money the community
fundraiser spent preparing for the shirt sale.
00:00:08.960 --> 00:00:13.200
In this video, you will continue making
calculations to summarize all of your data
00:00:13.200 --> 00:00:20.013
and find the profit for the t-shirt sales, or
how much money the fundraiser made -- or lost.
00:00:23.200 --> 00:00:25.840
To start, complete the first
row of the *Profit table*.
00:00:27.360 --> 00:00:33.215
The *Profit table* uses the data from the *Cost and
Sales* tables to find the overall profit or loss.
00:00:33.215 --> 00:00:37.101
Copy the column of *units
purchased* from the cost column...
00:00:47.360 --> 00:00:51.142
and paste it into the corresponding
column in the Profit table.
00:00:59.600 --> 00:01:03.991
Then, copy your costs and paste
them into the next column.
00:01:21.680 --> 00:01:23.740
Oops! There’s an error.
00:01:26.400 --> 00:01:28.960
Click into the cells to figure out the problem.
00:01:28.960 --> 00:01:33.520
The function is multiplying the columns
that correspond to the current row.
00:01:33.520 --> 00:01:36.634
But you’re trying to match the function
in a different part of the spreadsheet.
00:01:38.400 --> 00:01:41.040
That’s because the function you
copied is a *relative* function.
00:01:41.760 --> 00:01:43.840
Typical cell references are *relative*.
00:01:43.840 --> 00:01:46.000
That means they change when a formula is copied to
00:01:46.000 --> 00:01:48.800
another cell because they are
based on the cells around them.
00:01:48.800 --> 00:01:51.440
To fix this, use an absolute cell reference.
00:01:51.440 --> 00:01:53.920
*Absolute* cell
references remain constant,
00:01:53.920 --> 00:01:56.400
even when you copy them to another cell or sheet.
00:01:57.040 --> 00:02:00.640
Absolute references are important when
you want to reference the same cell
00:02:00.640 --> 00:02:02.640
or range in multiple formulas.
00:02:02.640 --> 00:02:07.183
To create an absolute cell reference, add
dollar signs to the letter and number.
00:02:12.800 --> 00:02:15.760
Then drag down the cell handle
to populate the rest of the rows.
00:02:19.120 --> 00:02:21.200
Compare the results with the cost table.
00:02:21.200 --> 00:02:23.200
The numbers match -- nice work!
00:02:23.920 --> 00:02:28.828
Now, copy and paste the units sold from the
*Current Sales table* so you can find the revenue.
00:02:59.840 --> 00:03:03.440
The *revenue* tells you how
much money the fundraiser actually made.
00:03:03.440 --> 00:03:04.880
Calculate the actual revenue.
00:03:06.960 --> 00:03:10.514
Multiply the number of units
sold by their sales price.
00:03:36.080 --> 00:03:38.080
Next, calculate the actual profit.
00:03:39.680 --> 00:03:43.280
The *profit* is the difference between
how much was spent to prepare for the fundraiser...
00:03:45.440 --> 00:03:48.000
and how much was earned.
00:03:48.720 --> 00:03:50.720
Subtract the cost from the actual revenue.
00:03:50.720 --> 00:03:54.080
To make it easier to remember,
add the formula under the header.
00:03:58.320 --> 00:04:01.386
And wrap the text so the entire header shows.
00:04:24.320 --> 00:04:27.840
Then subtract.
00:04:36.960 --> 00:04:41.840
And drag down the formula to complete
the rest of the calculations.
00:04:51.440 --> 00:04:52.960
Nice job calculating the profit!
00:04:55.360 --> 00:04:59.360
Now, use a subtraction formula again
to find the remaining inventory,
00:04:59.360 --> 00:05:02.560
or the number of shirts left over
after the fundraiser is complete.
00:05:03.600 --> 00:05:08.470
This time, subtract the number of units
sold from the number of units purchased.
00:05:32.320 --> 00:05:37.840
Calculate the totals for the three columns
you just completed using the SUM function.
00:06:10.000 --> 00:06:12.480
Finally, complete your
*Profit Summary* table.
00:06:12.480 --> 00:06:15.280
This pulls the totals from each
of the tables into one place,
00:06:15.280 --> 00:06:19.840
so it’s easy to reference and see the most
important numbers, such as costs and profit.
00:06:21.120 --> 00:06:24.960
Transfer the functions you used in each table
to the *Profit Summary* table.
00:06:24.960 --> 00:06:29.760
Use absolute cell references so they update
automatically based on the contents of the tables.
00:06:31.520 --> 00:06:33.840
Start with cost…
00:07:13.440 --> 00:07:19.897
Then copy and paste the functions from the revenue
and actual profit columns, and make them absolute.
00:08:38.720 --> 00:08:42.560
Now that you’ve completed the cost,
revenue, and profit calculations,
00:08:42.560 --> 00:08:46.080
you have a more detailed view of the
sales data for the fundraiser, so far.
00:08:46.640 --> 00:08:49.920
This is useful data for the fundraising
committee because they might want to hold
00:08:49.920 --> 00:08:53.840
another fundraiser to sell more shirts,
or try selling something else instead.