130|PYTHON – Quick Calculation

BYU Student Author: @Hyrum
Reviewers: @MitchFrei, @Nate
Estimated Time to Solve: 10 - 15 Minutes

We provide the solution to this challenge using:

  • Python

Need a program? Click here.

Overview
You are a forensic accountant hired by a tech company to investigate potential financial irregularities. The company sells electronic devices both online and in-store, and they report their revenue in these two categories separately. However, there have been rumors that the company may be manipulating their revenue figures to mislead investors and creditors.

Your investigation begins with a review of the company’s financial statements. You discover that the total revenue reported for each category is suspiciously consistent from quarter to quarter, and there are no explanations provided for any significant fluctuations. You suspect that the company may be inflating their revenue figures by fabricating sales transactions. You’re able to get ahold of their financial statements and transactions and have uploaded the information into two pandas dataframes.

Instructions

Your task is to write a Python code that takes the total revenue from the income statement data frame and compares it with the total transactions the company has listed in the transactions data frame. Use an conditional statement to print out whether or not the company’s transaction total is equal to the amount listed on it’s income statement. Copy and paste the following code into your python environment.

Copy and Paste This Code
import pandas as pd 

data = { 
    'Transaction': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15], 
    'Description': ['Sold high-end laptops to a corporate client', 'Sold projectors to a school district', 
                    'Sold office printers to a law firm', 'Sold desktop computers to a small business', 
                    'Sold gaming laptops to an individual', 'Sold video editing workstations to a media company', 
                    'Sold wireless earbuds to an online retailer', 'Sold smart thermostats to a property management company', 
                    'Sold USB flash drives to a college', 'Sold portable chargers to a tech company', 
                    'Sold digital cameras to a photography studio', 'Sold professional-grade monitors to a design firm', 
                    'Sold smartwatches to a fitness center', 'Sold wireless routers to a hotel', 
                    'Sold robotic vacuum cleaners to an online retailer'], 
    'Quantity': [100, 50, 20, 25, 10, 5, 50, 15, 100, 30, 40, 5, 50, 20, 10], 
    'Price Per Unit': [400, 5000, 1000, 1000, 1000, 5000, 50, 200, 15, 70, 500, 10000, 150, 500, 150], 
    'Total Price': [40000, 250000, 20000, 25000, 10000, 25000, 25000, 3000, 1500, 2100, 20000, 50000, 7500, 10000, 1500], 
    'Payment Method': ['Wire transfer', 'Invoice', 'Credit card', 'Invoice', 'PayPal', 'Check', 'ACH transfer', 'Invoice', 'Credit card', 'Wire transfer', 'Invoice', 'Check', 'ACH transfer', 'Invoice', 'PayPal'] 
} 

Transaction_df = pd.DataFrame(data) 

revenues = { 
    'Electricity sales': 500000, 
    'Total Revenues': 500000 
} 

expenses = { 
    'Cost of goods sold': 100000, 
    'Operating expenses': 200000, 
    'Depreciation expense': 50000, 
    'Interest expense': 10000, 
    'Total Expenses': 360000 
} 

net_income = { 
    'Net Income': 140000 
} 

df_revenues = pd.DataFrame(list(revenues.items()), columns=['Item', 'Amount']) 
df_expenses = pd.DataFrame(list(expenses.items()), columns=['Item', 'Amount']) 
df_net_income = pd.DataFrame(list(net_income.items()), columns=['Item', 'Amount']) 

income_df = pd.concat([df_revenues, df_expenses, df_net_income], keys=['Revenues', 'Expenses', 'Net Income']) 
Suggestions and Hints
  • Use the .sum() method to get the total of all the transactions
  • Use the .loc() method to retrieve the revenue.
  • Extract the ‘Amount’ value from the ‘Total Revenues’ row by using the .values method after you extract the row.

Solution

Solution Code
# compute the sum of the 'Total Price' column in Transaction_df 
total_revenue = Transaction_df['Total Price'].sum() 

# retrieve the 'Total Revenues' row from income_df 
revenues_row = income_df.loc[income_df['Item'] == 'Total Revenues'] 

# extract the 'Amount' value from the 'Total Revenues' row 
income_total_revenues = revenues_row['Amount'].values[0] 

# calculating the difference between the two totals 
difference = total_revenue - income_total_revenues 

# compare the two totals and print a message 
if total_revenue == income_total_revenues: 
    print("The sum of the transactions matches the total revenue in the income statement.") 
else: 
    print(f"There is a discrepancy between the sum of the transactions and the total revenue in the income statement. The difference is {abs(difference):,.2f} dollars.") 

Solution Video: Challenge 130|PYTHON – Quick Calculation

This is a good challenge for refreshing knowledge on calling DataFrames and the arguments you use in various df functions. It is meant to be a quick challenge but can take much more time to remember these functions. The first section, totaling the revenue in the transaction df was simple, but I needed to visualize the income_df before I could reference the .loc for ‘Item’ and ‘Total Revenues’.

130_1

After this, I was able to use .loc to get the correct row and extract the value from the ‘Amount’ column and create a simple if statement.

Thanks for the helpful challenge, Hyrum!

I am not very good with python (I have only had brief exposure to it). Even though I could probably figure out this problem if I really tried, it would take me a lot longer than most people. I decided to try using Chat GPT to solve it. On my first couple tried, Chat GPT 4.0 kept erroring out, which was unexpected. I then tried using a custom GPT that someone built specifically for python, and was able to get some code that ran. After pasting my code into a python compiler, I noticed the discrepancy outputted was incorrect. Chat GPT had changed some of the “starting code” that I gave it. After fixing this, I was able to get the correct difference number of 9,400