6 Replies Latest reply on Mar 28, 2014 7:55 AM by ianmanning

    Need help, Looping calc to find break even point between 2 yearly costs

    ianmanning

      Title

      Need help, Looping calc to find break even point between 2 yearly costs

      Post

           I am trying to write a calculation to work out when the cost of an item with a high initial outlay and low running costs pays for itself against an item with no initial outlay but high running costs. This is slightly complicated by the fact that this is being done on a summary field.

           After a survey of a customers premises I have identified all their currently installed lighting and have alternatives with much lower running costs. I end up with a related table with say 25 fluorescent lights of 4 different lengths and their LED alternatives.

           I have three summary fields to give me the total cost of all the installed lighting in year 1 , the total cost of all the alternative lighting in year 1 and the total cost of all the alternative lighting after year 2.

           year 1 includes all the initial cost of the new lights, Year 2 gives the incremental cost  ie year 1 plus the running cost for year 2

           I could write this using a case statement with 10 tests, as pay back will be less than 10 years, but I'm sure it can be done more elegantly and I thought I should learn how to do it.

           It could also be done with a script that loops, and a script trigger but again I think I should learn how to do it with a calculation.

           This is essentially the calculation I need.

           A = installed lighting year 1

           B = LED lighting year 1

           C = LED lighting Year 2

           D = C - B (cost of running the LED lights for a year)

           E = B - D ( initial cost of the LED Lights)

           Find when    A* years > E + ( D * years)

           How do I create the Loop in a calculation field?

           Thanks

           Ian

            

            

            

            

        • 1. Re: Need help, Looping calc to find break even point between 2 yearly costs
          philmodjunk

               I'm a bit puzzled by your initial set up. Why is the installation cost and "running cost" (Cost to pay utility for power?) combined for year 1 such that your calculation has to then separate out this cost by subtracting? Seems like these would be two different costs to record in two different fields for year 1 with the installation cost fields left blank for year two. And if so, there would be no need to do additional calculations to isolate the running and installation costs.

          • 2. Re: Need help, Looping calc to find break even point between 2 yearly costs
            ianmanning

                 Hi PhillModJunk,

                 I agree it looks very strange.

                 The simple answer is that they are the fields which are saved in each of the individual survey records.

                 There are about 30 variables depending on the type of lighting that is installed and that although a record is generated for each light in the survey, only the data that is actually displayed or necessary to calculate the displayed data is actually recorded. There is not much point in recording a value that can be calculated if it is not displayed.

                 The customer is not very interested in the individual lights, they want to know what the overall savings are for the whole factory or location.

                 The savings are worked out over the lifetime of our Lights (50,000 hours) which vary from 6 to 28 years depending on what the working hours are in the factory or area within the factory. I could have a field for all 28 years but most of them would be empty. If I were then to sum all the year1, year2... results then the results would look pretty strange if half of the lifetimes were 10 years and half were 20 years as in year 11 there would only be half the data.

                 I use a script to graph the savings for an individual light record and record the costs of years 1,2,5,10 and lifetime during this script. These are the values which are summarised for the overall report.

                 The payback or breakeven year calculation based upon the year 1 & 2 figures will be wrong if the life time of some of the lights are different and payback works out to be greater then 6 years, however a payback time of greater then 5 years is not interesting to a customer so the inaccuracy is irrelevant.

                 This is how I have currently solved it but I would like to learn how to do it with a looping calculation as this would be impractical for a longer sequence.

                 Let ( [ Installed = Total Cost year1 ; Ban1 = Total Cost year1 Banner-(Total Cost year2 Banner -Total Cost year1 Banner); BanI =Total Cost year2 Banner -Total Cost year1 Banner] ;
                 Case ( Installed > Ban1+ BanI ;"Year 1" ;
                 Installed * 2 > Ban1+( BanI * 2) ;"Year 2" ;
                 Installed * 3 > Ban1+( BanI * 3) ;"Year 3" ;
                 Installed * 4 > Ban1+( BanI * 4) ;"Year 4" ;
                 Installed * 5 > Ban1+( BanI * 5) ;"Year 5" ;
                 Installed * 6 > Ban1+( BanI * 6) ;"Year 6" ;
                 Installed * 7 > Ban1+( BanI * 7) ;"Year 7" ;
                 Installed * 8 > Ban1+( BanI * 8) ;"Year 8" ;
                 Installed * 9 > Ban1+( BanI * 9) ;"Year 9" ;
                 Installed * 10 > Ban1+( BanI * 10) ;"Year 10" ;
                 "after Year 10" )
                   )

                  

                 Thanks for your support.

                 Best regards

                 Ian

                  

                  

            • 3. Re: Need help, Looping calc to find break even point between 2 yearly costs
              philmodjunk
                   

                        The simple answer is that they are the fields which are saved in each of the individual survey records.

                   But then why don't you change the design of how you save this data so as to simplify the needed cost calculations that you need to do?

                   

                        The customer is not very interested in the individual lights, they want to know what the overall savings are for the whole factory or location.

                   But that does not mean that you can't separate installation and operating costs at the time you initially record the data--whether you record this data separately for each light or in the aggregate in a single record for the whole facility.

              • 4. Re: Need help, Looping calc to find break even point between 2 yearly costs
                ianmanning

                     Hi PhilModJunk.

                     I do not have a problem with changing the design, I could separate the initial cost from the incremental cost, but I'm not sure I understand how this helps.

                     it would eliminate the Let part of my calculation but if there were more years it would still be impractical.

                     Let ( [ Installed = Total Cost year1 ; Ban1 = Total Cost year1 Banner-(Total Cost year2 Banner -Total Cost year1 Banner); BanI =Total Cost year2 Banner -Total Cost year1 Banner] ;
                     Case ( Installed > Ban1+ BanI ;"Year 1" ;
                     Installed * 2 > Ban1+( BanI * 2) ;"Year 2" ;
                     Installed * 3 > Ban1+( BanI * 3) ;"Year 3" ;
                     Installed * 4 > Ban1+( BanI * 4) ;"Year 4" ;
                     Installed * 5 > Ban1+( BanI * 5) ;"Year 5" ;
                     Installed * 6 > Ban1+( BanI * 6) ;"Year 6" ;
                     Installed * 7 > Ban1+( BanI * 7) ;"Year 7" ;
                     Installed * 8 > Ban1+( BanI * 8) ;"Year 8" ;
                     Installed * 9 > Ban1+( BanI * 9) ;"Year 9" ;
                     Installed * 10 > Ban1+( BanI * 10) ;"Year 10" ;
                     "after Year 10" )
                       )

                     I think that I must be missing something really obvious or I am not being very clear in my description.

                     Thanks

                     Ian

                • 5. Re: Need help, Looping calc to find break even point between 2 yearly costs
                  philmodjunk

                       The point is to simplify how your data is organized before you attempt to calculate an answer thus producing a simpler expression to get the needed result. Your Case function doesn't really make sense to me. I'd have one record for each theoretical year. The Installation cost fields for year 1 would contain the data for the labor and materials costs needed to install that lighting option. Those fields would be blank for subsequent years. The operating cost value would then either be the same for each year's record or specified by you or a script if you have some kind of model for how the operating costs would change over time.

                       A calculation field then simply adds all of these fields to get the cost for each year. A summary field computes the total cost over each year. A script can then compare the total in this summary field to your "break even" total (which can be stored in either a variable or a global field), by duplicating the year 2 record in a loop until the total is greater than or equal to that specified break even amount. The number of records in your found set then become the total number of years needed to reach the break even point.

                       If that works for you, it would be possible to set up a recursive custom function that computes the same value in a loop without using a set of records to do so. Recursive custom functions require FileMaker Advanced in order to create them.

                  • 6. Re: Need help, Looping calc to find break even point between 2 yearly costs
                    ianmanning

                         Thanks for all your advise. I have now solved it. it was much simpler than I had thought.

                         I asked my son who is a mathematical genius and he solved it in about 5 seconds.

                         The point at which the two lines on the graph cross, the breakeven point is:

                         - initial cost of the alternative / ( incremental cost of the alternative - incremental cost of the current)

                         This gives a decimal year which is then simply changed into years and months.