En cours

Excel-based Quoting System for Beverage Manufacturer

Hi There,

I work for a small contract brewery and I need an Excel or Access-based quoting tool to determine the packing fee (called a copack fee) specific to each customers individual requirements based on our actual costs and allow me to add a markup to that packing fee.

The specification is as follows:

- I need to store basic information about each customer. Company name, contact name, address, phone number etc.

- Each customer has one or more products (beers).

- Each beer can be brewed and packaged into bottles or kegs (or both)

BOTTLED PRODUCT:

- Each product has a bottle packing configuration with costs which includes:

* Glass Bottle Type (selectable from a list of types of bottles we stock)

* Cap Type

* Label Type

- Each bottle is then packed into a carton/box. We then add fees based on the following:

* Packing Cluster Type (4 pack basket, 6 pack basket, plastic shrink wrap etc)

* Carton Outer (box) type

* Number of bottles per box based on box type

* Number of Clusters per box (based on Packing Cluster Type) based on box type

* Number of boxes per pallet per box type

* Number of litres of beer per box type

- After each box is packed and costed, we add the following costs:

* Energy cost per box based on a per litre rate which is set as a constant across all quotes (but can be changed at a later time)

* Brewing Labour cost per box

* Filtration Labour cost per box

* Packing Labour cost per box

* Pallet wrapping cost per box as a set fee based on the number of boxes per pallet above.

- Once all of these are added together, we add a margin based on a percentage (say 25%).to determine a total per bottled beer copack fee per box.

KEGGED BEER:

- After each keg is packed, we add the following costs:

* Energy cost per box based on a per litre rate which is set as a constant across all quotes (but can be changed at a later time)

* Brewing Labour cost per keg

* Filtration Labour cost per keg

* Packing Labour cost per keg

* Sterile filtration cost (this is optional and is an additional filtration only available for kegged beer as a per-keg cost)

* Pallet wrapping cost per box as a set fee based on 18 kegs per pallet.

- Once all of these are added together, we add a margin based on a percentage (say 25%).to determine a total per keg copack fee per keg.

Though this is not my brewery, this is kind of the questionnaire we would fill out to determine each customers requirements.

Once we have the information and the calculations are complete. I then need to be able to:

- store each quote and be able to add new revisions of it later (perhaps through being able to duplicate products and add version numbers)

- Have a nice and neatly laid out version for brewery internal use which breaks down each product configuration to be printed out.

- Have a version of the cost calculation for the customer which outlines the price that they will pay - without the cost breakdown. i.e. a single number copack fee per keg and per box.

And that's about it.

I don't mind what technology is used to achieve this outcome but Excel or MS Access are my preferred options. Please specify in your bid which technology you intend to use.

Cheers and good luck!

Compétences : Saisie de Données, Traitement de Données, Excel, Microsoft Access, Architecture Logicielle

Voir plus : quoting system excel, excel based bid system, excel based quote system, excel based contract system, excel beverage, excel quoting system, beverage manufacturer, what is requirements specification, what is a shrink, version technology, tool specification, need a small manufacturer, need a manufacturer of my product, manufacturer requirements, labour work, i have a product i need a manufacturer, have a product need a manufacturer, excel specification, excel energy, excel 16, data processing costs, cost of contract data entry, contract in excel, contract excel work, contract excel

Concernant l'employeur :
( 2 commentaires ) Moolap, Australia

N° du projet : #1638799