This lesson explains how Excel can turn a financial forecast into a connected workbook. You will follow the flow from sales assumptions through the income statement, balance sheet, and cash forecast.
A forecast workbook usually begins with a sales forecast. Excel formulas make it possible to update projected results quickly when an assumption changes. For example, if sales are expected to grow by 18% from the prior year, a formula can multiply prior-year sales by 1.18. In the example workbook, cell O4 uses ='Figure 18.12'!N4*1.18 to reference the prior-year figure and apply the growth assumption.
All flashcards
How does Excel apply an 18% sales growth assumption?
It multiplies the referenced prior-year sales amount by 1.18, increasing the base amount by 18%.
What is the purpose of distributing annual sales by month?
It assigns monthly forecast sales based on each month’s share of prior-year annual sales, optionally adjusted for current expectations.
How can the forecast estimate accounts receivable?
It can sum the relevant months of sales based on the assumed collection period; for a roughly 90-day collection period, it may use the current month and three prior months.
How is forecast retained earnings updated?
Add forecast net income for the current period to the prior year’s ending retained earnings balance.
Why link ending cash to the next month’s beginning cash?
The link carries the cash balance forward consistently and ensures that each month begins with the previous month’s ending amount.
Quiz questions
Which relationship should hold on a balanced forecast balance sheet?Answer: Total assets equal total liabilities plus total equity.
If inventory is estimated using cost of goods sold and a four-month planning window, which months’ cost of goods sold may be included?Answer: The current month and the next three months.
If utilities are paid within 30 days, which expense figure may determine February’s utility cash outflow?Answer: January’s forecasted utilities expense.
Why should calculated subtotals generally use formulas rather than manually typed amounts?Answer: Formulas update the subtotal when its underlying values change.
What does a nonzero balance-check result indicate when total liabilities and equity are subtracted from total assets?Answer: A difference exists and should be investigated in the workbook.
Based on Principles of Finance by Julie Dahlquist, Rainford Knight (OpenStax), licensed CC BY 4.0. Adapted into a summary, flashcards and quizzes.