AI Prompts for Financial Analysis: Forecasts, Cash Flow and Financial Models

A quality-of-earnings review adjusts reported profit to remove one-off and misclassified items, giving normalised EBITDA: earnings before interest, tax, depreciation and amortisation from repeatable operations. Every forecast should start from this figure.

• One-off income: a $0.9M Amazon settlement was recorded as revenue, although no customer bought anything.
• Misclassified cost: $1.2M of photography and video was recorded as an asset. It is marketing spend and should be an expense.
• Owner pay: the founder is paid $0.4M more than a market salary for the role, so the excess is added back. $3.1M − $0.9M − $1.2M + $0.4M = $1.4M.
• Cash conversion: only 31 cents of cash arrived for every dollar of reported profit. The difference went into unsold stock.

Unit economics measures the profit or loss on a single sale. The key measure is contribution margin: revenue minus the costs caused directly by that sale, such as product cost, shipping and payment fees.

• Average across channels: Kestrel keeps $87 on a $184 average order value (AOV), a 47% contribution margin.
• By channel: customer acquisition cost (CAC), the marketing cost of winning one customer, is $23 through organic search and $124 through paid social. After returns, paid social runs at −4% and marketplace at −11%.
• LTV/CAC: customer lifetime value divided by acquisition cost. At 2.1, a customer brings in about twice what they cost to acquire, but this depends on an unconfirmed retention rate.

Variance analysis explains the difference between budgeted and actual results. A price-volume-mix bridge splits a revenue variance into volume (units sold), price and mix (which products sold), and the parts must add up to the total.

• Revenue variance: volume −$3.1M, price +$0.9M and mix −$0.2M add up to the $2.4M shortfall.
• Margin variance: of 13 points of gross margin lost, discounting caused 5.2, freight and a supplier change 4.1, mix 1.9, and lower volume over fixed costs 1.8.
• One-off or structural: a structural cause continues into the next quarter. The discounting is structural, because the excess stock that causes it is still in the warehouse.

Product profitability analysis ranks each product, or SKU (stock-keeping unit), by the contribution it generates. At Kestrel, 11 products earn 79% of contribution and 15 lose money.

• Operating leverage: how strongly profit changes when sales change, driven by fixed costs such as rent and salaries. Kestrel’s is 3.4, so a 10% fall in revenue reduces profit by 34%.
• Cost allocation: the method used to assign shared costs to products. Charging warehouse costs by storage space instead of per unit turns two more products into losses.
• Diagnosis: the problem is product mix, not pricing.

A driver-based forecast calculates revenue from the operating factors that produce it, instead of adding a growth percentage to last year. Kestrel’s revenue = site sessions × conversion rate × average order value × repeat purchases.

• Most important driver: the conversion rate. Raising it from 2.1% to 2.4% adds $6.2M over the forecast period.
• Break point: below 1.9% conversion, the forecast cannot be achieved.
• Monitoring: acquisition cost, four-week repeat rate and return rate are tracked every month.

A three-statement model links the income statement (profit), the balance sheet (what the company owns and owes) and the cash flow statement (cash in and out). Because the three statements describe the same business, they must reconcile. A mismatch means an assumption is wrong.

• The failed check: closing cash in the cash flow statement is $2.1M lower than on the balance sheet.
• The cause: the model assumed stock is held for 71 days, while past results show 118 days.
• Common practice: many models adjust a figure until the statements match, which hides the error from the board.

A 13-week cash flow forecast projects cash week by week, because payroll and loan payments fall on specific dates. A covenant is a condition in a loan agreement; Kestrel must keep at least $500K in cash.

• The breach: cash falls to $310K in week 9, below the $500K minimum.
• Cash conversion cycle: the number of days cash is tied up in operations. Kestrel pays for stock 118 days before selling it, collects from customers in 4 days and pays suppliers in 38: a cycle of 118 + 4 − 38 = 84 days.
• Binding constraint: working capital, the cash tied up in stock and receivables. Each day of stock holds about $78K of cash: $48M of revenue at a 59% cost of goods is $28.3M a year, or $78K a day.

Capital budgeting evaluates an investment with three measures. Net present value (NPV) is the value of future cash flows in today’s money, minus the investment. Internal rate of return (IRR) is the annual return the investment generates. Payback is the time needed to recover the cost.

• NPV at 12%: $1.9M. The 12% hurdle rate is the minimum return Kestrel requires from an investment.
• IRR and payback: an IRR of 19%, above the hurdle rate, and 3.4 years to recover the $4.1M.
• Excluded benefit: “improved customer experience” was left out because nobody owns or measures it. Including it would raise the NPV to $2.6M.

Sensitivity analysis tests how much the outcome changes when one assumption changes; scenario analysis changes several assumptions together. A tornado chart ranks assumptions by their impact. Kestrel’s largest are conversion rate, freight and return rate.

• Break point: below 38.4% gross margin, fixed-charge cover falls under the lending agreement’s minimum. Fixed-charge cover is the profit available to pay fixed financing costs, divided by those costs.
• Realistic downside: the downside scenario models lower sales and higher costs together, not one at a time.
• Decision trigger: if the four-week repeat rate is below 18% by March, the Q3 stock order is cut by 40%.

A governing thought is the single sentence the board should remember from the presentation. For Kestrel: the company has a working-capital problem that presents as a margin problem.

• Three numbers: 11 weeks of cash, 41% gross margin and 118 days of stock, each shown with a comparison and a trend.
• The request: a markdown programme to sell excess stock and a $3M credit facility, approved by 14 March.
• Q&A preparation: seven likely questions ranked by potential damage, starting with the most likely one: why did Kestrel buy so much stock?