Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Find the profit and loss percent in the given Excel sheet using Pandas
Profit and loss percent is an important financial metric that helps in analyzing the profitability of a business. It is calculated by finding the difference between the total revenue and total cost, and then dividing that value by the total cost. In this technical blog, we will learn how to find the profit and loss percent in a given Excel sheet using Pandas. We will be using the same Excel sheet that we used in our previous blog post on finding profit and loss.
Algorithm
Import the Pandas library and read the Excel sheet using the read_excel() function.
Apply the following calculations to each row to determine total income and total cost −
Total Cost = Total Units Bought * Cost of Each Unit
Total Revenue = Total Sold Units * Selling Price of Each Unit
Use the following formula to determine the profit or loss for each row −
Profit/Loss = Total Revenue - Total Cost
Calculate the profit and loss percent for each row using the following formula −
Profit/Loss Percent = (Profit/Loss / Total Cost) * 100
Setup
To handle excel files in Python using 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.
To download the Excel spreadsheet used in the exercises below, please check out this link
Example
import pandas as pd
# Reading the Excel sheet
df = pd.read_excel('sales.xlsx')
# Calculating the Total Cost and Total Revenue
df['Total Cost'] = df['Units Purchased'] * df['Unit Cost']
df['Total Revenue'] = df['Units Sold'] * df['Unit Price']
# Calculating the Profit/Loss
df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost']
# Calculating the Profit/Loss Percent
df['Profit/Loss Percent'] = (df['Profit/Loss'] / df['Total Cost']) * 100
# Displaying the final DataFrame
print(df)
Output
Units Purchased Unit Cost Units Sold Unit Price Item Name Total Cost Total Revenue Profit/Loss Profit/Loss Percent
50 5.00 40 9.00 Apples 250.00 360.0 110.00 44.000000
100 3.50 80 7.00 Oranges 350.00 560.0 210.00 60.000000
25 12.00 20 15.00 Pineapples 300.00 300.0 0.00 0.000000
75 1.75 60 3.50 Bananas 131.25 210.0 78.75 60.000000
200 0.50 180 1.25 Carrots 100.00 225.0 125.00 125.000000
450 2.00 120 4.50 Potatoes 900.00 540.0 -360.00 -40.000000
40 8.00 30 12.00 Avocados 320.00 360.0 40.00 12.500000
80 1.50 70 3.00 Tomatoes 120.00 210.0 90.00 75.000000
300 20.00 25 25.00 Mangoes 6000.00 625.0 -5375.00 -860.000000
60 4.00 45 8.00 Grapes 240.00 360.0 120.00 100.000000
The Excel file "sales.xlsx" is read for data using the pandas read_excel() function.
By conducting arithmetic operations on two of the columns that are already present in lines 6 and 7, two additional columns are added to the DataFrame which are named “Total Cost” and “Total Income”.
The profit or loss is calculated by dividing the total cost by the total revenue. Following that, the benefit/misfortune is figured by partitioning it by the whole expense, then increasing the outcome by 100 to get the benefit/misfortune rate.
The terminal outputs the resulting DataFrame.
Applications
If you require to calculate profit and loss data and display it in a tabular format, the aforementioned code snippet can be useful, i.e. Businesses trying to track expenditures and evaluate overall performance can find it useful.
With the assistance of the tabular format, businesses can easily compare and assess their profit and loss numbers over a certain time, enabling them to confidently decide on their financial plan.
The code snippet is a versatile tool for financial analysis since it may be altered to satisfy the particular needs of a business and therefore this code may also be used by individuals to monitor their personal finances, including their assets and outgoings.
Conclusion
Pandas offers a dependable and effective method for figuring out an Excel sheet's profit and loss percentage. Businesses and individuals may quickly get useful insights into their financial performance by using Pandas to read in and change the data. Financial analysis requires the capacity to determine profit and loss percentages, and using Pandas helps expedite and improve this process. As a result, Pandas is a useful tool for anybody handling financial research and data.
281 Views
