Smart list Value > Member Name

 

How to read a member name in a calcmgr rule from smart list value

Smart Lists
Service Administrators use Smart Lists to create custom drop-down lists that users access from form cells. When clicking in cells whose members are associated with a Smart List (as a member property), users select items from drop-down lists instead of entering data. Users can't type in cells that contain Smart Lists. Smart Lists display in cells as down arrows that expand when users click into the cells.

Smart lists will enhance the user interface and control while setting the assumptions and inputs that drives the further processing.

We all know that
In Calculation Manager, you can insert a Smart List into a variable or into formula, script, condition, and member range components.

Problem Statement

If you look at the above form, it is “add new asset” forms, part of capex model. New assets are introduced at different months. We should calculate the asset schedule from the period of the asset acquisition. That involves the calculation of Gross Asset, Depreciation, Accumulated Depreciation, Net Asset Value. Here we focus only on Depreciation Calculation as the purpose is to understand the member lookup from the smart list

For a given asset, “New_Asset_001”, I wanted to calculate the depreciation from May onwards, for the “New_Asset_002”, from Jan onwards, This should happen based on the form selections, and dynamic.
Code should not be required cycle through all twelve periods for each asset to figureout the asset start period.

Solution Part:

From the different sources I learned We could reverse engineer a member name from the smart list value as below:

@MEMBER(@CONCATENATE("HSP_ID_", @hspnumtostring("AssetStartPeriod"->"BegBalance")))

Okay!!!

Can I use this function in FIX/ENDFIX to get the list of periods from the asset form. Let’s try this

FIX(@MEMBER(@CONCATENATE("HSP_ID_", @hspnumtostring("AssetStartPeriod"->"BegBalance"))):"Dec")

No, it’s not working. Let’s try the same using if/endif.

if (@ISMBR(@MEMBER(@CONCATENATE("HSP_ID_", @hspnumtostring("AssetStartPeriod"->"BegBalance"))) : "Dec"))

Hmmm. This got validated! Let me execute and see if it works as expected.

Results are good. 

Let me modify the start periods and run

 

Looks alright!

How this works? We should know how system read it. Let me try with a return function to know how system read.

@return(@CONCATENATE("HSP_ID_", @hspnumtostring("AssetStartPeriod"->"BegBalance")),error);

  Cannot calculate. Essbase Error(0): HSP_ID_2155886880967624

  Can this “HSP_ID_2155886880967624 be an alias hidden inside and  represent the period "Feb".

If I supply this string into @member function, what it reads.

Here you go – It is Feb

Note:

It works only if smart list is created from members.

And see to that code return a proper value always. Any #MISSING or Invalid Numeric ID will not work. Let me comment the if ("AssetStartPeriod"->"BegBalance" <> #MISSING) so that system process other assets also with missing data

Here it tried to process "New_Asset_002" also, and failed as it returned invalid period in the smart list lookup

Let me revert the changes.

It worked here as it didn't process "New_Asset_002" as the condition if ("AssetStartPeriod"->"BegBalance" <> #MISSING) filtered the "New_Asset_002". 

Hope this helps. Thanks. 

 

Statement of Cash Flows if at all prepared by a Techie

Imagine that you run a retail store, you find your long-term loyal customers are short of money due to recession and job cuts, you let them order the groceries ‘on the credit. You are therefore creating turnover, but there is no money inflow stemming from this soon.

This means that no funds are flowing in for the purchase of new goods, payment of employees’ salaries and utility bills. While this problem does not appear on the income statement (groceries ‘on the credit are considered income), or only with a substantial delay, it becomes directly visible in the cash flow statement, as the net profit shown on the income statement is adjusted for transactions in which the company has not (yet) received an inflow of money.

How to calculate Statement of Cash Flows

The cash flow statement is the central element of any financial statement analysis. Since the income statement is not adjusted for non-cash items, only the cash flow statement shows the true cash flows to and from the company during the year.

We have two methods:
The direct method: reports gross cash inflows and gross outflows from operating activities but is not as widely used as the indirect method as it is more complex to implement.

The indirect method: uses a format that differs from the direct method only in the section where net cash provided or used by operating activities is calculated.

The investing and financing sections of the statement of cash flows are exactly the same under either method.

What do we need?
Information used to prepare this statement is obtained from the income statement for the year and comparative balance sheets for the last 2 years. Net income is adjusted in order to convert the accrual basis income statement to cash flows.

As discussed earlier, the statement which follows is divided into operating, investing, and financing activities. When we use the indirect method to determine cash flows, we start with the net income figure from the income statement and adjust the net income amount to determine the net amount of cash provided or used in operating activities.

Let us use the accounting equation and modify it slightly by splitting cash from assets.

1)    Cash + Non-Cash Assets = Liabilities + Equity

If we solve for Cash, we have:

2)    Cash = Liabilities + Equity − Non-Cash Assets

We use the symbol Δ(delta) to define “change in” and apply it to the revised accounting.

3)     ΔCash = ΔLiabilities + ΔEquity − ΔNon-Cash Assets

If we split now:

Non-Cash Assets = Fixed Assets (FA) + Accounts Receivable (AR) + Inventory (INV)

(FA) = CAPital EXpenditure (CAPEX) − Depreciation (Dep)

Liabilities = Long-Term Debt (LTD) + Short-Term Debt (STD) + Accounts Payable (AP)

D = Debt = LTD + STD

Equity = Share Capital (SC) + Retained Earnings (RE) = Net Income (NI) − Dividends (Div)

Retained Earnings (RE) = Net Income (NI) − Dividends (Div)

This means that by examining the liabilities, equity, and non-cash asset accounts for changes from one period to another we can explain the change in the cash account.

we may rewrite the equation 3 as follows:

4)    ΔCash = Δ(LTD + STD + AP) + Δ(SC + RE) − Δ(FA + AR + INV)

5)    ΔCash = ΔD + ΔAP + ΔSC + Δ(NI − Div) − Δ(CAPEX − Dep) − ΔAR – ΔINV

Then let me substitute. WCR = Working Capital Requirements = AR + INV – AP 

6)    ΔCash = ΔD − ΔWCR + ΔSC + ΔNI − ΔDiv – ΔCAPEX + ΔDep   

7)    ΔCash = (NI + Dep − ΔWCR) − ΔCAPEX + (ΔSC + ΔD − Div)

n  Operating Cash Flow = NI + Dep – ΔWCR

n  Investing Cash Flow = ΔCAPEX

n  Financing Cash Flow = ΔSC + ΔD − Div

8) Ending Cash Balance = Opening Cash Balance + ΔCash

Eq 8 is a mathematical derivation of the cash flow statement under the indirect method.

Statement of Cash Flows is a great tool to reconcile the accrual method of accounting to the cash basis of accounting, and also to examine the changes in current assets and current liabilities.

Reference:

[1] Financial Forecasting, Analysis, and Modelling - A Framework for Long-Term Forecasting
MICHAEL SAMONAS