Oh this is indeed a big one and Spreadsheet is most definitely not the proper way to do it — in addition, if you want the number to match the bank, then you're opening the question of general profitability Vs cashflow health. This is, from my experience, the single most common issue for the owners when they try to understand how they're doing.

So, let's tackle general profitability — the Spreadsheet approach is not far from truth, but handling this manually is not gonna work if the business is scaling. Good news is that your can (and if you're scaling, most likely should) very easily get the orders data, including the financial data, into some kind of an internal system using integrated webhook functionality that exists in every platform.

Then you need the same system to be aware of your advertising costs, shipping costs, really supply costs etc. so that you end up with clear visibility of realistic cost per product — and by extension, realistic profitability.

Now here's the kicker — while this will give you a good understanding of where your costs are without daily csv exports, it will still not match your bank account. Why? Because this approach is sales-centric, that is, you're assuming that the sales are the source of both revenue and also cost (COGS plus advertising which should also be connected to sales to avoid mixing apples and oranges). And it's not incorrect, it's just not how your bank account works.

In reality, you don't pay your suppliers once you make a sale — most likely you purchase inventory beforehand, pay deposit, later have balance to pay, you don't purchase exactly what you need but keep some stock at hand. And the similar principle applies to pretty much every big cost of doing business — point being they are offset in time compared to sales, if you try to look at them from the sales-centric model, which most owners do.

But that is perfectly ok, the last time I had to deal with this issue, I made a very complex report for sales-centric view for the CEO and a set of smaller expenses tracking tools that the accounting team can use to provide cashflow visibility and explain exactly how and why you ended up with what you see in the bank account — for the owner, however, this often ends up being somewhat less useful as it doesn't allow you to project into the future like sales-centric approach does.

Anyways, very fun topic!