Project to identify how to extract maximum profit from stock requiring part replacement

[Rhothgar]

Hi

Not sure if this is the section to post in as I am looking for general advice to begin with and I will follow wherever it takes me.

I have an item which we buy in that is a finished item but 2nd hand so they can be complete or they can be missing parts or have broken parts. The reason will usually vary within a set of boundaries that rarely change.

The project I am looking to build and it may be way beyond my capabilities anyway is to design a spreadsheet or database or whatever that will allow me to enter an approximate sale price of the finished item and the purchase price and list any parts it may need.

As I buy a lot of these items, some without faults, some without, I am looking to work out whether it is better to order replacement parts at a set cost (which varies depending on the part(s) required) or whether it is better to strip one to repair the others which may have other defects.

It sounds as though it would use some complex conditional formatting.

I am open to ideas.

Thanks in anticipation.

[dflak]

Since an item may need a variable number of parts, this implies a one to many relationship which is better handled by a database than a spreadsheet. Give a shout out here: http://www.utteraccess.com. These are the Access experts.

What you probably need is a table that has the item, another table with a list of parts (which can be used for data validation and dropdown lists - also it can contain parts prices), and a table linked to the main table that shows parts used on the item.

This should be flexible enough to get you going in whatever analysis you need.

[Rhothgar]

Maximum Profit Project.xlsMaximum Profit Project.xls

Since an item may need a variable number of parts, this implies a one to many relationship which is better handled by a database than a spreadsheet. Give a shout out here: http://www.utteraccess.com. These are the Access experts.

What you probably need is a table that has the item, another table with a list of parts (which can be used for data validation and dropdown lists - also it can contain parts prices), and a table linked to the main table that shows parts used on the item.

This should be flexible enough to get you going in whatever analysis you need.

Hi

I am not experienced at using Access even on a basic level and I needed to get the project started today so this evening as I had not had any responses I have done the attached spreadsheet.

So far, it tells me what it costs to repair a set amount of inventory that I have checked over today.

Now I need ideas on how to make it work to my benefit! I want to minimise the amount of thinking I have to do to determine whether or not it is cheaper to canabalise an item which may be missing parts rather than repair and if so, how do I make it work out to maximise profit?

Thanks for your reply dflak. I may have to look into Access but I'm not sure I have the time for another new learning curve...