203|PYTHON – COGS Costing

Challenges
, , ,
1

BYU Student Author: @Trent_Barlow
Reviewers: @Marta_Ellsworth, @Millie_K_B
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • Python

Need a program? Click here.

Overview
You are doing bookkeeping work on the side to put yourself through college. One of your clients is using the Average Cost Methods for calculating cost of goods sold (COGS) and wants you to help him see how his profitability changes over the year. You determine the best way to do this is with Python.

Instructions

  1. First, read in the Challenge203_Sample_Transactions.csv file and make sure to parse the “Date” column.
  2. Sort the data frame by date and create a new index based on this sorting. The earliest date should be first, the latest date should be last.
  3. Add a new column called Is_Sale if the Type is equal to ‘Sale’. Make it a Boolean data type. Then make a new column called Revenue equal to the revenue taken in by each sale. For purchases, show Revenue as 0. Hint: If you multiply any number by a Boolean, it will return the number if the Boolean equals True, and 0 if the Boolean equals false.
  4. Using iterrows or a for loop using .index, create 3 new columns: Running_Quantity, equivalent to the amount of inventory on hand at the date of the row; Running_Total_Cost, equivalent to the total cost of all inventory on hand at the date of the row; and COGS, equal to the Cost of the Goods Sold if a Sale, and equal to 0 if a Purchase. Hint: the PricePerUnit column is the sale price per unit if the Type is “Sale”, but is the cost the client paid to purchase them if the Type is “Purchase”.
  5. Create three more new columns: Profit; equal to the profit of that transaction (Revenue – COGS); Profit_Percent (Profit/Revenue); and Running_Profit, equal to the sum of all Profit made before that date.
  6. Filter the data frame to only include the sale transactions.
  7. Make a matplotlib visualization with Running_Profit as one y axis, Profit_Percent as the other y-axis, and Date as the x axis.

Data Files

Solution

Challenge203_Solution.ipynb
Solution Video: Challenge 203|PYTHON – COGS Costing