• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quarterly Tracking of Transactions against specified Approval Limits

Hi all Dashboard Enthusiasts / Experts - Friends,

Have a challenging Database cum Dashboard problem....will try my best to explain....
I DO NOT EXPECT A COMPLETE SOLUTION......
Any type of Conceptual help like giving Model formulas that can be adopted or Ideas to create a better data model will really help.....

I need to create a Data Model and Reports based on following facts.
(I am also attaching a file showing some basic work I have done.... I am not able to figure out How am I going to track 60 to 100 parties with each having 1 or 2 or 3 ....up to 6 different types of transactions )

My company name is X.
X belongs to a group named SG.

SG has other companies named A, B, C, D, E……..and so on.
X enters into 5 or 6 types of transactions with each of the Companies A, B, C, D, E…….
Suppose transaction types are T1, T2, T3, T4, T5, T6.

Amount of transactions X can enter with other related companies is limited by approvals provided by a Legal Committee that monitors such transactions..

I want to track the following metrics:

a) Approvals taken against each Party & Transaction type combination for any given quarter:

Opening Balance, Consumption, Closing Balance

Closing Balance of Q1 will be the opening Balance of Q2


b) Depending on Comparison of Approval Amount with Transaction Amounts in that period, we need to track if the approval is

insufficient then highlight the same where approvals are less than Transactions
OK if no more approval is required ( Approval = transaction )
Excess available where approvals are greater than Transactions

c) At the end of a period of year which is 1st September to 31st August, in case there is any excess approval, the same will get lapsed

d) Based on trend of Transactions and Approvals we should be able to take Approvals in advance


Hope I have not made it too complex......in fact it is a complex problem that needs to be simplified !!
 

Attachments

  • Simulation Model.xlsx
    49.5 KB · Views: 17
Hello Folks,
For those who cared to understand this thread, apologies for posting almost a full time a software project.....but to update you, i managed to put together a report that automatically captures monthly and quarterly data and automatically shows trends of Quarterly Approvals V/s Transactions (these are like Debits V/s Credits). Inspired from Chandoo Small Business Dashboard style !! Now I am struggling to work out a mechanism that will provide interface for closing and Carry forward mechanism of Account at the year end..... Any directions, ideas, VBA scripts will help....
I am posting a file of my work with some dummy data.....
Never mind if you find it complex and give it a pass, Thanks for taking time to looking at this thread !

Thanks to all Ninjas and Champs who endeavor to find time and help here!
kj
 

Attachments

  • RPT-DB V3.1-dummy data model for forum.xlsx
    662.9 KB · Views: 22
Back
Top