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% |
