Results 1 to 2 of 2
2005-07-18, 23:05 #1
- Join Date
- Jul 2002
- Ankeny, Iowa, USA
- Thanked 0 Times in 0 Posts
Forecasting or Budgeting Data Input Tool (Excel 2003)
I'm wondering if anyone has an example out there on a simple data entry Excel tool for sales forecasting and/or budgeting. I need the sales reps to enter their forecasts and/or budgets into the spreadsheet. I've created numerous spreadsheets over the past 5 years which seem to work fine, however I'd like to advance it a bit if possible. Basically, I give the salereps an Excel file with a pivot table showing their sales history in it, and then I give them another Excel file with a blank template for them to simply enter the customer #, item #, warehouse #, quantity and sales price... they then drag it down for the next 11 lines so that there is a forecast/budget for 12 months for that customer (keeping the budget data in a database format, so that a pivot table can be used to read this budget database). I have another 10 or so columns of data that have vlookups in this budget template file that the salereps can't see, for example if they pick item # 1026, then the vlookup up formula enters in the item description (for example, wooden chair). These hidden columns are used in the pivot table so that the salesrep can see their budget data they just input in a summarized and nicely presented format (as opposed to a database format with no totals, etc). My request would be to have the history AND the budget data input all on one page... so they are not having to refer to another excel worksheet (or the hard copy of this history printed out on paper) and then rekeying the cust #, item #, etc, as the info is, afterall in the history report . The pivot tables have some limitations as to how may columns it will let you use when displaying the history, so this presents a problem with the current method. Likewise, I'd still like the budget to end up in database format so that in the end a pivot table can be created from it. Also, it would be great to see the history compared to the new budget data side by side for comparison. I do this now by copying and pasting the budget database into the history database and then showing the comparison in a pivot table. Any suggestions, or comments would be greatly appreciated!!!
2005-07-25, 22:18 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Re: Forecasting or Budgeting Data Input Tool (Excel 2003)
Do the suggestions in <post#=458609>post 458609</post#> help at all.