+ Reply to Thread
Results 1 to 9 of 9

Making excel template for profit margins

  1. #1
    Registered User
    Join Date
    06-14-2010
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Making excel template for profit margins

    Hi, currently at my work place I was tasked with learning excel and to make template that could calculate our profit margins by entering in product codes, our cost, and their price. Now the problem is that I have little experience with excel, I can do a basic worksheet but I don't know how to use things like Vlookup for excel which is a function i expect i will need to use. I was wondering if anyone could help me get off on the right foot with making this template. What are some functions I need to learn how to use? How would i get this all in a template? Are there good programs that can do this already? Thanks in advanced!

    -PrivateLucky

    edit: edit the title b/c it was not specific enough
    Last edited by PrivateLucky; 06-14-2010 at 02:44 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Unexperienced with Excel: Need Help

    Welcome to the forum.

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: Making excel template for profit margins

    A template is nothing but a excel worksheet that is saved somewhere and gets opened up and saved under a different name when a new set of imputs are ready to be entered into it.

    A good way to start is to write a rough draft, outline what your goals are for it calculate, and as you try to find a way to calculate certain parameters with, post specific questions here. Right now your question is a bit to broad.

    I'd also google 'exel totorial' - there's a lot of great stuff on the web.
    You either quit or become really good at it. There are no other choices.

  4. #4
    Registered User
    Join Date
    06-14-2010
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Making excel template for profit margins

    Alright I will try to describe what I want it to do more clearly.

    Our company gets excel spread sheets with thousands of product codes and what they are looking to buy them at every week. What I want to be able to do is enter in the product code of items we can sell and our cost to make the process go faster. Right now we do it manually and people have been complaing about how unproductive it is because some times there are no good prices or due to human error we miss a few and lose out on productivity.

    Here is an example.
    Company A wants to buy Product X at $10
    We have product X but bought it at $15

    I want to be able to enter in our product codes with the price we bought it at and it will tell us to not sell product X at $10 because we will lose money. And conversely it can tell us that if we sell product X at $20 it will tell us the positive margins and how big that margin is. Just let me know if there are any other specifics you need to know.

  5. #5
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: Making excel template for profit margins

    Ok, that's a lot more helpful. This should be pretty straitforward. Why don't you attach a sample spreadsheet (take out any info you do not want people to see), and I can give you an example of how to do this.

  6. #6
    Registered User
    Join Date
    06-14-2010
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Making excel template for profit margins

    Unfotunately I cannot give exact numbers or names over the internet but I made an example of what The sheets look like. The only difference is that my example has 6 codes while the real ones have several thousand. The master list is what we get our product for while the client list is what we are trying to compare it to. The first worksheet (master) will be pretty much static while we will be entering in our values into the second worksheet (client). There is also another problem I had when trying to do this my self. We get the same products from many different companys at different prices. There may be many similar product codes that we get from 2-3 companies and this is where i ran into a road block on Vlookup as you cant have that. Here is the example.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: Making excel template for profit margins

    See attached - chages are highlited. In order for this to work, product codes have to be sorted from low number to high number in Master List. Also, I'm assuming each product code has a unique price associated with it and "their product codes" correspond to the same product as "our product codes".
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-14-2010
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Making excel template for profit margins

    Thank you soooo much for your help so far. I am still trying to figure out a few kinks in it at the moment. For example sometimes when we get a list of products clients want, we dont have some of the products they want. How do I make it so that when I enter in a product from a client we dont have in our master list, it will show up as blank or "NOT IN INVENTORY"?

  9. #9
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: Making excel template for profit margins

    Please post a spreadsheet and show inputs and outputs you are trying to achieve, and where.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1