Join Rudolph Rosenberg for an in-depth discussion in this video Calculating gross margin, part of Financial Analysis: Analyzing the Bottom Line with Excel.
…Now that we have retrieved all of our cost per unit,…we need to calculate what is the total amount of cost per order,…which is going to be dependent on the quantity of pens that we have sold.…So let's do that in a, an extra column column S.…So, let's call it total cost.…And let's just multiply the cost per unit by the quantity.…Now that we have calculated this, let's double-click on the bottom-right corner of…the cell again to apply it to all of our invoices.…
So now we have, for each of our invoices and for each of our products, the revenue…we have generated and the cost that we have paid to generate that revenue.…What we're left with is calculating the gross margin.…So, as we know, the gross margin is just revenue minus cost of production, so…let's add here an extra column called gross margin.…And calculate the difference between the two.…Equal.…Discounted price minus total costs, and let's press Enter.…
And again, we can double click or drag the formula down to the last invoice.…So, we've discussed a couple of times the usage of gross margin in dollar form and…
Also check out the companion course, Financial Analysis: Analyzing the Top Line with Excel.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Finding data points
- Prepping data
- Calculating standard cost and gross margins
- Analyzing overall gross margin performance
- Analyzing individual and overall expenses