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.