This is an excel model that:
- organizes raw materials requirements for manufacturing - organizes orders (entered by user)
- organizes input costs of raw materials (entered by user)
- keeps track of historical order history and raw materials pricing
-calculates cost of finished product once it is manufactured based on database of raw materials required for each unit and historical pricing of raw materials
-organizes quantity in stock of raw materials
I already created the sheets for this project which provides the basic layout and general organization and functionality. This project is being outsourced because of ? my inexperience with VBA.?
The general outline for the project is attached. We work with a factory that manufacturers jewelry for us. We must supply all raw materials (diamonds and gold) to the factory and factory creates the finished product and sends it back to us. The coder is welcome to use forms instead of buttons and if the coder can effectively justify a better functioning model, I will consider paying a slightly higher price (though this is a small business project). I also recommend that any coder who would like to work on this project also be available to chat (I prefer g-chat) just to be sure that the project is going in the right direction.
Here are the expectations sheet by sheet:
**Order Entry:** This is where the user enters the item ordered by DZ item#. A form could be created here to enter it so it can be selected from a list (so that you can type the first two numbers and the results appear as you type to avoid typo errors). The date should be auto filled on the day that the new order is made. The style number is matched in from the RAW page.?
**RAW:** This will be a complete list of items that we manufacture and all of their material requirements. The items are organized by our internal stock number (DZ #) and we also use style # to communicate with the? factory? (the factory uses the style #). This is just dummy data currently but it will be filled out. ? However, each piece will have up to 4 stones varieties. The required fields for each stone variety is stone type, carats size (mm), shape, quality. The numbers displayed here represent the required amount of carats of that respective stone type in the corresponding item. To get an idea of the data size, there are *about:* 300 units, 15 different stone types, 5 different shapes, 2 qualities and about 30 mm varieties. ? I will enter this RAW data myself.?
**Required stones**: One of the primary purposes of this model is to determine the required raw materials based on the current order. Perhaps, there should be a summary page that shows all of the raw materials that I would currently need to obtain. ? It should be sorted and easy to look through so that I can quickly see all of the stone types that I need. I do not want to scroll through a very long list of all stones to determine how much is needed, I just need a summary for it.
**Stone Summary**: This is really just the inventory summary for the loose stones. The purpose is I want to be able to check the current stones in stock against the factory's records to make sure the amount of stones remaining at the factory matches on both records.?
**Stones sent:** This is a record that keeps track of the stones that are sent to the factory. The purpose of this is to keep track of historical pricing for the goods so I can see what I paid before for a particular stone type the next time that I have to order it. Also, this should also update the stone summary page because it increases the amount of carats in stock. The required fields are the same with an added field for cost.?
**Finished Received:** This is for the finished goods that come back from the factory. This sheet is used for multiple purposes. First, the amount of each unit received should match the unit ordered (on the corresponding order). Secondly, there needs to be a function that calculates the cost of the item based on cost of the stones sent x amount of stone type required by a ? certain piece. This would be the cost without labor expense which I would have to add in manually. I do NOT need to track inventory of finished pieces because once the goods are received, I calculate the cost and enter it into our internal database software.?
**Historical**: Everything here is already covered but once again, I need to track history (entire history for previous orders also) of items ordered as well as stones sent and their costs, etc...
**Additional notes:** This model also needs to organize the orders well so I can easily find the items included on the most recent order.?