This course will become read-only in the near future. Tell us at community.p2pu.org if that is a problem.

Calculate the Total Cost of Each Sale Made


Learn the VLOOKUP function

Now, we want to bring over our newly calculated product costs into the Sales spreadsheet, so that we know what the company spent to fill each order. To do so, we'll be filling in columns H and I in the Sales spreadsheet.

To calculate the Cost per Unit, we need to choose the appropriate cost, depending on the ID of the product sold. To do this, we'll be using the VLOOKUP function. This function looks up a given value in a table of values, and returns the corresponding result. It takes four arguments: the value to be looked up, the table to look up that value in, the number of the column (relative to the first) of the result to return, and a true/false argument having to do with what to do if the desired value is not found in the table. This last argument can mostly be ignored for now; you will usually just use "FALSE").
 
Put this formula in cell H6:
=VLOOKUP(D6,Cost!$B$8:$H$10,7,FALSE)
and press enter. This means that we are looking up the value in cell D6 (the product ID) in the table that is found in the Cost sheet running from cell B8 to H10. We use fully absolute references so that we can copy this formula to the rest of the column. Also note the syntax for referencing cells in a different sheet; as before, we could have written the "=VLOOKUP(" part of the formula, then used the mouse to choose the cells to refer to for the first two arguments. The third argument, 7, says to use the seventh column in the table B8:H10. Counting column B as "1", column H, holding the desired Total Cost value, is column number 7.
 
Copy the formula you just wrote, and paste it into the rest of the empty cells in the column, then calculate the total cost of each sale by multiplying the number of units by the cost per unit you just calculated. At this point, it might also make sense to calculate the total cost for the year, by summing the values in column I in cell I34, as you did in task 2. You should receive a value of $12,394.16.
 
Now that we have the Total Revenue and Total Cost for the year, we can calculate the company's profit margin in cell I35; simply divide the Total Revenue by the Total Cost. You should get a profit margin of 153%. In the next and final task, we'll learn how to break this figure out by month.

Task Discussion


  • Gareth Glynn said:

     I'm sure that VLOOKUP is a profoundly important tool, but the exercise made it mercifully easy
     

    on June 18, 2012, 4:50 a.m.