I’ve been a dilettante in eBay sales since college and, wanting to add a little more rigor to what I sell and how to price it, looked to the spreadsheet for guidance.
If you’ve ever sold online before, you know the cost of shipping is literally a “make-or-break” input in profitability. Consumers more or less expect free shipping nowadays, so it’s easy not to factor this into the final tally and end up losing money (not to mention your precious time, which I am sure you would rather not spend in line at the post office).
Download the exercise file here
So, you want to “bake this into” the final selling price to some extent while still pricing the item at a level someone will actually pay for. What a dilemma! Let’s use Excel to plot a course forward.
Setting up the P&L
Even something as simple as shipping a package on eBay can include multiple costs and inputs. Let’s keep things simple here by focusing on the sales price, cost of item sold, and cost of postage. These will be our “variable input” cells that can change and drive the rest of the model.
We’ll also add some “fixed costs” for the cost of packaging supplies such as a mailer, paper and ink.
Our final “P&L” will look like this. Each calculated cell’s formula is displayed next to it, courtesy of the FORMULATEXT()function:
Expecting to see cell references and not words in cells B6and B8? I named these cells SalesPrice and PostageCost, respectively. You can name a cell by clicking inside the box to the left above the worksheet and typing over that cell’s respective reference (B1, in this case).
Double-check and toggle between these named cells by clicking the drop-down button in this menu:
Our very simple shipping model is set up. We want to experiment with what levels of pricing and shipping costs lead to a profitable outcome. We could of course just do this by keying in figures of interest and seeing “how the cookie crumbles:”
However, this feels like we are “walking and chewing gum,” by trying to balance the competing interests of sales price and cost of postage. There is a lot of information to keep in mind as we enter in new data and try to memorize the results as we compare them.
It’s like we want to compare several combinations of each of the two at once!
That’s precisely what we are going to do, using two-way data tables. This is a stalwart Excel tool for financial modeling — which is a fancy term to describe what we are doing by trying to make a buck on eBay!
Preparing the data table
A data table will allow us to compare what profits result from various combinations of price and postage costs. Let’s build our table in cells A19:F24. Across row 24 we have various potential prices. We can put these in any interval we want, but it’s typical for prices to be in nice round numbers, so we’ll do intervals of five.
Shipping costs go down column A. We’ll do these in intervals of three to reflect the wider likely values of postage costs.
Then we have our value of interest, profit, in cell B15 (and yes, I named it!).
What we are going to do next is to “tether” this table to our above model such that each combination of values is “plugged into” the model and we get a resulting answer inside the table. First things first — locate the data table by heading to Data in the home ribbon and selecting “What-if Analysis.” Data Table will be the final selection in this drop-down.
Have cells A19:F24 selected for the next part.
Now we want to “tether” these two values to the table. Remember that sale prices go across the row and postage costs down the column, translation: cell B1 is our “Row input cell” and cell B2 is our “Column input cell.”
Fantastic! Now we have the resulting profit of each given combination of price and postage.
Conditional formatting to top it off
Why don’t we go ahead and make this table “pop” by applying conditional formatting. You can do this by heading to Conditional Formatting on the Home tab of the ribbon, selecting “Highlight Cells Rules,” selecting “Less Than,” and formatting cells that are less than 0 as red. This will highlight the unprofitable combinations of profit and postage.
Cleaning the corner
Remember cell A19? That’s our “plug” value that isn’t really doing much — we are interested in the profit values inside the table. We can’t delete this number because that will “unlink” our model. What we can do is reformat it so it’s blank.
To do that, click inside the cell, then select Ctrl + 1 on your keyboard. Go to “Custom” formatting and create a format type ;;;. Hit OK and you will see our output is gone, but we’ve retained the cell reference.
Doing it with modeling
Not only is this exercise something that can be applied to real-life scenarios and something that I personally will be using to estimate eBay profits, it shows the real power of Excel in designing and innovating with data. Setting up a financial model like this in R is possible but it just wouldn’t have the “pop” that we get from visually calculating our inputs, outputs and combinations thereof. That’s why of all the use cases of Excel, financial modeling may have the best staying power and is an area to check out if you want to improve your Excel skills.
For this, I would recommend Financial Modeling in Excel for Dummies by my friend Danielle Stein Fairhurst. Check out my review here.
Now, get out there and sell, and remember me when you’re a retail mogul!
Leave a Reply