Q: Would numbers be good for stock control

Lee,

What is stock control?  Can you provide any description of what that entails?

logboys wrote:

 

Looking to setup some basic stock control, not sure if numbers would be any good?

Thanks

Lee

I think so. You could conditionally format cells to alert you to conditions that are important, such as re-order points. You could compute how many units of finished goods could be made from existing stock, etc.

Jerry

Hi Lee,

I assume you are asking about stock control for a warehouse or shop that sells to customers. The short answer to your question is yes. Numbers can handle such data processing. It would be good to hear from you about your intended work flow. How will you enter the data? At the end of each day? After an inventory of your stock? From invoices? From a list of customer purchases?

More information will help towards a solution.

Regards,

Ian.

Hi Lee,

An interesting and challenging problem. There are many ways to approach this with a spreadsheet. I am trying to get my head around the quantities involved.

If you buy logs by the truckload (lorryload in UK speak) and sell them in smaller quantities (for example a customer buys and takes a trailer load) how many trailers can you fill per lorryload? That will help you to calculate when to order in another lorryload.

If you buy kindling loose and sell it by the sack, how many sacks can you sell before you must buy (or chop) more kindling? How do you estimate the number of sacks if the kindling is loose? Or is the kindling the offcuts from logs?

You say you have three products. Kindling and logs are two, what is the third?

We could create a spreadsheet with either:

• three Numbers tables, one for each product. Then a summary table to show remaining stock of each kind; or

• a single sales table where you choose the product and quantity (perhaps from pop-up menus). Then three summary tables to pull each product into three lists showing stock remaining.

Either way, we can place an alert for you to order more stock. The power of Numbers lies in its ability to hold data in several small, discrete tables. Tables that do the work can be hidden from view. Tables that take input or show results can be on the "display" sheet. Linking between tables and sheets is easy.

Quantities sold will be important. For example a handful, sack, wheelbarrow, boot load, trailer load... and how they all relate to lorryloads or pre-packaged bags ordered in.

So many possibilities!

Thank you for exercising my brain. More information will lead us to a solution.

Regards from DownUnder,

Ian.

Hi Lee,

Ordering and selling by the sack makes this more straightforward. No need to calculate how many wheelbarrows equal one lorryload!

Here is a suggested layout of a Numbers document. This is one way. There are, no doubt other ways. Here is a screen shot of the Sheets Pane. Two Sheets. One Sheet to record individual sales per day. Another Sheet to keep a running total and show stock remaining.

Here is the 'Today' Sheet:

Column A of the 'Individual sales' table is a decimal time with a decimal point (.) instead of a colon (:). it won't work as a time value. It is just any easy way to keep some kind of record of when a sale was made. No big deal, I am trying to avoid the "quicksand" of how Numbers handles dates and times. Another way would be to use that column for the salesperson to insert their initials.

The 'Totals today' table adds the number of sacks sold. Formula in B2:

=SUM('Individual sales: sacks' :: B) then Fill Right.

At close of business each day, copy the three totals from the 'Totals today' table. Then delete today's sales on the 'Individual sales' table, ready for a fresh start tomorrow.

Go to the 'Daily total sales' table on the Summaries Sheet and Paste Values (not Paste, or that will insert the formulas, not the values).

I am having problems posting screen shots.I will try again in another reply.

Regards,

Ian

Hello again.

Here is the 'Summaries' Sheet.

At the end of each day, Paste Values into the 'Daily total sales' table (from the 'Totals today' table on the 'Today' Sheet). The bottom row of  'Daily total sales' is a Footer Row which will maintain the formulas as you add more rows. Formula in Column B of the Footer Row is =SUM(B) Then Fill Right. Totals are for the current winter.

The 'Deliveries' table is where you record sacks delivered by your supplier. Totals are for the current winter.

The 'In stock' table shows sacks in stock. Conditional formatting will show red text on a yellow fill when stock of any kind falls below the Reorder level. Change the reorder level to suit your turnover and the delivery time by the supplier.

I have placed the Numbers document in my Dropbox to help you get started:

https://www.dropbox.com/s/pgo19l2mvbpbtbf/logboys.numbers

Lee wrote:

Thanks again Ian from a cold wet UK

Lee, please send some rain over here.

Regards,

Ian.

logboys wrote:

 

If in any way i am able to leave feedback for you please let me know where and how.

The community experts are incredibly helpful, aren't they! I think they're in it mostly for the satisfaction of helping others (unless the "Lounge" is really special). But, in addition to Facebook and Twitter, there is one small and quick thing you can do here for Ian. As the person who asked the question you have the sole power to mark helpful answers by hitting the 'This solved my question' and 'This helped me' buttons below replies, thus awarding reputation points.

SG

Hi logboys:

As an alternative, it seems to me that your needs might be meet using the Numbers Checking Register Template and making a few tweeks to it. Like a checking account, money in, money out, wood in, wood out. I came up with this...

I used the reorganize feature to sererate the types of wood as an example, but maybe not necessary as there is the Quantities on Hand table doing that. Conditional formatting used to turn red the quantities 600 or below and the Time to Reorder note.

MSwint

Hi Lee,

Thanks for the greenie. After you try the document, let me know if it needs to be modified to suit your workflow. 

Happy Numbers!

Regards,

Ian.

G'Day Lee,

How is the Old Dart?

If you are seeing something like this,

my guess would be that the formula has been accidentally deleted. Click once on one of the totals that do work,

and you will see it selects the column in the table above,

and this appears in the formula box'

The formula is =SUM('Individual sales: sacks' :: C)

Now copy (command c) then click once on cell B2 and paste. That will put the formula in and adjust to column B.

=SUM('Individual sales: sacks' :: B) Press enter. Done!

If you like, you can lock a table. Select the table by clicking on its icon in the Sheets Pane to the left.

Menu > Arrange > Lock. The faint x marks show it is locked,

You can unlock it if you want with Menu > Arrange > Unlock.

Another enhancement is to add extra columns to the Reorder Level table on the Summaries Sheet. Then you will get an alert for each of the three types of firewood.

Stock on hand from last winter counts as a delivery for this winter!

Happy Numbering!

Ian.