How to automatically update a 12 month budget

[epu]

I'm putting together a 12 month expense budget worksheet for each of our cost
centers. In it, I would like to be able to have the cost center managers
input for each applicable GL the annual amount, frequency of the expense
(monthly, quarterly, one time, etc) and have the worksheet automatically
calculate the 12 month budget for all the different frequency types. For
excample, if the cost center manager put in an annual amount of $800, a
frequency of monthly, and a beginning month of April, I'd like the worksheet
to automatically put in $100 in months April - December for that line item.
Same for the other frequencies. How can I do this? Thanks!

 

[Max]

Here's one formulas driven set up to deliver it ..

Illustrated in this sample:
http://freefilehosting.net/download/3lk5d
AutoUpdate 12 mth budget.xls

In A1:E1 are the col labels: GL#, Amt, Freq, Begin MthYr, End MthYr
In G1:R1 are 1st-of-month real dates for the 12 month period: Mar08 to Feb09
(formatted as: mmmyy)

1. DV created in C2 down to select Frequency: Monthly, OneTime
2. DVs created in D2 & E2 down to select "Begin MthYr" & "End MthYr",
using defined Name: MthYr, Refers to: =z!$G$1:$R$1

Then in G2:
=IF($C2="","",IF($C2="OneTime",IF(G$1=$D2,$B2,""),IF($C2="Monthly",IF(AND(G$1>=$D2,G$1<=$E2),$B2/DATEDIF($D2,$E2,"m"),""))))
Copy G2 across to R2, fill down as far as required

Usage
1. Input amount in col B, in B2 down
2. Select Freq
a. If Freq = OneTime, then select only "Begin MthYr"
to denote the MthYr for the placement (Leave End MthYr empty)

b. If Freq = Monthly, select from both "Begin MthYr" & "End MthYr"
to denote the period for the placement

The equally apportioned amount per month within the period
will be auto-calculated

For "Quarterly", just do as per step 2b,
select the begin & end mthyr for the quarter period
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[epu]

Thanks for the reply Max. Sorry, but I'm a total newbie. What does DV stand
for and what is the =z!$G$1:$R$1 formual mean? Thanks again

 

[Max]

DV = Data Validation (droplists)
This DV is created via selecting the range, say C2:C10, then clicking Data >
Validation, Allow: List, Source: Monthly, OneTime (Manually typed)

First, create the defined name via clicking Insert>Name>Define
Name: MthYr
Refers to: =z!$G$1:$R$1
Click OK

Then create the DV above via selecting the range, say D2:E10, then clicking
Data > Validation, Allow: List, Source: =MthYr
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

 

[epu]

Thanks Max, works great!

DV = Data Validation (droplists)

This DV is created via selecting the range, say C2:C10, then clicking Data >
Validation, Allow: List, Source: Monthly, OneTime (Manually typed)


First, create the defined name via clicking Insert>Name>Define
Name: MthYr
Refers to: =z!$G$1:$R$1
Click OK

Then create the DV above via selecting the range, say D2:E10, then clicking
Data > Validation, Allow: List, Source: =MthYr
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Max]

Thanks Max, works great!

Welcome. Take a moment to press the "Yes" button below from where you're
reading this.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---