Find the profit and loss in the given Excel sheet using Pandas

Pandas is a popular data manipulation and analysis library in Python that is widely used by data scientists and analysts. It provides several functions for working with data in Excel sheets. One of the most common tasks in analyzing financial data is finding the profit and loss in a given Excel sheet.

Setup

To handle Excel files in Python, you need to install the openpyxl dependency. To do this, open your terminal and type the command ?

pip install openpyxl

After successful installation you can proceed with experimenting with Excel files and spreadsheets.

Note: For this tutorial, we'll create sample data instead of using an external Excel file to ensure the code runs properly.

Algorithm

To calculate profit and loss using Pandas, we follow these steps ?

• Read the Excel sheet into a Pandas DataFrame using the read_excel() method.

• Calculate total cost and total revenue for each product.

• Subtract the total cost from the total revenue to determine profit/loss for each row.

• Analyze the overall profit and loss by summing the columns.

Basic Profit and Loss Calculation

The following example creates sample sales data and calculates profit/loss for each product ?

import pandas as pd

# Create sample sales data
data = {
    'Item Name': ['Apples', 'Oranges', 'Pineapples', 'Bananas', 'Carrots'],
    'Units Purchased': [50, 100, 25, 75, 200],
    'Unit Cost': [5.00, 3.50, 12.00, 1.75, 0.50],
    'Units Sold': [40, 80, 20, 60, 180],
    'Unit Price': [9.00, 7.00, 15.00, 3.50, 1.25]
}

df = pd.DataFrame(data)

# Calculate total cost and revenue
df['Total Cost'] = df['Units Purchased'] * df['Unit Cost']
df['Total Revenue'] = df['Units Sold'] * df['Unit Price']

# Calculate profit/loss
df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost']

print(df)

   Item Name  Units Purchased  Unit Cost  Units Sold  Unit Price  Total Cost  Total Revenue  Profit/Loss
0     Apples               50       5.00          40        9.00      250.00         360.00       110.00
1    Oranges              100       3.50          80        7.00      350.00         560.00       210.00
2 Pineapples               25      12.00          20       15.00      300.00         300.00         0.00
3    Bananas               75       1.75          60        3.50      131.25         210.00        78.75
4    Carrots              200       0.50         180        1.25      100.00         225.00       125.00

Filtering Profitable and Loss-Making Products

We can separate products into profit and loss categories using boolean indexing ?

import pandas as pd

# Using the same data from previous example
data = {
    'Item Name': ['Apples', 'Oranges', 'Pineapples', 'Bananas', 'Carrots', 'Potatoes'],
    'Units Purchased': [50, 100, 25, 75, 200, 450],
    'Unit Cost': [5.00, 3.50, 12.00, 1.75, 0.50, 2.00],
    'Units Sold': [40, 80, 20, 60, 180, 120],
    'Unit Price': [9.00, 7.00, 15.00, 3.50, 1.25, 4.50]
}

df = pd.DataFrame(data)
df['Total Cost'] = df['Units Purchased'] * df['Unit Cost']
df['Total Revenue'] = df['Units Sold'] * df['Unit Price']
df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost']

# Filter profitable products
profitable_products = df[df['Profit/Loss'] > 0]
print("Profitable Products:")
print(profitable_products[['Item Name', 'Profit/Loss']])

print("\n" + "="*40)

# Filter loss-making products
loss_products = df[df['Profit/Loss'] < 0]
print("Loss-Making Products:")
print(loss_products[['Item Name', 'Profit/Loss']])

# Calculate totals
total_profit = profitable_products['Profit/Loss'].sum()
total_loss = abs(loss_products['Profit/Loss'].sum())

print(f"\nTotal Profit: ${total_profit:.2f}")
print(f"Total Loss: ${total_loss:.2f}")
print(f"Net Result: ${total_profit - total_loss:.2f}")

Profitable Products:
   Item Name  Profit/Loss
0     Apples       110.00
1    Oranges       210.00
3    Bananas        78.75
4    Carrots       125.00

========================================
Loss-Making Products:
   Item Name  Profit/Loss
5   Potatoes      -360.00

Total Profit: $523.75
Total Loss: $360.00
Net Result: $163.75

Summary Statistics

Generate comprehensive profit/loss statistics using Pandas built-in functions ?

import pandas as pd

# Create expanded dataset
data = {
    'Item Name': ['Apples', 'Oranges', 'Pineapples', 'Bananas', 'Carrots', 'Potatoes', 'Avocados', 'Tomatoes'],
    'Units Purchased': [50, 100, 25, 75, 200, 450, 40, 80],
    'Unit Cost': [5.00, 3.50, 12.00, 1.75, 0.50, 2.00, 8.00, 1.50],
    'Units Sold': [40, 80, 20, 60, 180, 120, 30, 70],
    'Unit Price': [9.00, 7.00, 15.00, 3.50, 1.25, 4.50, 12.00, 3.00]
}

df = pd.DataFrame(data)
df['Total Cost'] = df['Units Purchased'] * df['Unit Cost']
df['Total Revenue'] = df['Units Sold'] * df['Unit Price']
df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost']

# Generate summary statistics
print("Profit/Loss Summary Statistics:")
print(df['Profit/Loss'].describe())

print(f"\nProducts in Profit: {(df['Profit/Loss'] > 0).sum()}")
print(f"Products in Loss: {(df['Profit/Loss'] < 0).sum()}")
print(f"Break-even Products: {(df['Profit/Loss'] == 0).sum()}")

Profit/Loss Summary Statistics:
count      8.000000
mean      46.843750
std      141.967346
min     -360.000000
25%       32.500000
50%       84.375000
75%      117.500000
max      210.000000

Products in Profit: 6
Products in Loss: 1
Break-even Products: 1

Applications of Computing Profit and Loss Using Pandas

• Financial data analysis ? Businesses can analyze their financial data to determine profit and loss for different products and services.

• Investment analysis ? Investors can examine company financial data to determine profitability.

• Business forecasting ? Companies can predict future profits and losses by analyzing historical data patterns.

Conclusion

Pandas provides powerful tools for calculating profit and loss from Excel data through simple DataFrame operations. By combining total cost and revenue calculations with filtering and statistical functions, you can gain valuable insights into business performance and make data-driven decisions.