4 Replies Latest reply on Apr 9, 2012 5:04 AM by OlivierRamoudt

    Income/Expense report - layout or structure problem?

    OlivierRamoudt

      Title

      Income/Expense report - layout or structure problem?

      Post

      I read a post by PhilModJunk on income/expense reporting but I'm stuck on how I could make my solution work with a lineitems type of table that Phil suggests. (the post: http://preview.tinyurl.com/cap3p96)

      From what I understand from Phil the basic database structure should look something like the screenshot I have attached.

      I'm in the events business, and during an event I have a lot different types of income & costs and at the end of the night I would like to know the profit/loss I have made. 

      On the income side I basically have: bar takings, entrance tickets, cloak room takings.

      On the cost side I have: venue rent, insurance, artist cost etc.

      The problem arises when I try to house these incomes and costs in two tables: 'incomedetail' and 'costdetail' and link them to my events table through a cost/income line items table and use that line items table as a portal on my event layout. My sources of income aren't just final amounts that I can type into the field corresponding to the source of income.

      For instance for my bars I just need to type in the total amount: bar1cashier1total bar1cashier2total bar2cashier1total etc where these would be the total amounts of cash typed into the cash registers.

      For the entrance I need to have: entrance1price1, entrance1price2, entrance2price1, entrance2price2, entrance1amountsoldprice1, entrance1amountsoldprice2, where the total amount of cash generated is amount sold x type of ticket.

      All my sources of income are calculated in a different way, therefore not allowing for a straighforward lineitems setup where you choose your type of income and just fill in the amount.

      This quickly becomes a very long list of different types of income. In total I have about 43 different sources of income on one event all calculated in a different way.

      I don't see how I could link the 'incomes' table to a 'lineitems' table and have that line items table appear as a portal on my 'events' layout without there being just too many fields on the lines portal in the limited space the portal row will offer me. I need to be able to house 43 different types of income on one layout. I fear a portal can never work taking this into account. Do I need to rethink my database and create different tables for all my different sources of income and have their fields appear individually on my Events table instead of working with a portal? If that's the case, I'm worried about how I'll be able to run a report to calculate my profit/loss for the event.

      Any help is more than welcome.

      screen-capture.jpg

        • 1. Re: Income/Expense report - layout or structure problem?
          GuyStevens

          Could you give a little bit more information and especially examples of the different types of income?

          Maybe we can find a way to solve this problem.

          But I'm a very visual guy, I need to see what it is you are trying to do.

          Right now I can't imagine how you would work with this.

          For instance, what kind of data would you give in?

          What kind of calculations do you need to make?

          Maybe we could make an "Income type" table with calculations in it for the different kinds of income.

          • 2. Re: Income/Expense report - layout or structure problem?
            OlivierRamoudt

            Hi Dasaint, first of all thank you for your reply.

            I don't think explaining it visually will make it more clear, mayby by comparing it to say the following simple solution will make my problem more clear. Suppose that we are using a basic database to process orders. This would in my view have the following tables:

            ORDER---<LINEITEMS>---PRODUCTDETAILS

            In the product details table you would have a series of fields such as product name, description, price, weight etc. In this solution you have to assume that all your products obey to a uniform set of fields so that they fit into the fields you have specified. For instance:

             prod1  coca cola  10$  nonalcoholic
             prod2  fanta  10$  nonalcoholic
             prod3  red bull  15$  energydrink
             prod4  budweiser  75$  alcoholic

             

             

             

             

             

             

            If we transpose this to my solution this would give:

            EVENT---<COSTANDINCOMELINES>---INCOMEDETAIL

            The problem arises from the fact that my sources of income are not uniform the way the items in my product detail table are, so I can't see how a record in the Income Detail table can hold all the fields I need. Here are three examples

            Bar revenue is the amount my cash registers read at the end of the night (bar1revenuetotal, bar2revenuetotal, bar3revenuetotal etc)

            Ticket sales is a calculated result (ticketprice1xamountsold + ticketprice2xamountsold etc) I can have anything up to 12 entrances at an event selling tickets at 4 different prices.

            Cloak room revenue is a calculated result (coatsprice x amountsold + bagsprice x amountsold etc) I can have anything up to 6 cloak rooms at an event.

            The different way in which income is generated (direct input and calculated results) and the amount of physical places where the income comes from (12 entrances and 6 cloak rooms, and up to 10 bars) is leaving me struggling with how I could fit this all on to the CostAndIncomeLines portal on the Events layout.

            I'm thinking in the direction of using as many tables as there are sources of income and linking those to my CostAndIncomeLines table, and housing the Lines portal on my Events layout under a set of tabs.

            If it's still not clear let me know and I'll make something more visual.

            • 3. Re: Income/Expense report - layout or structure problem?
              GuyStevens

              I think I understand what you are looking for and I think the answer lies in multiple line items tables.

              I scetched out your structure and made a quick little example file.

              I hope that comes close to what you are looking for.

              http://dl.dropbox.com/u/18099008/Demo_Files/Income_Events_DaSaint.fp7

              Let me know if you have any more questions.

              Note that this is just a really quick test and it's very basic. You can take this structure and go with it as far as you want. You could have tables for the cash registers and add extra details. You could put in pre determined ticket prices etc.

              The sky is the limit.

              • 4. Re: Income/Expense report - layout or structure problem?
                OlivierRamoudt

                Hi Dasaint,

                What can I say? Thank you!

                I had come to the same conclusion pretty much since the sources of income are too diverse in their creation to house them in one table, let alone one record in that table at a time. It's just that I try to minimize the amount of tables I use to keep the relationships graph as sober as possible. But hey sometimes you need to branch out. :)

                I've had a look at the sample file you sent me, thanks for putting me on the right track. I'll let you know if I bump into any other obstacles while trying to elaborate on the flie you sent me.

                thanks again.