Use Functions to Summarize Data and Calculate Profit

In the previous video, you used functions to calculate the costs in your data tables to tell you more about how much money the community fundraiser spent preparing for the shirt sale.

In this video, you will continue making calculations to summarize all of your data and find the profit for the t-shirt sales, or how much money the fundraiser made -- or lost.

To start, complete the first column of the "Profit" table.

The Profit table uses the data from the Cost and Sales tables to find the overall profit or loss.

Copy the column of the units purchased from the cost column.

And paste it into the corresponding column in the "Profit" table.

Then, copy your costs and paste them into the next column.

Oops! There's an error.

Click into the cells to figure out the problem.

The function is multiplying the columns that correspond to the current row.

But you're trying to match the function in a different part of the spreadsheet.

That's because the function you copied is a relative function.

Typical cell references are "relative." That means they change when a formula is copied to another cell because they are based on the cells around them.

To fix this, use an absolute cell reference.

"Absolute" cell references remain constant, even when you copy them to another cell or sheet.

Absolute references are important when you want to reference the same cell or range in multiple formulas.

To create an absolute cell reference, add dollar signs to the letter and number.

Then drag down the cell handle to populate the rest of the rows.

Compare the results with the "Cost" table.

The numbers match -- nice work! Now, copy and paste the units sold from the "Current Sales" table so you can find the revenue.

The "Revenue" tells you how much money the fundraiser actually made.

Calculate the actual revenue.

Multiply the number of units sold by their sales price.

Next, calculate the actual profit.

The "Profit" is the difference between how much was spent to prepare for the fundraiser and how much was earned.

Subtract the cost from the actual revenue.

To make it easier to remember, add the formula under the header.

And wrap the text so the entire header shows.

Then subtract.

And drag down the formula to complete the rest of the calculations.

Now, use a subtraction formula again to find the remaining inventory, or the number of shirts left over after the fundraiser is complete.

This time, subtract the number of units sold from the number of units purchased.

Calculate the totals for the three columns you just completed using the SUM function.

Finally, complete your "Profit Summary" table.

This pulls the totals from each of the tables into one place, so it's easy to reference and see the most important numbers, such as costs and profit.

Transfer the functions you used in each table to the "Profit Summary" table.

Use absolute cell references so they update automatically based on contents of the tables.

Start with cost.

Then copy and paste the functions from the revenue and actual profit columns, and make them absolute.

Now that you've completed the cost, revenue, and profit calculations, you have a more detailed view of the sales data for the fundraiser, so far.

This is useful data for the fundraising committee because they might want to hold another fundraiser to sell more shirts, or try selling something else instead.

Now it's your turn: complete the Profit table using absolute cell references, subtraction formulas, and the MULTIPLY and SUM functions, complete the Profit Summary table with absolute cell references, and wrap the text in the header row.