Excel Project

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
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?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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
 

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
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!
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
you may want to produce a flow chart showing the path someone would take

to see where the common and easy selections start with common costs
 

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
I dont get that? Could you rephrase it in laymans terms?

Thank you very much for your help!!
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
Hmm yeh i had kinda though about that but say for instance 1000 letterheads may be £150 but 2000 isnt 300 as it would be like you suggested....as the price for run ons is cheaper?

Would there be a way of solving that?

Thanks
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
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

rammyboy

Thread Starter
Joined
Sep 26, 2005
Messages
213
The new sheet attatched to my previous post is the updated products page.

Would i make these V lookups and then on the user form make it so i type in the product number and it gets the description, quantity and price?

Any suggestions?

Many Thanks
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top