Time Diagrams and Cash Flows in Excel

A Time Diagram is a visual representation that illustrates the timing and magnitude of cash flows over a specific period. It’s a graphical tool used to understand when cash inflows (money coming in) and outflows (money going out) occur during the life of an investment.

Basic Theory:

Time Diagrams: A time diagram visually represents the timing and magnitude of cash flows over a specific period. It helps investors and analysts understand when cash inflows and outflows occur during the life of an investment.

Cash Flows: Cash flows represent the movement of cash into and out of a business. Positive cash flows indicate incoming cash, while negative cash flows represent outgoing cash. Net cash flow is calculated by subtracting the outflows from the inflows.

Procedures:

1. Creating a Time Diagram:

1. Data Input:
   • Enter the time periods in one column (e.g., Year 0, Year 1, Year 2).
   • Enter corresponding cash flows in another column.
2. Insert Chart:
   • Highlight the data range.
   • Go to the “Insert” tab and choose a scatter plot or line chart.
3. Customize:
   • Label axes, add a title, and format as needed.

2. Cash Flow Analysis in Excel:

1. Cash Flow Formulas:
   • Use Excel formulas such as SUM for total cash inflows and outflows.
   • Calculate net cash flow using =SUM(Inflows) - SUM(Outflows).
2. Discounted Cash Flow (DCF):
   • Apply DCF analysis using the NPV (Net Present Value) function in Excel.
   • Set the discount rate and input future cash flows to find the present value.

Comprehensive Explanation:

Let’s consider a scenario:

Scenario:

A company is considering an investment that requires an initial outlay of $50,000. Projected annual cash inflows are $20,000 for the next three years.

Calculation:

1. Time Diagram:
   • Create a table in Excel with columns for “Year” and “Cash Flow.”
   • Enter data: Year 0 (Outflow: -$50,000), Year 1-3 (Inflows: $20,000 each).
   • Create a line chart representing the time diagram.
2. Cash Flow Analysis:
   • Use the SUM function to calculate total inflows and outflows.
   • Calculate net cash flow: =SUM(Inflows) - Outflow.
3. Discounted Cash Flow:
   • Assuming a discount rate of 10%, use the NPV function.
   • Formula: =NPV(10%, Inflows) - Outflow.

Result:

The time diagram visually shows the initial investment in Year 0 and positive cash flows in Years 1-3. The net present value calculation helps determine if the investment is financially viable.

Other Approaches:

Payback Period:

Calculate the time it takes to recover the initial investment. Use Excel’s IRR (Internal Rate of Return) function to find the rate at which the project breaks even.

Sensitivity Analysis:

Explore different scenarios by adjusting variables like cash inflows, outflows, and discount rates to assess the investment’s sensitivity to changes.