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.
Week 4 total
Week 5 total
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?
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.