The 12-Month Cash Flow Analysis is a crucial tool for illustrating how merchandise planning and cash flow directly influence the overall health and sustainability of a business.

Step 1: Access the Data Worksheet

Begin with the Data Worksheet, which can be found on Tab 6 of the workbook. This worksheet draws most of its data from the merchandise plan itself.

1. Set the Current Month

   • Locate the gray field labeled CURRENT MONTH and enter today’s date.

   • The Excel workbook will automatically convert this date into "Mon-YY" format and update all subsequent months accordingly.

2. Input Key Financial Data You will need the following information to populate the worksheet:

   • Sales

   • Inflow (Total Cash Coming In)

   • Expenses

   • Cost of Goods Sold (COGS)

   • Cash on Hand

   • Payables (Outstanding Obligations)

   Sources of Information:

   • The current month’s merchandise plan

   • Average monthly expenses derived from the financials (automatically calculated by dividing total yearly expenses from the Break-Even Analysis worksheet by 12)

   • Last month’s P&L (Profit & Loss statement)

   • Last month’s merchandise plan

   • Last month’s balance sheet

Once all relevant data is entered, proceed to Tab 7 to view the 12-Month Cash Flow Analysis itself.

Step 2: Understanding the 12-Month Cash Flow Analysis

The 12-Month Cash Flow Analysis is divided into three main sections:

1. Last Month Status

2. 12-Month Cash Flow Projections

3. 12-Month Cash Position

1. Last Month Status

The Last Month Status section (highlighted in purple) provides a comparative overview of how the business performed last month versus the merchandise plan. The primary focus here is on cash flow, which reflects the overall financial health.

This section shows two comparisons:

• Planned vs. Actuals from the P&L: Compares planned figures against actual results from the Profit & Loss statement.

• Planned vs. Plan Actuals: Compares planned figures against actual results recorded in Retail ORBIT®.

By analyzing these two perspectives, you can assess whether:

• Sales figures from the P&L align with the Retail ORBIT® data.

• Net inflow from Retail ORBIT® corresponds to the COGS shown on the P&L.

Example Insight: In the provided example, the retailer exceeded their sales goal by 57%, while increasing expenses by only 11% and overshooting purchasing by 17%. This indicates strong performance but also suggests the need to monitor expense growth closely.

Step 3: Utilizing the Analysis for Strategic Decisions

The 12-Month Cash Flow Analysis provides insights that guide strategic planning and decision-making. By regularly updating and reviewing this tool, you can:

• Identify Cash Surpluses or Shortfalls: Plan proactively for periods where cash flow may be tight.

• Monitor Expense Trends: Keep track of whether spending aligns with planned forecasts.

• Adjust Merchandise Strategies: Ensure purchasing decisions align with cash flow realities.

By consistently using the 12-Month Cash Flow Analysis alongside merchandise planning, businesses can maintain financial clarity, adapt to changing market conditions, and make informed decisions that promote long-term success.

• Copy of FinancialAnalysisWorkbook_6-3_BLANK.xlsx600 KB