Gross Profit Index (GPI) is a financial ratio that measures the relative change in gross profit over a period of time. It is calculated by dividing the gross profit of the current period by the gross profit of the base period. A GPI greater than 1 indicates an increase in gross profit, while a GPI less than 1 indicates a decrease in gross profit.
Formula
The formula for calculating GPI in Excel is:
where:
- is the gross profit of the current period, which is the difference between the revenue and the cost of goods sold (COGS) of the current period.
- is the gross profit of the base period, which is the difference between the revenue and the cost of goods sold (COGS) of the base period.
Procedure
To calculate GPI in Excel, follow these steps:
- Enter the revenue and COGS data of the current period and the base period in separate columns. For example, enter the revenue data in column B and the COGS data in column C.
- Calculate the gross profit of the current period and the base period by subtracting the COGS from the revenue in each period. For example, enter the formula
=B2-C2in cell D2 to calculate the gross profit of the current period, and enter the formula=B3-C3in cell D3 to calculate the gross profit of the base period. - Calculate the GPI by dividing the gross profit of the current period by the gross profit of the base period. For example, enter the formula
=D2/D3in cell E2 to calculate the GPI. - Format the GPI as a percentage by clicking the Percentage button on the Home tab in the Number group.
Example
Suppose a company has the following revenue and COGS data for the year 2023 and 2024:
| Year | Revenue | COGS | Gross Profit |
|---|---|---|---|
| 2024 | $120,000 | $80,000 | $40,000 |
| 2023 | $100,000 | $70,000 | $30,000 |
To calculate the GPI for the year 2024, we can use the formula:
This means that the gross profit of the company increased by 33% from 2023 to 2024.
The following table shows the Excel calculation of the GPI:
| Year | Revenue | COGS | Gross Profit | GPI |
|---|---|---|---|---|
| 2024 | $120,000 | $80,000 | $40,000 | 133.33% |
| 2023 | $100,000 | $70,000 | $30,000 |
Alternative Approaches
There are some alternative approaches to calculate the GPI in Excel, such as:
- Using the
SUMPRODUCTfunction to calculate the gross profit of each period in one formula. For example, enter the formula=SUMPRODUCT(B2:B3,-C2:C3)in cell D4 to calculate the total gross profit of both periods, and then divide the gross profit of the current period by the total gross profit to get the GPI. For example, enter the formula=D2/D4in cell E2 to calculate the GPI. - Using the
GROWTHfunction to calculate the GPI directly from the revenue and COGS data. For example, enter the formula=GROWTH(B2:B3,C2:C3)in cell E2 to calculate the GPI. Note that this function requires that both the revenue and COGS data are positive and non-zero.
