CashFlowMapper - Tips & Tricks

General – “How do I deal with…?”

1.   A quarterly transaction occurs, however in one quarter, it occurs one month later.

• Set up 4 x annual transactions. Just because it’s a quarterly transaction doesn’t mean using a quarterly frequency is the best way to handle it.

2.   A transaction that occurs both mid-month and end of month.

• Set up 2 x monthly transactions: one for mid-month, and one for end of month.

3.   A transaction that will change in value periodically.

• If the transaction changes value annually, use the inflation feature.
• If the transaction changes in value more frequently:
  • Set up the transaction, including start and finish dates.
  • Copy the transaction, altering the value and start and finish dates again.
  • Use the factor feature as needed.
  • Repeat as often as necessary.
• If the transaction occurs daily, weekly, or monthly, use the FX calendar to apply a factor every recurrence to reflect the variability you are planning.
• For all the examples above, grouping the transactions will have them appear as one line in all reports, online and exported.

4.   Transactions that are dependent on other transactions, such as variable costs occur.

• Use the copy transaction feature.
• Set up the first transaction. Copy it.
• Rename and change the value of the copied transaction. If the transaction will occur a few days before or after the first transaction, change the start date.

5.   How to create a daily transaction that only occurs from Monday to Friday.

• Create the daily transaction.
• Create a weekly transaction for the negative amount of the daily transaction to start on the first Saturday in your plan.
• Repeat the above transaction commencing on the first Sunday in your plan.

6.   Track budgeted cash flow against actual cash flow.

• Create your budget in a plan. Let’s say it covers a 12-month period.
  • Using the budget as a template, every month, create a plan using your actual. Each month, you can merge the monthly plans into a single plan, adding each month as you go.
  • Overlay the merged actual graph on the budget graph for an easy comparison.
  • Export the respective Model – By Transaction Reports for each plan for a numerical comparison.

7.   Credit card transactions.

• There are two ways to forecast these.
  • Make a schedule of all transactions paid by credit card outside of CFM and plan these as one transaction on the day you pay your credit card.
  • Plan all credit card transactions individually but group them for reporting purposes.

Inflation

1.   Apply inflation / deflation across all or some transactions effective from the same date.

• Assuming the first date you want to apply inflation from is 1^st July 2022, ensure your transactions all have an end date of no later than 30^th June 2021.
• Copy the transactions and have them start on 1^st July with the inflated value (if they are to be inflated on 1^st July 2022) and apply the inflation factor to the transactions. The factor will be applied every 1^st July for the life of the plan.

Merging Plans

1.   Ensure that transactions in the files to be merged will not be extended in the merged file.

• Ensure that the transactions have either:
  • A finish date before the next recurrence, or
  • A specified number of recurrences so that the transaction only occurs as many times as needed.

2.   Make identifying the source of transactions easy.

• If you know that a plan is going to be merged into another plan, in the description field for transactions, reference the name of the original plan. For example, “plan 1 payroll”, “plan 2 payroll” etc.
• In the merged plan, when viewing the transactions, model, and graph, it will be obvious which plan each transaction originated from.

Exporting Plan Files

1.   How to save exported csv files for use in spreadsheets.

• Export the file and save with .csv extension.
• Open the file in your spreadsheet program. Save it as a spreadsheet.
• You can now use this version of the file as you would any spreadsheet, and you still have your original export as a csv file.

2.   Don’t try and used a .csv file as a normal spreadsheet workbook.

• Always save your download as a spreadsheet before using it for other purposes. If you add more sheets to the workbook of a .csv file, they’ll be lost when you open the file again, along with any formatting or formulae you use.

3.   The exported model shows hyphens instead of zero for cells with a zero value.

• Highlight the entire sheet.
• Click Ctrl-H.
• In the “find” field enter “-“.
• In the “replace field enter “0”.
• Enter. The hyphens have been replaced with zeroes.

Plan Details & Transactions Template -

Importing / Exporting Plans

1.   Use the download template as a base for all your upload files.

• Download the file, save it as a .csv file. Keep a copy of it as it is for reference purposes regarding the syntax of each field. If an upload contains any text that doesn’t match this syntax, it won’t work.
• For a reusable template, clear the contents of cells B2:B7 & A11:N19.
• You now have a template to base all future uploads on.
• For scenarios, keep a template with your base descriptions to save re-entering these.

2.   Shortcuts for recurrence & frequency.

• From the Import Rules file, simply copy / paste values the options in cells A5:A8 (recurrences) cells D11:D17(frequencies) as required into your template.

3.   Dates.

• As long as the underlying text of the date in the import file is per the required syntax, the appearance of them is irrelevant.
  • The dates must be entered in “d/mm/yyyy” format, which will give the required syntax for the upload.

4.   Scenario generation.

• Saving a copy of a file, editing it and then uploading the edited version is a quick way of producing plans under varying scenarios for comparison to each other.

5.   Error messages.

• The line number refers to the row number in the upload file, not the actual transaction number.
• Keep the Transaction Builder page open so you can note the line numbers that need correction.

6.   Use any data in a csv file for import.

• Use your internet banking csv or accounting system exports as a base to import into CashFlowMapper™.
• You can import data from any source, as long as the template syntax requirements are met.
• The most common causes of errors in import files are stray space characters either before or after numerical data. Check your data for these before importing.
• The second most common causes of error are commas. These cannot be anywhere in your file; they prompt the software to think there’s a new column of data after the comma, and therefore causes errors.

Foreign Exchange Template

1.   Ensuring syntax of dates is correct.

• As long as the underlying text in the import file is per the required syntax, the appearance of them is irrelevant.
  • FX Daily, Weekly & Monthly: dates must be entered in “d/mm/yyyy” format.
  • FX Yearly: whole number to be entered.

Reporting

1.   Plan Details & Transactions.

• Use this report as a quick way of making scenarios / variations by exporting, editing, and re-importing to a new file. Where you have a large plan with many edits to do, this is much more efficient than manually altering transactions.
• By keeping all versions for a planning period in one spreadsheet workbook (remember to save them as a spreadsheet to facilitate this), you can easily compare plans for the differences between them.

2.   Occurrence Dates Report.

• Use this report as a quick check of the maths around the transactions and checking that the correct basis is used for tax return transactions.
• The calculation on a transaction, where all features are used is:
  • Amount +- factor *inflation rate / FX rate.

3.   Model – Calendar Report.

• This report gives you a numerical replica of the online calendar version of the model.
• This will enable easy analysis of which days of the week have the most activity. Have the online version of the report running so you can get explanations of these movements via the commentary.

4.   Model – Transaction Report.

• Use this report as a basis for a “traditional” cash flow forecast report:
  • The report exports with all data as text. Manipulation of the report is therefore easy.
  • You already have the month-to-date and plan-to-date opening & closing balances and activity – in total and by transaction.
  • If necessary, sort your transactions into “inflows” and “outflows” and add subtotals for these – and there’s your standard cash flow forecast report.
• It may be desirable to export related transactions in groups. For example, all project-related inflows and outflows may be grouped together and subtotalled.
• Once you have a preferred ordering of transactions for this report, re-order transactions in the plan so they export in that order.

5.   Graph.

• To get the most useful graphical reports out of CashFlowMapper do screen dumps showing relevant commentary. This provides a cash flow report that any reader can understand.
  • The tooltips have 3 levels of commentary: balance, grouped, and transaction details.
  • There are 3 options for tooltip frequency: monthly, weekly and all (daily).
• To isolate specific days on the graph:
  • Scroll and zoom into the graph and click the tooltips for your selected day(s).
  • Click “Reset zoom”, and the graph will return to default dimensions, but keep your tooltips.
  • Drag the tooltips into any position you like.
  • Click “Export” for your report, or discuss live.