Program recomendations

[Mr_Gilbert]

Hi All,

Im after some advice. Please accept my apologies if this is in the wrong part of the forum (i don't post often).

I have been given a role of looking after a budget at work. Currently the whole thing is set up in excel with 180+ sheets in 1 of the workbooks (some other workbooks report on the main one). Im not convinced that excel is the right way to monitor the budget, but i cant think of a better way. Im hoping that someone with more knowledge can maybe point me in the right direction (even if that direction is excel).

Some details of the current set up:

We have 180 stores. each store has a budget for the staff worked out in excel (1 sheet per store). we then use data from our HR system and try to run a comparison to ensure no store is going over budget (or highlight stores that are under budget). the data comes in, in a very set way from the HR system and can be difficult to work with (200,000+ plus rows). Ideally i need to be able to update the data from the HR system (its a new file updated each week with new rows added) and then run a report on it, to show stores over budget and by how many. We then do cost comparison as it is broken down by position and each of them have a different salary attached. The reports needed are for various levels, so some people just want the overall over or under figures, some people want the nitty gritty of the individual stores as well as reports being sent to the store managers. The reports are all done manually at the moment. My first thought was to automate the reports, but i don't want to waste time on that, if there is a better overall solution.

Any thoughts and advice would be appreciated.

 

[Jeffrey Mahoney]

From my perspective, Excel can do anything you need it to do. I have created so many varieties of budget workbooks over the last 30 years.

The last one I created had a ton of features. We had hundreds of project sheets similar to your "Store" sheets. The project sheets contained the monthly details and history. The summary sheet pulled the data from the project sheets using INDIRECT. The summary sheet was given the ability to click on a row and navigate directly to the project sheet so the user didn't have to go hunt it down. Each row in column A used a formula to give the indirect formulas the name of each sheet. This allowed a user to change the project sheet names without affecting the formulas. The project sheets have to be formatted identically. There are ways to create named ranges that save a ton of time. There are ways to edit project sheets simultaneously.

I created macros that pulled data from 3 different reports in SAP. When I was given this task, the previous person spent two days updating the values from SAP by hand. I managed to get the updates in 10 minutes. Now we could get financials and create projections with daily updates instead of weekly.

So, to summarize, this project took me months to create. I use this design for new clients. I could show you the methods I used, but it certainly isn't plug and play. You would have to decide if this is a road you want to follow. In my opinion, having the ability to automatically create a custom repeatable report is priceless.

Jeff

 

[Mr_Gilbert]

Thanks for the reply. It’s interesting to hear that you have also previously had a similar situation, at least I know that it isn’t just me.

I think I need to do some research on correct formula usage as, I can’t stop the document becoming huge and sluggish to update. Seems slow when using it, which waswhat prompted me to ask if there was a better alternative.

I would be interested in seeing how you accomplished the task, if you have anything you can share, I would be extremely grateful.

 

[hernantorres23]

Hi, there is several ways for budgeting and forecasting, in my case I has been using Access with MySQL, Microsoft Forecaster (this come for SQL Server, Oracle or Access) and Excel (Power Query), but firstly I raise several factors. I work every day with budgets and forecasts, this is my day to day and it is a delicate job that requires a lot of customization for each business.

I would pleasure to help you, every day there are many things to learn

 

[Jeffrey Mahoney]

Here's an example where I use the Indirect function to get values from the project sheets.

Column A contains the sheet name. I have a cell in each project sheet that gets the dynamic name of each project sheet in case it changes.

Book1
	A			D							K		M	N	O
8	Sheet			Description							Funded Budget		Current (Target) BUDGET	Planned (w/Actuals) BUDGET	Forecast (w/Actuals) BUDGET
9				Routine Maintenance
10	Payroll			Payroll Labor and Tax Credit							þ		$3,900,000	$3,922,964	$3,922,964
Spend Sum

Cell Formulas
Range	Formula
A10	A10	=Payroll!ShtName
M10	M10	=INDIRECT("'"&$A10&"'!"&"Current_BUDGET")
N10	N10	=INDIRECT("'"&$A10&"'!"&"C31")
O10	O10	=INDIRECT("'"&$A10&"'!"&"Forecast")

 

[Jeffrey Mahoney]

This is the formula that I use to get the sheet name
=MID(CELL("filename",Payroll!AC1),FIND("]",CELL("filename",Payroll!AC1))+1,100)

 

[Jeffrey Mahoney]

This is the code I use to navigate to each project sheet by double clicking on any of the rows on the summary sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim R As Range
    Dim i As Range
    Dim A As String
    Dim C As String
    Dim Q As String
    Dim j As Integer
    Dim B As String
    Dim Ind As Integer
    Dim D As Integer
    Dim X As Long
    Dim Sht As Worksheet
    Dim Cel As Range
    Dim f As Integer
    Dim X1 As Long
    Dim X2 As Long
    Dim Y1 As Long
    Dim Y2 As Long
    Dim u As Range
    Dim BBoolCol As Long
    Dim RedTargCol As Long
    
    CallingSht = ActiveSheet.Name
    
    
    Set R = Range("SpendSheet_hdr").Offset(1, 0)
    Set R = Range(R, R.Offset(1000000, 0).Offset(0, 300))
    Set i = Intersect(Target, R)
    If Not i Is Nothing Then
      Cancel = True
      A = Range("A" & i.Row).Value
      Set Sht = Sheets(A)
      If Sht.Visible = xlSheetHidden Then Sht.Visible = xlSheetVisible
      Sht.Activate
      ActiveCell.Select
    End If

 

[Jeffrey Mahoney]

I work every day with budgets and forecasts, this is my day to day and it is a delicate job that requires a lot of customization for each business.

Agreed. I worked on a budget system for 3 years and spent the whole time customizing it. That's good for me.