4 Replies Latest reply on Jun 17, 2013 6:47 AM by jlamprecht

# Need help with budget solution

Hi everyone, being that I'm self taught in FM and have to develope only when needed at work my skills are rusty and limited. I need help with a budget solution the boss wants. It sounds pretty basic but as I thought it through there were some things I just don't know how to do.

The boss wants a weekly budget for various department heads. Each department is given a certain amount of money for the fiscal year and he wants, on a weekly basis, a report that shows if they are above or below thier budget displayed as a percent. He wants it to look something like this but in a horizontal list view.

Unit

Week 4 total

Week 5 total

YTD total

Over/Under%

Allocation

Remaining

The data is being impoerted weekly from an Excel file and it is very basic, just unit names, dates and amount of money are the only fields that are relavent. The problem I'm having is how do I figure out the over/under% on a weekly basis. I was thinking that FM needs to know how many weeks into the budget we are to properly compute that. For example, the Training Dept is allocated \$1,000,000 for this year, that's \$19,230/week or \$83,333/month. Twelve weeks in lets say their YTD total spent is \$240,000. To be on coarse for thier budget they should be at \$230,760, so they are 4% over. This math is easy but the problem I have is how do I get FM to know it's the 12th week, or any other week? Is their some formula that for every 7 days it counts one and adds them up to know what week it is?

Here are the fields I have so far, let me know if I'm on the right track. Obviously I have fields for unit, weekly amount, ytd total, date and allocation. For allocation I put a Case function in there that looks like this. I figured it would display the proper allocation for each unit depending on which was displayed in the unit field. Is this proper?

Case (

unit="patrol"; "\$1,000,000";

unit="set"; "\$500,000";

unit="vci"; "\$250,000";

etc...

)

Each unit (there are approximately 30) will need a seperate weekly average to maintain budget that needs to be calculated for any given week during the year. This is where I'm stuck. I just don't know how to formulate this. Any help would be greatly appreciated. Thanks.

Gregg

• ###### 1. Re: Need help with budget solution

I don't think that Case function is correct or I'm not using it properly or I should be doing differntly altogether. Just to test something I made a field for percent of budget used. I used this formula in a "percent_used" field: z_ytd_total * 100 / allocated (allocated is a field where the above Case staement is). When I went to the layout all the different units showed the same answer irregardless of their respective budget numbers. How do I get it to calculate each unit?

• ###### 2. Re: Need help with budget solution

Gregg,

Just a couple of quick notes:

First, you should look closely at the Date category of functions for FileMaker. You will probably need to feed a given date into WeekOfYear or WeekOfFiscalYear functions to learn what week it is. And watch out for weeks that span years! because no year has exactly 52 weeks. http://www.filemaker.com/help/html/func_ref1.31.12.html

Regarding your Case statement, you may need a separate table to handle the units and their allotments.

Hope this helps a little,

Debi Rubel
FullCity Consulting

• ###### 3. Re: Need help with budget solution

Thanks Debi,

Unfortunately our fiscal year starts July 1st and I can't figure out how to adjust WeekOfYearFiscal to account for that, it defaults to Jan 1. Even after doing a Google search I couldn't find a solution. Ugh

• ###### 4. Re: Need help with budget solution

Here is a custom function I found for fiscal start dates outside of Jan. 1:

http://www.briandunning.com/cf/147

It shows start month and day. I hope it helps.

-JohnAustin