0 Replies Latest reply: Sep 10, 2010 10:38 AM by mlombard RSS

    Financial Statements, Problem using SET Analysis

      I need to create a financial statement that includes a percent of NET SALES column for three time frames, (ACTUAL, BUDGET, PREVIOUS MONTH) . I am using a chart table . I need to divide the line entries by their respective NET SALES amount. (ACTUAL LINE items divided by ACTUAL net sales total, BUDGET LINE items divided by BUDGET net sales total, , PREVIOUS MONTH LINE items divided by PREVIOUS MONTH net sales total)

      I have tried to use Set Analysis to do this by creating three variables but I only get the first variable value for all three columns in the TABLE

      vNETSACT=SUM({<(TYPE={ACT}),LINEID={NET SALES}>}AMOUNT)

      vNETSBUD=SUM({<(TYPE={BUD}), LINEID={NET SALES}>}AMOUNT)

      vNETSPRE=SUM({<(TYPE={PREV}), LINEID={NET SALES}>}AMOUNT)

       

      And then create an expressions with

       

      Columns that have these calulations

      The spreadsheet I need to replicate looks like this:

      SUM(AMOUNT)

       

      SUM(AMOUNT)/vNETSA



      SUM(AMOUNT)

       

      SUM(AMOUNT)/vNETSBUD



      SUM(AMOUNT)

       

      SUM(AMOUNT)/vNETSPRE

       



       

       

      ACTUAL

      ACTUAL

      BUDGET

      BUDGET

      PREVIOUS

      PREVIOUS

      ITEM

      Amount

      % NS

      Amount

      % NS

      Amount

      % NS

      Gross Sales of Goods

      61,781

      114.8%

      52,974

      105.9%

      55,488

      112.9%

      Sales Discounts

      7,970

      14.8%

      3,159

      6.3%

      6,842

      13.9%

      Net Sales

      53,811

      100.0%

      50,000

      100.0%

      49,145

      100.0%

      Administration Cost

      825

      1.5%

      1,081

      2.2%

      870

      1.8%

      Income/Expenses I

      -138

      -0.3%

      68

      0.1%

      -79

      -0.2%

      Indirect Cost of Distribution

      1,439

      2.7%

      959

      1.9%

      844

      1.7%

      Market and Sales - Organization

      2,632

      4.9%

      2,250

      4.5%

      2,762

      5.6%

      Research & Development I

      82

      0.2%

      73

      0.1%

      82

      0.2%

      Extraordinary Items

      -138

      -0.3%

      68

      0.1%

      -79

      -0.2%