sales projection and net sales program

[ultra99]

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

[Ken Puls]

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.)

[ultra99]

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

[Ken Puls]

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.)

[ultra99]

I'm using Excel 2010.

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

[Ken Puls]

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

[ultra99]

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.

[Ken Puls]

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.

[ultra99]

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?

[Ken Puls]

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.

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/