Tech Support Guy banner
Status
Not open for further replies.

Excel Project

2K views 16 replies 4 participants last post by  Zack Barresse 
#1 ·
Hi,

I am doing A Level ICT at 6th Form. I have chosen to create a quotation for a printing company as a minor project. The project needs to be able to create a quotation from the requirement's inputted.

I am have become stuck in the design stage and could do with a few pointers if anyone could suggest some.

Obviously there are many different products in the printing industry and each product vary's.

I proposed to make this through look up tables but would this work?, if not does anyone know how i could make this?

Many Thanks

Alex
 
#2 ·
depends on the complexity of the requirements - I have built a spreadsheet (actually in MSworks as the customer did not have excel0
to price for a window company based on some criteria entered into a spreadsheet and using a lookup table

butt as I say - it really depends on how complicated the requirements are
 
#3 ·
Sounds like you could have an add-in with a sheet as a general template, then, depending on user input (probably via UserForm) adjust the template accordingly. Or, if that demands too much of having an in-file template (hidden sheet) then you could create it on-the-fly with your code.

But as ETAF states, depending on the requirements it may be more (or less) complicated than that.
 
#4 ·
Its like this...

A quotation for say 500 single colour letterheads on standard paper would be worked out by calculating the following variables:
Materials (set costs from mateirals)
Ink(set cost for 1,2 and 4 colours)
Labour (Hourly)
Quantity
Size(i.e a4)
and then profit.

Could this be done?

Any one suggest how?
 
#5 ·
yea - you should be able to do that with some lookups,excel functions or a userform as suggested..

Not going to go into to much detail as thats the point of your project to work it all out - your approach - and part of learning is making the mistakes

dont try to do the whole thing - just try a quick little experiment - should be able to knock something up in a couple of hours to test the theory
 
#6 ·
Thats the thing....I dont know where to start with the calculation/forumula phase!

I have designed the form side of it with VisualBasicEditor and UserForm in mind, but that is as far as i have got!

If you could start me off with the theory of it! :D It would be much appreciated!
 
#9 ·
I think ETAF is trying to say if you build a table that defines different items such as you laid out (cost of materials, labor, etc), basically down to a single units cost, one unique record per row/record. Then you can do a lookup or find and multiply by whatever the user has inputed for quantity, then do that for the other user items.
 
#11 ·
thats what you could do in the lookup - quantity - there is an option that if the look-up not an exact match it will take a near value - so 354 items might use the cost assigned to 400 for example - trying to be vague otherwise we could just end up doing the project for you.
 
#12 ·
I was thinking about lining out the base cost for each item. Then when the user inputs the quantity you take their number (i.e. 354) and take the data for the item, find the item and it's base price, then multiply that by the user input quantity.

If you have discounts for items over a certain amount, that should be figured in after the above calculation as a separate figure. You could do this with a simple If/Then function.
 
#13 ·
Ok..I have started to design the system. The table attatched will be the basis of forming a quotation as this will be used as a sort of look up table thing to find the prices for the quote.

Will this work and how would i make this work?

Many Thanks

Alex

Edit - New XLS sheet
 

Attachments

#17 ·
I don't really think that would be a simple way. In fact, I think you'd be shooting yourself in the foot. You should have only one location for thte information and not break it up into many small pieces (I'm assuming so you could see it all on one page).

I've altered your data slightly and uploaded a sample. If these are the basic units of measurement, the most base units you work with, then that is what you need to work with.
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top