Structural question: Business Assessment

[shak]

Hello everybody,

I'm a bit undetermined about the structural design of a business assessment in FileMaker.

Here's what I need and have:

Table 'Positions'

Table 'Values'

Table 'Categories'

I display the positions table, which contains the fields year, company, positionText and a calculation field with a fix value for every time period that has records in the table "Values" (M1...M12, Q1-Q4,Y). The "Values" table contains various fields for planning figures, actual figures and calculations with these figures.

Basically it looks like this:

year | company | category | customer or other planning positions | Y planning Fix* | Y actual/planning* | Y Delta* | Y actual* | Jan planning Fix* | Jan planning R* | Jan actual planning* | etc..  for every month and the quarters

* field in the "Values" table

Then there should be sub summaries by categories. These sub summaries should either show a total of the planning figures or subtract totals from other sub summaries.

Long story short: I'm a bit undetermined whether I should use the FileMaker sub summaries (part definition) with a lot of "hacks" and unstored calculations or create actual records for the (fake) sub summaries and use a lot of script triggers to update the values. I tend toward the second solution and think it's better for the overall performance.

Any thoughts?

Best

shak

[shak]

To make it more clear here's the basic structure (numbers don't necessarily make sense but you get the point):

[comment]

To be honest, I can't make heads or tails of it. What is it that you are actually tracking; what does it mean in real life?

[shak]

To be honest, I can't make heads or tails of it. What is it that you are actually tracking; what does it mean in real life?

The "Positions" records show planned and actual revenues for every customer (planning for the year, quarter and month). But there are also expenses like fees for freelancers, office expenses, etc. So it's basically a statement of revenues and expenditures.

At the bottom of the list it should give you the EBIT (earnings before interest and taxes).

[comment]

Isn't the planned/actual revenue/expense for a year the sum of planned/actual revenues/expenses for the months? Also, why would you have all the monthly values in the same record?

I am still not sure what is the gist of your solution. I would expect something like a table of Projections {CompanyID, CategoryID, Month, Amount} and a table of Actuals {CompanyID, CategoryID, Date, Amount}. Then you can summarize the actuals by any time period and/or category and compare the summary value to the projected value.

Of course, if your data already has a one-to-one pairing of projected vs. actual amount at the company/category/month level, you could use a single table with the two Amount fields. Still, there should be a separate record for each such pair of values.

[shak]

Thanks for your answer

Isn't the planned/actual revenue/expense for a year the sum of planned/actual revenues/expenses for the months? Also, why would you have all the monthly values in the same record?

Well, the monthly values aren't all in the same record. The Values table has one record for every month/quarter for every position - or did you mean that? The client just want's to display all the months, quarters and the year horizontally; it's not all in one table (this makes it more complicated to summarize).

I am still not sure what is the gist of your solution. I would expect something like a table of Projections {CompanyID, CategoryID, Month, Amount} and a table of Actuals {CompanyID, CategoryID, Date, Amount}. Then you can summarize the actuals by any time period and/or category and compare the summary value to the projected value.

Maybe my description wasn't that good. My problem really is the view of the data. The client wants the thing to behave like a spreadsheet (I know...) and needs to see exactly this view. And that makes it a bit complicated, because you have to have very different looking sub summaries all adding and subtracting different sums. That's why I thought about "simulating" the sub summary with a record in Positions (and also related records in Values) and populating it with the use of script triggers. Turning the view by 180° would be easier imho, but is not accepted :-)

Does this make it more clear?

[comment]

Well, it depends on how much data there is. Have a look at the file posted here:

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

I would also search the forums for crosstab to see some pre-11 approaches.

The client just want's to display all the months, quarters and the year horizontally;

What should be displayed vertically - and in what order?

[shak]

Well, it depends on how much data there is. Have a look at the file posted here:

http://fmforums.com/...-in-version-11/

I would also search the forums for crosstab to see some pre-11 approaches.

Thank you very much, I'll look into that.

What should be displayed vertically - and in what order?

Vertically positions and sub summaries should be displayed. Basically like a standard statement of income. The attachment (wikipedia) shows the sub summaries. "Sales Revenue", "Other Expenses", etc. are the parts which summarize the single positions (you should be able to collapse/expand the parts). The attachment shows only one column for values, as described above here I need multiple columns for different values and time periods (every month, quarter and - of course - the year; see above).

[comment]

How many companies do you want to display at the same time?

[shak]

How many companies do you want to display at the same time?

From 1 company to max. 6 companies at a time; you should be able to say "I want to see company A and company B" or "I want to see the whole group", etc. When more than one company is shown the client just wants to see the single positions of "Sales Revenues" and one other expense part. All the other parts should not show the single positions but only the sum (this is what I meant with expand/collapse).

[comment]

I am not sure Filemaker is the best tool to build such display. It wouldn't be too difficult to have a list view of Categories showing summary data for a selected company, using either filtered portals or dedicated relationships to show 17 columns (12 months, 4 quarters and year). I believe it wouldn't be prohibitively slow, either. To show 6 companies at the same time would mean calculating 102 columns "live" - this could be a point where you might prefer to use stored values.

Collapsing and expanding is not a trivial proposition either.

[Lee Smith]

To make it more clear ....:

please update your profile to show your FileMaker and Platform information click here http://fmforums.com/forum/index.php?app=core&module=usercp&tab=core&area=profileinfo

Lee