Modeling Revenue and Profit (GHD035)

After yesterday’s card reading, I got to thinking about the products I sell on Amazon. Currently, there are only two: the ETP Small Notebooks and the even smaller 4×6 StickyPads. I knew that they were somewhat profitable, having run the numbers before using my estimator spreadsheet. However, that spreadsheet wasn’t designed for profitability over time. Today I made a new private spreadsheet for managing that information, which fits nicely under my make a neat business Groundhog Day Goal category.

The Spreadsheet

As this spreadsheet contains production information, I’m not sharing it as I did with the other sheet, but I can describe how it works! This spreadsheet is for physical goods sold on Amazon, using Fulfillment by Amazon to handle shipping of product. I also have a Shopify store, but it is only for digital goods at this time. Here’s what it looks like, reformatted to fit the screen:

Dave's Magicial Pricing Worksheet Each entry is one long row, but I’ve split it into a a stack so it’s easier to see what each column is.

A: Reference Info

I use this area to keep track of monthly amazon costs.

  • I have a “professional seller” account, which costs $39.99/month but also waives a $1/item LISTING FEE; If you are selling more than 40 items a month, then this is worth it. There might be additional features that come with it but I forget what they are.
  • I note the AMAZON REFERRAL FEE (15% for goods sold in the Office Products category).
  • I list both FULFILLMENT BY AMAZON WAREHOUSE STORAGE RATES that are calculated based on cubic feet used and time of year. It’s more expensive to maintain warehouse space during the holiday season.

B: Product Run Pricing, Profit, and Manufacturing Cost

I am organizing each row by production run, which is when I restock an item. The fields marked EDIT are where I can set prices, quantities, and variable costs, and ultimately I can use it to calculate profit and estimated revenue based on historical sales data.

  • Set Ret Price is the main “what if” variable, allowing me to determine if a run is profitable or not. With my cost structure, I know I can’t sell for less than about $9 and make a profit, so finding ways to balance the other variables in production is key to maximizing gain and customer value.
  • Sales/Week is based on historical data that can be pulled out of the Amazon Seller’s Central inventory management tools. I divide this number by 4.35 to get a feel for how many months a production run will take to sell-through, which I can use to estimate revenue and profits by month.
  • units, print, ship are the costs associated with manufacturing a product and shipping it to Amazon’s warehouses. The number This produces unit cost of manufactured good which is used in calculating profitability.

C: Amazon Fees associated with the Product Run

The last section continues from the previous data, and is for calculating fees that Amazon charges. There are three sets of costs: order handling, item handling, and warehousing.

  • Amazon Referral Fee is a percentage of the retail price of sale, the cost of selling a product on the Amazon website. In the case of office products, their fee is 15%. It varies by the type of product.
  • FBA Order Handling is the fee associated with Amazon accepting payment from a customer and handling the transaction.
  • FBA Pick is the warehouse fee for fetching your item. This cost varies by the size of object.
  • FBA Weight is the fee associated for having to lift the item. Heavier objects cost more than light ones.
  • Crate Volume is in cubic inches, and is the physical volume taken up by product in the warehouse. This number is obtained by multiplying the dimensions of a shipping carton (crate volume) with the number of shipping cartons (crate num) to get cubic feet.
  • Estimated Warehouse Cost of Storage is calculated based on the historical amount of days it takes to sell-through a production run times the daily cost of warehouse storage divided by two. This gives me a sense of the total cost of warehousing fees over the life of it being in-stock.
  • Unit Warehouse Cost of Storage is estimated by assuming that the EST WHCOS can be divided by the number of units in the production run.

Stuff Learned and GHDR Points Earned

With this spreadsheet, I can now make more informed guesses on pricing based on production run costs, and forecast profits based on historical data. Hopefully I haven’t made a mistake in my calculations. This is a big visualization win for the “creative business” goal; I’ve been putting it off for a couple of years because I wasn’t sure how to put it together, but again I am reminded that trying stuff out and working through the problems is never as awful as my lazy lizard brain whispers it to be.

PTS DESCRIPTION
10 Creative Business Result: Financial Analysis Made
5 Helped myself with some past result work (the old spreadsheet)
3 Tried something I wasn’t sure would work!
3 Overcame resistance and worked on this thing
2 Posted words on this website!
2 Made a model to help clarify decision making

That’s 25 points. A marginally good day! I also spent about 5 hours on this plus the blogging, which uses up a lot of the energy I had for doing work today. I’d like to try to squeeze out some paying work next if I can apply mindfulness kickstarting tricks like (1) clear the mind of emotion then (2) pick 30 seconds of gathering work to (3) answer a question or set-up a question that can be resolved quickly. I can feel my focus disintegrating rapidly as I type this, possibly the result of mental fatigue, dehydration, or side effects of the low calorie diet I am trying to start today. Wish me luck!


About this Article Series

For my 2016 Groundhog Day Resolutions, I'm challenging myself to make something goal-related every day from February 2nd through December 12. All the related posts (and more!) are gathered on the Challenge Page.

0 Comments

A message from Dave:

I believe we all benefit when we respectfully share our perspectives on common experiences. My house rules are "please be respectful of divergent views" and "enjoy the flow of ideas!"

Leave a reply

Your email address will not be published. Required fields are marked *

*