sales projection and net sales program

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
Hey, thanks for the reply.

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

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*0Z...GLuach*2Z2ldGK/sales.PNG?width=335&height=289
 
Last edited:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
I'm using Excel 2010.

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

Attachments

  • sales_forecast.xlsx
    100.9 KB · Views: 35

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
Hey, thanks for the reply! phew! :p

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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

Attachments

  • xlgf262-2.xlsx
    106.9 KB · Views: 30

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :)

ultra99 said:
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/
 

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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??
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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?
 

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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.
 

ultra99

New member
Joined
Jul 8, 2011
Messages
12
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Pivot tables work on single tables. If you want to pivot across multiple tables, you need to merge the data, or use PowerPivot.
 
Top