keeping track of metal usage

[Unhindered]

Hi guys, bit of a boring one! I work in a jewellers and they recently started an excel spreadsheet for me to input the metal weights each week and a seperate spreadsheet to put metal used for jobs etc.

It is a very long tedious process and I want to adjust it but wondered what everyone else does if they do this or have done previously? Thank you in advance!

[Gemsetterchris]

weigh the same metal at the end of the week?
As in clean stock metal & not finished items as you won't be taking into account filings ect.

Don`t forget to charge for faffing about on the spreadsheets!

[Unhindered]

haha! im on salary so can't charge extra unfortunately! I weigh the metal twice a week as requested because someone comes in on my day off so i weigh the day before and after. I also have to weigh the pot ive used after ive finished a job so i can work out how much i have used, but its the spreadsheets that are confusing me! They are very overcomplicated and don't make sense, so im basically just inputting information that relates to nothing! trying to work out how to make it relate

[pearlescence]

Spend hours on their silly spreadshit and explain why in detail when they wonder at nothing actually made - you'll save on metal weighing too!

[Aurarius]

I echo pearlescence's sentiments.
What is this weighing business all about anyway? Is it that they don't trust you not to walk off at the end of the day with some nice offcuts of gold wire stuffed into your trousers? I'd want to be told the reason for it personally if I was working for them. I'd object less to a CCTV camera in the workshop.

[metalsmith]

Its to keep staff busy when (as if) there's nowt better to do.


Start simple and add some small complexity

Input = Output

I = O

(Use row 1 for headers)

Put our starting Input, for arguments sake, in cell G2
There's nothing coming in from elsewhere, so humour me and set H2 to 0
Humour me further and set column A2 = G2+H2

The output stream is divided into:
B) Viable Product;
C) Useful/Useable Resource;
D) Reclaimable/Reuseable (filings etc); and
E) Loss, which maybe the trace material that is not reclaimed and/or cannot be accounted for in B, C, D or E

(we can put these items in these columns - put the headers in Row1)

At startout: A2 = B2 + C2 + D2 + E2

For the third (since headers are in row 1) and subsequent rows we need to account for Useful/Useable from our first row of numbers

In row 3, input = anything new, G3, plus anything left over from before, C2

A3 = C2 + G3

then the other cells should balance with A3 so

A3= B3 + C3 + D3 + E3

... and so on for additional rows

As a test that everything works ok, these all should balance on each line.

At the end of the period, there will be no input, so Gend = 0 and anything left over is effectively D +E.
If you have just closed the period for the sake of accounting and there is some C leftover, this can now be added to H2. You were probably wondering why I previously added nothing. It was a placeholder for this.

So now everything should now stack up.

Hope this is clear

[Gemsetterchris]

Clear as mud. Need to lie down now.:what:

[Unhindered]

haha! Wow that is quite something!

For now I have asked them to combine the two charts so it actually makes sense so that should help :D fingers crossed!

[metalsmith]

Clear as mud. Need to lie down now.:what:

Thanks Chris

Hopefully this will crystallise things

Example values:8622 Formulae:8623

Numbers you should input are in pale yellow. One sheet shows typical numbers and the second shows the formulae.
Rows are time units.. days / weeks whatever and units are um/ mg / g / kg / tonnes as you prefer, but keep it all constant and it should work fine.

[Gemsetterchris]

Your descriptions are no doubt very clear, just my brain has a built in defence mechanism against this technical stuff.:)

[metalsmith]

Your descriptions are no doubt very clear, just my brain has a built in defence mechanism against this technical stuff.:)

No problem, Chris; consider you've had a lucky escape!