Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: sales projection and net sales program

  1. #1

    sales projection and net sales program



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I want to create a simple program to help me determining sales projection/growth in a particular account.

    Meaning, I have an account with a certain # of customers. If I take % of these customers, how much sales would I gain? And I would like these numbers to be converted to a graph, if possible. Also, I would like to add a database so when I click on an account all the information, profile, etc. would pop up.

    Is my idea reasonable/doable? Anyone has any other ideas? We could brainstorm here..

    I could use anything, Access, Excel, asp, sql, etc.

    Thanks

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum!

    I routinely use Access as a back end database for storing my data and do the generation/manipulation/reporting in Excel, so yes, I'm certain this could be done. You'd need to flesh out a lot more of the logic to make it happen though. As long as you're willing to do the leg work on it with our advice/tuotoring, we can certainly help you out. (We can also find you a consultant if you just want to pay someone to take care of it for you.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hey, thanks for the reply.

    Of course I'm ready to get my hands dirty! I want learn this stuff and FAST!!

    I was thinking of something to get me started. I have an excel sheet with forecast/actual sales for the year month by month. And the average sales for each quarter (every 3 months) and total at the end of the year. I want these numbers to be converted into graphs for each account separately and for all accounts total.

    Also, I would like to create a macro (button) to click on and the result to appear like the image below. (where "All Departments" are replaced with "Accounts")

    http://api.ning.com/files/HHeDBq*0Zm...335&height=289
    Last edited by ultra99; 2011-07-08 at 08:25 PM.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Okay... I'm curious, what version on Excel are you using? The sparkline graphs were built into Excel 2010.

    Do you have (or can you make) some falsified data in the same structure that you have to start? Then we can start looking at the best way to build it up.

    (FYI, I'm going to be away this weekend. If someone else doesn't pick up the thread, I'll loop back on it after the weekend.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    I'm using Excel 2010.

    Here's a the file i'm working with.
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi there,

    So give me a little background on this file. Is this a copy of what you actually use on a daily basis? It seems to be formatted a bit like what you use to capture data, a bit like what you use to visualize and report it out. How many rows of data would you typically capture in a year?

    The reason that I'm asking is that I think you'll be able to make much more use of your data if you can get the source into a table. You can then hit it with PivotTable/PivotCharts to get the data into the format you need for easier mining...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Hey, thanks for the reply! phew!

    Yes, this is the file I use every month to input the data. I'm willing to change the formatting, or start from scratch.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Here, have a quick look at this. Would need to be seriously prettied up, but this is one pivot table built off the source data. You could also build others and slice and dice it in different ways... probably much easier than what you had.

    I'm not totally sold on the table structure, but it will work to start, I think.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Hey, thanks for your efforts!

    I haven't used PivotTables before, but I'll play around and come back to you. Do PivotTable automatically generated line/bar graphs?

    What if I want to link the Accounts to Access database to show all customers and customers' information, how do I do that?

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by ultra99 View Post
    I haven't used PivotTables before, but I'll play around and come back to you. Do PivotTable automatically generated line/bar graphs?
    Automatically, no. But if you click on the PivotTable and choose to insert a new chart, it will make a pivot chart linked to the pivot table. And if your pivot table isn't showing the way you'd like your data displayed, then create a new pivot table to format your data the way you want it. There are instances where we'll build different data sets out of PivotTables into another format, then build a normal chart off it, but the cool thing about pivot charts is that they can be linked to slicers and allow for drill down.

    If you'd like some good articles on PivotTables, check out Debra Dalgleish's site. She's got a full list of articles here: http://www.contextures.com/tiptech.html

    I know Debra does watch the forums here, as does Roger Govier, so if you would like any help with them, I'm sure we can call them in.

    Quote Originally Posted by ultra99
    What if I want to link the Accounts to Access database to show all customers and customers' information, how do I do that?
    We have a couple of options here. We can connect to the Access database and pull data into a table/pivottable. If you're allowed to download and install programs on your computer, then you could also consider installing PowerPivot. PowerPivot adds a whole new dimension to PivotTables and makes some things MUCH easier to do. It's a free addin for Excel 2010 which you can learn more about here: http://www.powerpivot.com/
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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