How to create a product sales quotation system - hopefully not too ambitious?

[TheLobber]

Dear All,

I'm new to this forum and a very basic user of Excel... so I make my apologies now before I hack anyone off!

So down to the nitty-gritty and don't berate me too much if you think this is essentially impossible Imagine if you will, a quote for a new kitchen that includes company and client details at the top then subheadings for cabinets, worktop, accessories, appliances and finally a cost total. Under each of those subheadings the products will be listed with various column headings providing information as to the size, colour, brand and price.

The quote needs to be printed out onto minimal sheets of A4 (Save the Planet!) i.e. if there are only several items being quoted to a client, I would want to only print off one page... a large quote may need to expand up to 4 or 5 pages.

Issue: Each subheading can have anything between 1-100 products... how to auto adjust the categories/subheadings so they directly arrive after one another?

I have a software that creates a excel spreadsheet for me, however all the information that it creates is very basic and jumbled. The challenge is to extract that information and organize it so it creates a quote for a client.

I would be grateful for any light someone could shed on my situation and sorry in advance if this reads as gobbledygook.

Thanks in advance!

[humdingaling]

the question straight out the gate

Do you just want it done
what your asking isn't anything particularly new or difficult
a simple google search would yield you many templates to which you can either adopt or pay for

or

do you want to learn everything involved with making one yourself and therefore knowing how to maintain/possibly expand if required
there would be several techniques to learn to make it have the usability/polish you would want out of such a quotation spreadsheet

I have a software that creates a excel spreadsheet for me, however all the information that it creates is very basic and jumbled

This is already your starting point?
what is the software? something like MYOB/Quicken or some other small business accounting software?

you said the challenge was extracting the information if that is the challenge then that should really be the question?
as you shouldn't have to maintain a seperate quoting system which isnt linked in to whatever software you are running

[TheLobber]

Hi Humdingaling,

love the username BTW!

In answer to your question... I would love to have it just done, but I will need to scale it and do many other things at a later stage i.e. search for customer quotes! So sadly I think i'm just going to have to learn it all myself or try to find a template that I can breakdown and try to understand.

The information is produced by a CAD software and is a very basic excel readable file, so there is no option to do anything in the software and must all be done in excel.

[humdingaling]

Thanks i wear the name proudly

ok fair call

reason i asked about the secondary software as it seems like its a semi basis on how your quotes will sort of run
i assume you have ready made templates to drawn down on

ie someone can have the "Standard" sizes for kitchen cabinet or if they want anything "radically" different then its custom work

leads to next question
how do you select things to then come up with a quote
do you charge by the cm or something or is it predefined set of costs or something else? like you just calculate it manually off excel every time

reason i ask this now is it will help in determining how you design your "quoting system"
the basic look at feel is relatively easy part the mechanics behind how it quotes...thats dependant on how the quotes work

i can understand your top layer..like you select the type/category of work then it gives a list of sorts of things below?

maybe you can start by doing a high level list/structure of how you envisage it working

[humdingaling]

sorry jumping ahead abit
found some random video clip with tips you can learn and apply to your potential quoting system

its by Bill Jelen who is pretty easy to listen to and understand
https://www.youtube.com/watch?v=Tj_Ce7LS1Ok
its part of a whole series of useful "tips" to learn of which you can apply to your system
https://www.mrexcel.com/news/next-invoice-number/

[TheLobber]

Hey Humdingaling, thanks for the first video - those buttons are a very helpful addition!

I think my Post may be a little confusing so I aim to try and clarify a little... I hope!?

What i'm trying to create is a spreadsheet that organizes the jumbled information that i have - There is no information missing in the spreadsheet I have and no information needs to be added, all I am essentially trying to do is organize it into a form.

What I initially imagine is essentially a blank first page with some simple formulas i.e. if (cell) = "cabinet" then assign to sheet 2 then all data on that line would automatically pass across to sheet 2. On sheet 2 there would be a heading saying CABINETRY then under that would be column subheadings saying Price, Size, Quantity Etc.

The whole form would take on a hierarchy like below:

Sheet 1:
Area to copy and paste data from CAD file

Sheet 2:
1) Company Details - Permanent
2) Client Details - Manual Entry

Sheet 3:
3) Cabinetry Details - Automated from Copy and Paste

Sheet 4:
4) Decor/Profiles - Automated from Copy and Paste


Sheet 5:
5) Worktop - Automated from Copy and Paste

Sheet 6:
6) Appliances - Automated from Copy and Paste

Sheet 7:
7) Invoice total/Tax - Calculated from "Cost Column" Cells on sheets 2-6

I imagine a function placed into each line of the sheet like: if line = empty then move to sheet 3 or separate sheet that acquires all data from the previous sheets.

Hope this makes things more understandable?

Thanks in advance to anyone able to assist me!

[humdingaling]

haven't forgot about you just tied down at work at the moment...everyone at works wants everything before end of year etc etc
i'll come back to you when i do get a bit of free time

[TheLobber]

Thanks Humdingaling! Hope you have a merry Xmas!