Evaluating the Profitability of Products Using Excel
For this example, we are given a list of products, their sales prices and cost prices and are asked to design a formula that would determine the profitability of eash item, thus giving us a quick and easy insight into which products are the most and least profitable. To achieve this goal, we will need to use conditional formatting. This tip is for Excel 97, 2003 and 2007.
How To Determine the profitability of a group of products
- In a new worksheet, type the cost of each product in column B and the corresponding sale price in column C.
- Select cells D2:D6 and type the following formula: =1-(B2/C2).
- Press Ctrl+Enter.
- From the Format menu, select Conditional Formatting. In Excel 2007, Conditional Formatting can be found from the Home tab. Click it and then click on New Rule.
- As Condition 1, select Formula Is and type the following formula: =D2=max($D$2:$D$6). In Excel 2007, Click on “Use a formula to determine which cells to format” and then enter the above formula.
- Click Format, select the Patterns tab, choose a color, and click OK. In 2007, click Format then Fill to choose a color. What the above rule does is color the the highest profit margin in the list.
- If you haven’t formatted the cells as percentage, you can do so now.
How to determine profitability
