Metrics (Cash Flow) and Excel Risk Mitigation (#29)

In this episode, we review the ways in which Excel spreadsheets can fail and how to mitigate these risks, and also discuss the essential metrics relating to cash flow. Key points discussed concerning Excel risk mitigation are:

  • Excel is useful for rapid system construction.

  • It is possible to set up data validation in Excel, but this feature is rarely used.

  • A general failing is the high degree of company-wide access to Excel spreadsheets.

  • A user can have a false sense of security when using Excel, not realizing the ways in which it can issue false results.

  • The best risk mitigation approach is to identify the most critical spreadsheets in the business and focus on improving them; review for complicated formulas that are likely to fail.

  • There are spreadsheet problem detection products on the market that can be applied to Excel.

  • Use the Excel feature to view all formulas; makes it easier to peruse them.

  • Look for hard coded figures within a range of formulas; more prone to error.

  • Investigate cells containing high-dollar values.

  • Conduct data analyses for large data sets within a spreadsheet.

Key points relating to the metrics for cash flow are:

  • Always use a few cash flow measurements as a basis of comparison to other metrics.

  • Cash flow return on sales; shows the amount of cash generated as a percent of sales.

  • Cash flow return on assets; less subject to manipulation than return on investment metrics.

  • Fixed charge coverage; use it to see if most of a firm’s cash flow is used to pay for its fixed expenses.

  • Cash to current assets ratio; a high proportion of cash indicates a high level of liquidity.

  • Cash to current liabilities; provides a very conservative view of cash requirements.

  • Cash flow to debt ratio; see if there is sufficient cash flow available to pay down debt obligations.

Related Courses

Business Ratios Guidebook
Introduction to Excel
Excel Formulas and Functions