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
