Master Stock list & Order spreadsheet assistance.

Hi,

Please bare with me as I try and explain , it's nearly midnight here in the UK and I've hit a brick wall!

I run a Fudge company on behalf of my mum who is fundraising for her Multiple Sclerosis treatment abroad next year. Basically we have created the fudge company to help as fundraiser towards the total medical costs etc.

I have created a spreadsheet which I aimed to track how much stock we had and to track how many order's we've had and to deduct the order's off the master stock tab. I've attached the spreadsheet so that someone could have a look while reading this thread in the hope of making sense of it.

We sell fudge in 2 types (100g bags & 200g blocks) hence the 2 columns.

Master Stock Tab - This tab is what it says.
- 'Physical stock' is what we have, after we've done a stock take we will put the stock numbers in here.
- 'Current Stock' is where I've hoped all the equations would total up to. So 'Current Stock' = 'Physical Stock' -'Month Total' (In OCT bags tab)

OCT Bags / October Bags
This is where I'd hope we would be able to track our stock sales. For example in the spreadsheet you can see under After Eight & 15/10 I've put a 5 to simulate we sold 5. The month total is 5.

I would also have a OCT Blocks / October Blocks , November, December, January etc etc, which will all be in the same format as the 'Oct BAGS'

My only problem that I've hit a brick wall is that, we are constantly making fudge so we would constantly be adding to stock and the physical stock column would be constantly changing, which would constantly change the current stock tab. Now I believe that once I've got several month's of tabs there and multiple sales of 'After Eight' for example, it's going to be come very hard to manage because the Physical Stock will constantly be changing which will affect the current stock but from all the 'Month Total' columns, the only way I can see this all working is by constantly adding to 'Physical Stock' instead of just changing '7' to '4' or '9' when we make more Fudge etc.

We sell online and market stalls hence one of the reasons why data will constantly be changing to keep track of what we have etc

Is there any was we can simplify things or am I making it sound way to complicated than it actually is? I hope the above makes a little bit of sense, I'm writing this as I'm about to nod off!!

Thank you very much in advance for any help,
Regards
Josh

Here's a setup where you have monthly sheets that feed the production (rows 4:39) and sales (rows 42:77) of fudge to sheet 1. That would allow for weekly details and an overall cumulative record. The formulas are very simple as in the following which keeps track of physical stock:

Formula:

Please Login or Register  to view this content.

You would just keep adding months i.e. 'Sept 16:Feb 17'!K4 as you go along.
Let me know if you have any questions.