Calculate Profits and Returns on Investment

In this video, you and your group will use a formula to calculate the ratio between box office returns and budget for individual movies.

Watch this entire video first, then gather your group around one computer to complete the steps.

To compare returns on investment for movies in your data set, first calculate the profit.

Profit is the amount of money spent to make the movie -- your budget -- minus the amount of money made at the box office -- the revenue.

Only one person in your group should add these calculations to your spreadsheet, but all group members should assist at one computer.

First, create a new column and label it "Profit." Move your graphs around if you need to make room.

In this cell, you will subtract the movie budget from the box office revenue.

In the first cell beneath "Profit," type "equals." Select the adjacent cell in the "Box Office Revenue" column.

Type a minus sign, then select the adjacent cell in the budget column.

Press Enter.

The movie's profit automatically calculates in this column.

Change the format to currency.

Then, copy the formula to the entire column.

Select the cell with the formula and copy it.

Select the column header.

Paste the formula into every cell in the column.

Great! Sort the spreadsheet by profit.

Notice that some movies cost more money to produce than they made at the box office.

If a movie's budget was more than the amount it made, the movie had a negative profit, and lost money.

Now that you have the amount that each movie made after subtracting production costs, you can calculate the return on investment for each movie.

Create a column for Return on Investment, or ROI.

Create a formula that divides the profit by the budget.

Type "equals," then, select the first Profit cell.

Type a forward slash for divides, then select the first Budget cell.

Press Enter.

Now, the return on investment appears, but as a whole number.

Select the cell and change the format to percent.

Terrific! Copy the formula to the rest of the column.

Select the cell with the formula and use keyboard commands to copy.

Then, right click on the column header and select "Paste." The formula also pastes to the column header.

Change the top cell back to ROI.

Great! Once someone in your group has completed the ROI column, sort the sheet by column from high to low or low to high.

Which movies have a very bad return on investment?

Are there any patterns you notice right away?

With this data set, for example, there are several horror movies that had an excellent return on investment.

On the other hand, there are several biographies and dramas near the bottom of the list.

You can get some idea of the most and least successful movies from this data, but it is still difficult to tell what genres, actors, and directors do well.

In the next video, you will examine profit and returns on investment by genre, director, and cast members.

Decide with your group who will be responsible for analyzing genres, directors, and cast.

Now, it's your turn: Gather your group around one computer.

Create a column in the Movie Data sheet with a formula to calculate profit, or box office minus budget.

Copy the formula to the rest of the spreadsheet.

Then, create a column with a formula to calculate the return on investment for each movie.

Divide the movie's profit by the budget and change the format to a percentage.

Copy the formula to the rest of the spreadsheet.

Finally, decide with your group who will be responsible for calculating the return on investment for genres, directors, and actors.

In the next video, you will each analyze one of these aspects of the movie data.

When your group has finished calculating profit and ROI and you know if you are analyzing genres, directors, or actors, return to your computer and move on to the next video.