Adjusting Budget Balances

Question

Post reply

Adjusting Budget Balances

Joe Contreras

Ten Centuries

Points: 1341

February 12, 2007 at 11:17 am

#115521

Hi,

I'm trying to figure this problem out. Here is an example of what I'm trying to accomplish using SQL:

Dept. No	Dept.	AS Sold Budget	Current_Month	Unadjusted ETC	Adjusted ETC	Y1	Y2	Y3	Y4	Y5
1	Pasc Finishing	4515	124	4391	4391	0	0	0	0	0
2	Waxing		50	4341	4351
3	Sanding		100	4251
4	Additional Finishing		120	4131

I have an initial budgeted amount of 4515 in the [AS Sold Budget] column, now I need to subtract that amount from the [current_month] column for the first record then on the second record I need to subtract the remainder in the [Unadjusted ETC AP1] column from the second [current month] value to get the unadjusted balance. I need to continue this for the remaining depts as well as future AP periods where the current_month is pulling actual data values and can change for future AP Periods. One is how can I accomplish the balance reduction and what is the best way to attack the future AP Periods where current_month will change for the next AP Period. And BTW I need to allow the users the option to by pass the [Unadjusted ETC] using the [Adjusted ETC] and hence will need to recalculate the remaining balance for depts after the adjustment has been made.

Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Without more information (like DDL for the table) the best we can do is give you an algorithm. If (big if) you have some means of idnetifying the sequence of months, perhaps a month number or something, then obtaining the prior month unadjusted ETC is:

select top 1 UnadjustedEtc from TblName where MonthNumber < CurrentMonthNumber order by MonthNumber desc

Once you identify the proper row to pull data from, you have a few different ways to pull either the AdjustedEtc or UnadjustedEtc. I would probably use coalesce to select the first one that is not null. Somethign like the following:

select top 1 coalesce(AdjustedEtc, UnadjustedEtc) from tblName where MonthNumber < currentMonthNumber order by MonthNumber desc.

Clerly, this is only pseudocode. It is however the best we can do without DDL and sample data to work with.

hope this helps

Wayne

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 2

Actually I haven't built the table structure yet. What I'm trying to figure out right now is the best possible solution to this issue. This is the information I can provide:

ERP Data:

[Dept]/[Current_Month Actuals]/

Forecast Data:

[AS Sold]

The [AS Sold] value is my starting point, for example from the above example it's 4515. Next I want to be able to calculate the current AP01 balance by Dept (The saving grace here is that the ERP data is pulled based on a parameter AP Value, what this means is that the [current month] field will be updated with current AP data based on dept). The values from the example above 124/50/100/120 are being pulled from the ERP system which I then need to calculate in the [Unadjusted AP01 ETC] in the following manner:

[Unadjusted AP01 ETC]

Rec1: 4515 - 124 = 4391

Rec2: 4391 - 50 = 4341

Rec3: 4341 - 100 = 4241

Rec4: 4241 - 120 = 4121

Finishing balance for AP01 is 4121. Next for AP02 I have a new beginning balance of 4121, I then need to calculate AP02 using or pulling AP02 Dept. ERP data and loading it into [current month].

Sample data:

AP02 Balance = 4121

Rec1: 4121 - 100 (new AP02 Dept ERP actual data)

Rec2: 4021 - 21 (new AP02 Dept ERP actual data)

Rec3: 4000 - 200(new AP02 Dept ERP actual data)

What I need to do is get the last value of the dept. and use it as the beginning balance.

Two things I'm trying to accomplish is how to get the rolling balance and how do I use last value as the beginning balance for the next AP Period. I think this and a combination of .NET will address my problem.

I hope this helps.

Thanks,

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 3

Hi,

This is what I have so far:

Output:

Dept	ASSold	CurrentMonth	New Balance	Adjusted_ETC
6723	4515	124	4391	0
6724	4515	50	4351	10
6725	4515	100	4241(should be 4251)	0
6726	4515	120	4121	0

SQL:

I'm not sure how to review the adjusted ETC column because the current month should be subtracting 100 from 4351 not 4341. Below is my SQL any helpful suggestions. The update works fine when the adjusted_etc is not used. Can't quite figure this one out.

Thanks

If NOT EXISTS(SELECT adjusted_etc FROM test_etc WHERE adjusted_etc > 0)

UPDATE a SET a.unadjusted_etc = c.newbalance

FROM

test_etc a INNER JOIN (SELECT a.dept,a.assold - (SELECT SUM(b.currentmonth) FROM test_etc b WHERE a.dept >= b.dept) newbalance FROM test_etc a) c

ON a.dept = c.dept

If EXISTS(SELECT adjusted_etc FROM test_etc WHERE adjusted_etc > 0)

UPDATE a SET a.unadjusted_etc = c.newbalance + a.adjusted_etc

FROM

test_etc a INNER JOIN (SELECT a.dept,a.assold - (SELECT SUM(b.currentmonth) FROM test_etc b WHERE a.dept >= b.dept) newbalance FROM test_etc a) c

ON a.dept = c.dept

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 4

Hi Everyone,

Here is the intial output of my issue

Id	Dept	ASSold	Actuals	AP01 Unadjusted Balance	AP01 Adjusted Balance
1	6723	4515	124	4391	0
2	6724	4515	50	4341	10
3	6725	4515	100	4241	0
4	6726	4515	120	4121	0

Step 1 updating the AP01 Unadjusted balance I accomplish this by using the following update

UPDATE a SET a.unadjusted_etc = c.newbalance
 FROM
  test_etc a INNER JOIN (SELECT a.dept,a.assold - (SELECT SUM(b.currentmonth) FROM test_etc b WHERE a.dept >= b.dept) newbalance FROM test_etc a) c
   ON a.dept = c.dept

Step 2

If there is a value > 0 in the adjusted column then I need to update the AP01 unadjusted balance from the record that contains the new adjusted balance add that new value + the existing balance and that becomes my new AP01 unadjusted balance. My new result is as follows:

Id	Dept	ASSold	Actuals	AP01 Unadjusted Balance	AP02 Adjusted Balance
1	6723	4515	124	4391	0
2	6724	4515	50	4351	10
3	6725	4515	100	4251	0
4	6726	4515	120	4131	0

The new AP01 unadjusted balance is now 4351 and going forward the actuals are subtracted using the new balance. How can I attack this problem any ideas

Thanks in advance