Tracking Cash in Portfolio Slicer
Portfolio Slicer can work in 2 modes – “Track Cash” mode and “Do not track Cash” mode. For simplicity, we recommend that you start using Portfolio Slicer in “Do not track cash” mode. Then when you are comfortable with transaction data entry and reports, you can choose to switch to “Track Cash” mode. You can change how Portfolio Slicer tracks cash in "Config" table.
“Track Cash” mode
In “Track Cash” mode Portfolio Slicer uses special symbol “* Cash” (“star” symbol then “space” then word “cash”) to assign cash balance. That value is calculated in account currency, but for reporting purpose result could be converted to any reporting currency. When account has no transactions, that account cash balance value is 0. When you enter transaction, Portfolio Slicer knows how that transaction impacts cash balance, so to calculated cash balance at any point in time Portfolio Slicer simply adds each (signed) transaction cash impact from the first transaction up to last transaction for reported date.
Let say you have account with no transactions and you want to record purchase of 40 shares for symbol VWO. Before you can do purchase, you need to have money in that account – that means first you need to deposit money into account. If you will not deposit money, Portfolio Slicer will calculate that after this transaction cash balance value is negative and that negative cash balance will be included in all your reports.
So proper way to record your 1st purchase would be like this:
As you can see in the 2 right columns, Portfolio Slicer for each transaction instantly calculates TotalAmount and CashBalance.
TotalAmount column value represents unsigned cash impact of that transaction in specified accounts (first column) currency.
CashBalance column shows what your cash balance amount is AFTER current transaction. You should monitor calculated CashBalance amount to make sure it does not become negative. Exception is that you might have negative amount if ON THE SAME DAY you have other transactions that change that balance amount back to positive value. So really rule is – at the end of the day, after all transactions for that date are accounted for, your cash balance amount should be greater or equal to zero. You can turn off CashBalance column calculation by changing cell “src!N4” value to anything else than “Yes”. This CashBalance column is used just in Trans table, but it is not used in any further Reports out of Power Pivot data model.
To work with cash Portfolio Slicer uses 2 transaction types “Deposit” and “Withdraw”. For both transaction types you specify amount deposited/withdraw in the column Price and you should use positive numbers for both transaction types. There is also transaction type “Int” that is used to record interest received from cash, but really this transaction type is the same as dividends received, just “Int” transaction type case the symbol is “* Cash”. For these 3 listed transactions you must always use symbol “* Cash”.
“Do not track Cash” mode
In this mode Portfolio Slicer assumes that at any point account Cash Balance value is 0. So if you deposit money into account, that transaction will be ignored. If you record “Dividends received” transaction, dividend amount you specified will be properly allocated to “Dividends” received calculation, but your cash balance amount after this transaction will stay 0 – so you can assume that all money from dividends you received were instantly withdraw from account.
Cash or No cash?
We recommend that you start using Portfolio Slicer in “Do not track Cash” mode. It is easier to manage your portfolio in this mode as it requires you to enter less transactions. But at the same time, when you do not track cash, you do not see full picture of your investments, for example your Rate of Return calculation will not account for cash that is not invested. So after you get comfortable with using Portfolio Slicer, you should consider switching to “Track Cash” mode.