Video savvy but Business… well… not so much? This might help… building a P&L in Excel

Hands up all those that remember Tandy Electronics, otherwise known in the US and worldwide as Radio Shack.

Today, both are a sad and sorry version of their past glories and indeed, not even really entities anymore. They are now just names and used for marketing purposes by the Kogan outfit, as is Dick Smith, another gone-by-the-wayside company from the glory days.

Back in the day though, there were more Radio Shack / Tandy stores than even McDonalds’, Radio Shack was the largest retailer AND mail order company in the world (allegedly) and the world’s largest accepter of credit cards.

Sadly, the company missed the mobile phone boom entirely through lousy business predictions and management, and very quickly whimpered off into the distance and never recovered.

The closest approximation today to the real Tandy Electronics as we knew it in Australia is JayCar. The major difference between the two is that Tandy also sold high-end stereo gear as well as CD and DVD players along with the electronic components, calculators, test gear, walkie talkies, scanners, radio controlled toys, batteries – you get the idea.

Oh and of course. Computers. LOTS of computers. The venerable TRS-80 (disparagingly called the Trash 80).

Now I mention Tandy not for any real nostalgia; I worked for them for 5 very good years and enjoyed every bit of it. But apart from allowing me to get a handle on the world of the personal computer from the very, very start, Tandy gave me one incalculable asset.

How to understand a P & L – a profit and loss report.

You see, store managers, we were paid on the bottom line. Make a profit, you got a goodly share; make a loss, you got nuffin’ – and it was cumulative so you had to pull it back.

I mention this here now as in my travels and discussions with people in the video business, it seems to me – and some have even told me – that while most are REALLY good at their particular craft (editing, shooting, FX, lighting, audio etc) when it comes to business basics like a P & L, it is a bit of a dark art.

So, on the basis that it might be useful to someone at least, here is a primer and quick how to.

The Top Line

The top line of a P & L shows income. In the case of my Tandy store, this was primarily retail sales although there was provision for sales of stock to other stores who needed something urgently.

For Australian Videocamera, this would equate to magazine subscriptions, website subscriptions, advertising revenue and a loose “other” (consultancy, contract writing etc) by way of comparison.

Next Line Down

This is the trickiest one and is abbreviated as COGS for Cost of Goods Sold. In the Tandy model, this was a % figure of the total retail sales (57%) and was calculated as the average across the board for all products sold in store. For inter-store stock transfers, 15% was taken off this (so you actually could still make a profit selling to another store when their own stock management processes were not up to snuff. In other words they were penalised at having to buy at a higher price than from the warehouse).

Tandy’s stock system was quite complicated (hence the average 57% figure), but if you could master it, it allowed you to work out the best products to sell for maximum profit. For example, selling a blank audio cassette complete with label, case etc might make you 42% margin, but selling the components individually could actually cost the customer less, but make you an average of 73% – WELL above the average 57%.

Your COGS would be the cost of any product you have to buy in that you resell, which might also include contract labour as well as physical product.

From here we can calculate the Gross Profit which is simply Sales minus COGS.

The Middle Line

This section includes all the costs of actually doing business such as wages, rent, fuel, advertising, postage, freight, power and water, registrations, stationery, travel costs, vehicle maintenance and so on. In other words, Expenses which can be fixed or variable month by month.

The Bottom Line

Finally we have the all-important Bottom Line, which accountants call the Nett Profit. In simple terms, this calculates Gross Profit, less Expenses.

Excel

All of these calculations used to be made on a large gridded sheet of paper with  pencils and rubbers and a calculator handy.

Then in 1979 two very smart fellers, Dan Bricklin and Bob Frankston, put their collective heads together and almost single-handedly kick started the personal computer revolution by creating VisiCalc, the “visible calculator”. (And I don’t care what Apple say here, without software the hardware was going nowhere).

VisiCalc immediately replaced the large gridded paper with an electronic version with 64 columns and 256 rows. Other similar programs quickly followed including Multiplan, PFS:Plan and more, then Lotus upped the ante with Lotus 1-2-3 which included integrated graphs and macros, as did Open Access, SuperCalc, WINGZ and lots of other later offerings.

Then Microsoft, smarting that Multiplan had been usurped, brought out the Big Daddy which is still here to this day and the undisputed King, Excel.

Lotus tried a short-lived fightback with Symphony, but by then Excel was far too entrenched and had morphed (by users) to include a database system and even at one place where I worked for a short period in the 90s, a word processor, I kid you not …

Today, Excel is an almost necessary tool for every business to use and I urge you if you have no knowledge of it AND are in any sort of business to go and try one of the many tutorials available (I’d recommend those at Lynda.com).

A sample of a P&L is shown here using a current month as a starting point (real numbers) and then projecting forwards over the 5 months to give a sales and costs template to work from, in order to reach a required end bottom line.

As time passes, the projected values for each month can be updated with the real figures letting you know in real time how the business is faring.

Even if you only understand the basics of Excel, this sort of spreadsheet can be put together in literally minutes as long as you know the base numbers for the current month (from invoices, receipts etc).

Here is a quick video showing that.

If the demand is there, I’ll put together a 10 minute tutorial showing each step. If you’d like to see this, just email me at david@auscamonline.com.

Be the first to comment

Start a conversation! Leave a comment here :)

This site uses Akismet to reduce spam. Learn how your comment data is processed.