Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to automatically update a 12 month budget
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to automatically update a 12 month budget
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to automatically update a 12 month budget
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" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to automatically update a 12 month budget
DV = Data Validation (droplists)
1. DV created in C2 down to select Frequency: Monthly, OneTime This DV is created via selecting the range, say C2:C10, then clicking Data Validation, Allow: List, Source: Monthly, OneTime (Manually typed) 2. DVs created in D2 & E2 down to select "Begin MthYr" & "End MthYr", using defined Name: MthYr, Refers to: =z!$G$1:$R$1 First, create the defined name via clicking InsertNameDefine 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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to automatically update a 12 month budget
Thanks Max, works great!
"Max" wrote: DV = Data Validation (droplists) 1. DV created in C2 down to select Frequency: Monthly, OneTime This DV is created via selecting the range, say C2:C10, then clicking Data Validation, Allow: List, Source: Monthly, OneTime (Manually typed) 2. DVs created in D2 & E2 down to select "Begin MthYr" & "End MthYr", using defined Name: MthYr, Refers to: =z!$G$1:$R$1 First, create the defined name via clicking InsertNameDefine 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 --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to automatically update a 12 month budget
"epu" wrote:
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to increment budget automatically when entering a date? | Excel Worksheet Functions | |||
Divide Monthly Sales Budget to Day Budget | Excel Worksheet Functions | |||
automatically update chart plotting current month and previous 6 | Charts and Charting in Excel | |||
YTD Budget Sum if Actual Month has activities | Excel Worksheet Functions | |||
How do I set up a Yearly, by month, budget in Excel? | Excel Discussion (Misc queries) |