Computer, do this.

My thoughts, ideas and processes with computing

Home

About

Normans

My Github

Pieces of Eight & Spreadsheets

Posted: 2017-11-19 | Last updated: 2017-11-19

The Problem

Working for an eCommerce business, I am often tasked with comparing prices. My day to day involves a lot of spreadsheets. These spreadsheets are used to ensure that prices are as correct as can be.

Anyone who has created a spreadsheet with around 50 tabs and many thousands of calculations over tens of thousands of rows and hundreds of columns knows that this can become a cumbersome endeavour. Not to mention the task of trying to convey this mess of numbers and grids to others.

There must be a better way...

Stipulations

Prices we have on the website need to;

Those points are just the basics, there are many variables involved in pricing a product. There are also a lot of products (tens of thousands) spanning across multiple websites, categories, product types, suppliers... the list goes on.

Process

Added to all of this is the fact that prices need to be checked often and quickly. Here's a scenario that often appears;

  1. We get a new price list from a supplier, if we are lucky it is in the form of a spreadsheet.
  2. We have a set time period before these prices take effect.
  3. We need to change the prices on the website before a set time, making sure they all add up and we, the business as a whole, are making money.

Possible current solutions

bulb-2928829_640

Solution 1: Let's start at the top and go through these products one by one in the admin back-end of the website.

Solution 2: Let's export the appropriate products, match them up with the products in the supplier's price list and enter each price one by one.

Solution 3: Let's export the products, as above. Then use a

INDEX(list-of-prices, MATCH(sku, list-of-skus, exact-match))

formula to match a product's SKU with the price list and pull in appropriate prices, then use simple formulas to add appropriate margins and other factored costs (delivery etc).

Hopefully anyone can see that of these 3 solutions, the third is the most efficient, but it still has problems.

What if we are using a different SKU to the suppliers?

This unfortunately happens. In an ideal world it would not but if we get a SKU which has a '/' character in it, we need to replace it with an '_', '-' or nothing. This is totally avoidable with proper preparation and coding but that's another story.

It also may be as simple as the supplier's updating/changing their SKU in this new price list. The thing to do here is to use the formula here to match all we can, and then manually go through the ones left over.

So there's the solution, what's your point?

Solution 3, above is what we currently do when updating prices from our suppliers. This works but still involves a lot of manual intervention and time.

Not mentioned above is also the situation of comparing prices with our online competitors. Aside from collecting the data, which is another matter, we need to match SKUs, take in to account other charges and consolidate the important parts of this information in to an easy to understand report at the end.

The computer can do the work, we just lack the instructions to give to the computer! This is where my light bulb moment occurred.

"Pieces of Eight"

An idea I have mulled over for a long time is a project I have initially dubbed "Pieces of Eight" (because I like pirates and gold!).

At the moment this program presents the user with a form to;

  1. Create a new "Company",
  2. Import a "product" file (.csv) in to the object instance,
  3. Reports the read in data and current profit margin as a percentage.

Example of Pieces of Eight program

Not terribly useful currently, my functionality ideas are;

My biggest hurdle with these ideas, as I foresee, is going to be matching SKUs with what the other company's are using. Something I need to do research on when I am there.

Currently I am working on this outside of work, so weekends and an hour or two most nights but if I can get it right, it could save myself and my colleagues hours and hours of time at work.