Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 23

Thread: sales projection and net sales program

  1. #11


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

    Quote Originally Posted by Ken Puls View Post
    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.
    Definitely they can join in!!

    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/
    I would like to start WITHOUT the add-on. Would like to build the database in access and link it in excel.

    There are also a bunch of youtube tutorials on PivotTables and other excel content here, maybe that a good start for me?

  2. #12
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Definitely check out Mike's videos.

    The first par tis that you're going to need to build your access tables and fill them with some data. I'd suggest creating a table for forecasts, and one for actuals. Better to keep those types of data separate and join them with a query if needed.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    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. #13
    But wouldn't be easier, at least visually, if the forecast was right next to the actual?

    Why would these be separate tables better?

    Sorry for my stupid questions, but I want to learn and use excel more efficiently, and professionally!

  4. #14
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    There's a difference between storing and displaying. I build my budgets a year in advance, and I'm assuming that your forecasts will be done in advance as well. In this way, if you want to build JUST a forecast, you can do it easily, or JUST an actual statement as well. We can also use a query to pull them together to display them however we want.

    So basically the tables are more targeted which allows better focus and more robust use of your data.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    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. #15
    Actually, it makes sense now, having the data separate and create a macro or something to display them together.

    I do forecasting every month for the remaining months of the year because the forecast change according to the actual sales. Now this brings up a question, I have an $X sales total for the year, and need to forecast the months, but sometimes they change, one month they decrease, another increase, BUT the total $X by the end of the year should be the same/constant. Is there an equation that can correct my forecast to maintain the total $X at the end of the year?

    Did I make any sense??

  6. #16
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    You did, yes. Does it only go till December every year though? So if you forecast $200 per month for 2011, you want your forecast to stay at $2400 for the full year and just adjust the final month to match? What happens when you get to January of 2012?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    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. #17
    Correct, I want the total to stay $2400 for the full year. When I get to January 2012 I start over with a different forecast/target.

  8. #18
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    How much data o you have? I ask, because it may be best just to start in Excel, capture the raw data in a tabular format, and work on your presentation in this spreadsheet. I would also probably go with Ken's suggestion of PowerPivot as you have 2010, it will allow you to come to terms with all of data structure concepts that apply with databases, but all from within an Excel environment. Once you have working as you really want it, you could think of moving it to a proper database at that point.

  9. #19
    Hey Bob, I'll begin with 2 separate tables and do pivot tables, and then a query or something to generate the charts.

    I want a table ready to input the data on a monthly basis which then can create charts through out the year if I want to compare months, quarters with different accounts or products.

    I'll keep the databases and customer information towards the end, as I might be going ahead of myself here.

  10. #20
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    Pivot tables work on single tables. If you want to pivot across multiple tables, you need to merge the data, or use PowerPivot.

Page 2 of 3 FirstFirst 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
  •