PDA

View Full Version : Suggestions for Creating Hundreds of Data Lines for Budget



jaydee
08-30-2019, 07:22 PM
All-

I’m trying to figure out a template I can use to create hundreds of data lines (that do not exist) for next years budget.

Our company gives managers an Excel sheet (first tab), where they are supposed to estimate for each customer:
1) total sales
2) gross profit

The problem is, our parent company wants to see the budget numbers broken out by department.

On tab 2, I have raw data for the 3 customers that I got from our accounting system. When I put this into a pivot table (tab 3), it shows customer sales, by master dept / department. I’m trying to create budget lines that I can merge with our actual data (tab 2). This is because they pivot the data, so I can’t put the budget numbers off to the side. Note the formulas I need are on tab 3, highlighted in yellow, but I have to do it manually.

Anyone have any ideas on a possible template I can use? I’m trying to figure out a way where I can dump in the budget figures from the managers (tab 1), and the rows would be created automatically. There are 1000’s of customers.

Any ideas appreciated!

Paul_Hossler
08-31-2019, 08:45 AM
Not perfect (but then nothing ever is)

You can get the PT to calculate %Actual Sales and %Actual GP

24908

But I think you still need a lookup range got the Company-Budget-GP data. I put copy on Sheet1 and Sheet2 for now

The Budget$ and BudgetGP$ still need formulas as far as I can see; maybe a PT or PowerPivot expert has a better way

Sheet1 is by company from your example

Sheet2 is my Major Dept then company


BTW, I didn't see where the



hundreds of data lines (that do not exist) for next years budget.

come in

Max_iR
08-31-2019, 02:22 PM
Try this :
Click on the button.

Sub test()
Dim rng As Range
Dim i, lr, lrd As Long
lr = Sheets(2).Cells(Rows.Count, 2).End(3).Row
Set rng = Sheets(2).Range("b1:d" & lr)
Application.ScreenUpdating = False
Sheets(4).Range("a1:f" & lr).ClearContents
Sheets(4).Range("a1:c" & lr).Value = rng.Value
Sheets(4).Range("a1:c" & lr).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
lrd = Sheets(4).Cells(Rows.Count, 1).End(3).Row
Sheets(4).Range("D2").FormulaArray = "=SUM((A2='1921 Actual Sales'!$B$2:$B$" & lr & ")*(B2='1921 Actual Sales'!$C$2:$C$" & lr & ")*(C2='1921 Actual Sales'!$D$2:$D$" & lr & ")*('1921 Actual Sales'!$E$2:$E$" & lr & "))"
Sheets(4).Range("D2").AutoFill Destination:=Range("D2:D" & lrd)
Sheets(4).Range("e2").FormulaArray = "=SUM((A2='1921 Actual Sales'!$B$2:$B$" & lr & ")*(B2='1921 Actual Sales'!$C$2:$C$" & lr & ")*(C2='1921 Actual Sales'!$D$2:$D$" & lr & ")*('1921 Actual Sales'!$f$2:$f$" & lr & "))"
Sheets(4).Range("e2").AutoFill Destination:=Range("e2:e" & lrd)
Sheets(4).Range("f2").FormulaArray = "=SUM((A2='1921 Actual Sales'!$B$2:$B$" & lr & ")*(B2='1921 Actual Sales'!$C$2:$C$" & lr & ")*(C2='1921 Actual Sales'!$D$2:$D$" & lr & ")*('1921 Actual Sales'!$g$2:$g$" & lr & "))"
Sheets(4).Range("f2").AutoFill Destination:=Range("f2:f" & lrd)
Application.ScreenUpdating = True
End Sub

p45cal
09-01-2019, 04:43 AM
While finding a Power Query solution for this I have been able to reproduce your department-by-department Budget Sales figures, but extended to all three companies.
When dealing with the GP column I have significant misgivings about how how these are calculated.
If you take the screenshot below:
24914

For this department an Actual GP of 1,932 on Actual Sales of 10,895 is about 18% of Sales.
Surely you'd expect the same department to maintain this sort of profit margin year to year?
So why, on Budget Sales of 1,249 do we not have a figure of about 220 for Budget GP?
Instead you have 532 which is closer to 43%?

I'll be looking at this on-and-off today to try to come up with Budget Cost and Budget GP columns which tally with the Plan 2020 values on the 2020 Plan sheet.

p45cal
09-01-2019, 05:41 AM
Ignore that last post of mine, I hadn't realised that the 22% manager's GP% forecast was so much greater (nearly 2.5 times greater) than the previous year's actual GP% of about 9%.
I'll have something for you later today…

p45cal
09-01-2019, 11:35 AM
I'm out of time at the moment and rather than offer no solution I'll offer a nearly right solution (I haven't coped well with the negative GP).
I'm currently a beginner as far as Power Query goes so I'm sure this will be very much a beginner's solution.
The attached has a new table on sheet Sheet4. It uses no data at all from Pivot sheet. It uses only a table called Table2 located A4:M7 of the 2020 Plan sheet, a table called ActualSales being cells A1:G8810 of the 1921 Actual Sales (1921!?) sheet, nothing else.
In order to update the result you only need to update the data in these 2 tables and refresh the new table on Sheet4.

If you were to create a Pivot table from this new table with the appropriate subtotals, you'd see that the Sales/GP/Cost are the same as the manager's forecasts excluding figures for negative GP - which is where I've run out of time.

jaydee
09-01-2019, 01:19 PM
Hey guys, thanks for the varies ideas.

Paul – Thanks for the PT trick, shaves two formulas that I was manually calculating. I think I can use it somehow.

For the hundreds of lines I was referring to, I attached a file to show what it looks like. For the first customer Costello, I added 23 data rows into my actual sales detail which is picked up in the PT. When you open the file, I highlighted the variances column which we’ll be using monthly to monitor our progress against budget. Since I have to do that for over 1000 customers, it’s like I have to create 23 lines for each customer. It’ll be worse if they make revisions, because I’m copying and pasting it now, but once there are changes, it’ll be a nightmare to delete and re-add data rows since it’s not formula driven.

P45cal – Thanks for your help! I’ll have to read about power query since I also don’t know anything about it, but if you think it helps I’ll do some research.
I opened up your file, but I didn’t see any formulas on the tab sheet4, so I wasn’t sure how things were recalculating.
Budget numbers can go up, just as they go down for varies reasons. Lost customer due to competitor, downgraded sales, new technology eroding margins, etc.

Anyway, thanks again guys for the feedback. Glad to know I wasn’t the only one thinking this was a challenge.

p45cal
09-01-2019, 01:46 PM
I’ll have to read about power query since I also don’t know anything about it, but if you think it helps I’ll do some research.
I opened up your file, but I didn’t see any formulas on the tab sheet4, so I wasn’t sure how things were recalculating
To demonstrate: If, in the file I attached earlier, in the 2020 Plan sheet, you change the manager's forecast for COSTELLO Sales8 from 200,000 to say 100,000, then goto to the new table on Sheet4, right-click it and choose refresh, you'll see it update. This will happen if you update either/both of the tables (on the 2020 Plan and the Actual Sales sheets).
(I haven't tried to see what happens if the companies on both tables do not match in number or name!)

To see what's going on, go to the ribbon, choose the Data one, then in the Queries & Connections section, click on Queries & Connections and a pane, similar to a Pivot Table pane will appear on the right, where you'll see 5 queries. Double-click on any of them to bring up the Power Query Editor. The only one which puts anything on a sheet is the one called Merge2.

If I get the time and inclination I'll look more carefully at how to deal with negative GP.

p45cal
09-01-2019, 02:34 PM
(I haven't tried to see what happens if the companies on both tables do not match in number or name!)
Now I have: if the company is present on the Actual Sales sheet, but not in the managers' forecasts, its departments get listed but nothing appears in the right 3 columns. If vice versa, the company doesn't appear at all in the new table. No errors occur.

Something that may be more important; you say you have to do this with thousands of customers and you've shown us 3, and those three alone needed nearly 9000 lines on the Actual Sales sheet. At that rate you'd only have room for about 350 customers on that sheet. Power Query can deal with data with multi-million rows.

Potentially, with Power Query, you wouldn't need the Actual Sales sheet at all, you could connect directly (or indirectly) to the source data and never see it. The only thing you'd need for input is the data on the 2020 Plan sheet! That way, when either the Actual Sales data or the 2020 Plan numbers change, all you'd have to do is refresh the new table.

It can get better than that; you may never need to see the new table either! Since you're going to use it to drive a Pivot table, let that be the only place the data is visible…

Paul_Hossler
09-01-2019, 06:32 PM
Another approach might be to use the PT, but add a PT refresh event handler to add the Budget numbers to each row




Option Explicit


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rBudget As Range, rRow As Range
Dim vCompany As Variant, vBudgetSales As Variant, vBudgetGP As Variant
Dim sCompany As String
Dim n As Long


Set rBudget = Worksheets("2020 Plan").Cells(1, 1).CurrentRegion

With Application.WorksheetFunction
vCompany = .Transpose(rBudget.Columns(1))
vBudgetSales = .Transpose(rBudget.Columns(11))
vBudgetGP = .Transpose(rBudget.Columns(12))
End With

Application.ScreenUpdating = False

With Me
.Range("H:I").Delete
.Range("H1").Value = "Budget Sales"
.Range("I1").Value = "Budget GP"
.Range("H:H").NumberFormat = .Range("D2").NumberFormat
.Range("I:I").NumberFormat = .Range("E2").NumberFormat
.Range("D1").Copy
.Range("H1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("E1").Copy
.Range("I1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

For Each rRow In Intersect(Target.TableRange1.Rows, Target.DataBodyRange).Rows
With rRow.EntireRow
sCompany = .Cells(1).Value

n = 0
On Error Resume Next
n = Application.WorksheetFunction.Match(sCompany, vCompany, 0)
On Error GoTo 0

If n > 0 Then
.Cells(8).Value = .Cells(6).Value * vBudgetSales(n)
.Cells(9).Value = .Cells(7).Value * vBudgetGP(n)
End If
End With
Next

.Columns("H:I").AutoFit
End With

Application.ScreenUpdating = True
End Sub

Leliel
03-20-2021, 10:53 AM
That's definitely something to use as well. I'm trying to create a budget for my start-up as well now and this Excel will definitely come in handy. It's actually funny how people who start a business completely ignore the fact that without a proper budget you will run into some great obstacles sooner or later. That being said I'm trying to keep myself as informed as possible and one of the ways is by reading books related to budgets. It's a great way to start learning more about investments as well and most importantly how to become successful when investing.

SamT
03-20-2021, 12:37 PM
Budget Forecasting is based on a series of Best Guess What-If Scenarios. IMHO, the best method for presenting this to Decision Makers is to have three sheets visible: The Current Budget; the Forecasted Budget; And a Scenarios Sheet

The Scenarios Sheet is merely a set of Parameters the Decision Makers can edit

All Budgetary changes follow historical patterns
What If Minimum Wages Changes by +-?%
What If Sales Changes by +-?%
What If Sales Commission changes by +-?%
More foreseeable What If scenarios


I would also suggest some specific Scenarios:

Minimum Wage = $15/hr
Loss of biggest Client
Loss of Best Sale person
Natural Disaster(s)



These are not one dimensional issues. For example: If Mandatory Minimum Wage changes, all elements of COGS will also change.

euleriscool
03-21-2021, 02:57 AM
Thanks. Also helped me

Ireul
03-22-2021, 02:20 PM
That's definitely something to use as well. I'm trying to create a budget for my start-up as well now and this Excel will definitely come in handy. It's actually funny how people who start a business completely ignore the fact that without a proper budget you will run into some great obstacles sooner or later. That being said I'm trying to keep myself as informed as possible and one of the ways is by reading books related to budgets (https://www.juststartinvesting.com/best-budgeting-books/). It's a great way to start learning more about investments as well and most importantly how to become successful when investing.

That's a great share as I was looking for free material to read on the subject! Thanks.