1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Project

Discussion in 'Business Applications' started by rammyboy, Jan 30, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. rammyboy

    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
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    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. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. rammyboy

    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?
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    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. rammyboy

    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!
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    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
     
  8. rammyboy

    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!!
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  10. rammyboy

    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
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    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. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. rammyboy

    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
     

    Attached Files:

  14. rammyboy

    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
     
  15. w-plaza.com

    w-plaza.com

    Joined:
    Nov 10, 2006
    Messages:
    6
    nice yaar thanks :D
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/539673

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice