Project Purchase & budget tracking tool

[Chuck]

hi all,

just advising that i have put my template up on MS for thsoe who will
need a tool that tracks purchasing based on account codes & shows
calenderisation of those purchases

be advised that this tool was designed for my use but put general
functinoality in it to support others. mainly a project management
budget tool

http://office.microsoft.com/en-us/templates/TC300010861033.aspx?CTT=42

thanks for all those who helped me iron out the details

cheers

 

[Roger Govier]

Hi Chuck

Nice Template, but on Spend Calendar, shouldn't the formula in C12 be
=SUMPRODUCT(--('Purchase Record'!$G$6:$G$18>0),--(MONTH('Purchase
Record'!$G$6:$G$18)=C8),'Purchase Record'!$F$6:$F$18+'Purchase
Record'!$I$6:$I$18)

in order to also pick up the variation between Order Value and Actual
Invoice value?

 

[Chuck]

hi roger,

took me a while to understand what you were saying but i finally got
their, and yes, i would think you were right.

in my case, if i put a PO value, that would be final and would expect
the invoice to be the same, however, on what you said, it is true,
that the invoice is the true ammount over-riding the PO value.

i have ammended my origonal template on my PC and will update the MS
one accordingly when i get at home (as i dont have Office 2007 here)

thanks for the pick up
chuck

 

[Chuck]

just realized that i think i have to change the formula in the Actual
tab now accordingly.. just not sure what the formula is

 

[Roger Govier]

HI Chuck

Because there is only one sheet - Purchase Record - where you monitor
difference between PO value and Invoice Value, that is done on an invoice
Total basis. There is no breakdown to GL code level, hence you cannot
reflect that on your Actual Tab.

Maybe it would be better to leave your formula as it was on the Spend
Calendar, but add another row at 16 showing Invoice difference with the
formula

=SUMPRODUCT(--('Purchase Record'!$G$6:$G$18>0),
--(MONTH('Purchase Record'!$G$6:$G$18)=C8),'Purchase Record'!$I$6:$I$18)

and at row 18 have an overall difference which sums rows 14 and 16.

On another matter, the individual tabs 001, 002 etc, all have values in
them. The blank template should have nothing.
When they are removed, then you get errors all over the Spend Calendar.
These are created because of the formula
=IF('001'!$J$38>1,'001'!$J$38,"")
which should be amended to
=IF('001'!$J$38>1,'001'!$J$38,0)
and so on down the page
It might be better to make the formula
=IF(INDIRECT("'"&TEXT(A6,"000")&"'!$J$38")>1,INDIRECT("'"&TEXT(A6,"000")&"'!$J$38"),0)
and copy down.
This would enable more rows to be added to cope with additional tabs beyond
013.
However, if you want to have more than 13 tabs, then you would also need to
amend the formula on the Actual tab from
=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),
"000")&"'!C24:C37"),C8,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!J24:J37")))
to
=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(list,"000")
&"'!C24:C37"),C7,INDIRECT("'"&TEXT(list,"000")&"'!J24:J37")))

where list is a range of cells containing the numbers 1 through 13. I put
the list on the Notes tab, with PO List as a heading in F1, and the numbers
in F2 onward.
Insert>Name>Define>
Name List
Refers to =OFFSET(Notes!$F$",0,0,COUNT(Notes!$F:$F))

When the user adds more tabs, e.g. 014, they would add the number 14 in cell
F15.

Hope these suggestions help.