Financial Projections Workbook
Insert Your Name of Firm HERE
Financial Projection Model
YEAR OF FIRST INPUT DATA SALES (LAST YEAR) SALES GROWTH (YEARS +1 TO +5) COGS/SALES (% OF SALES) SALES EXPENSE (% OF SALES) ADMINIST. EXPENSE (% OF SALES) DEPRECIATION EXPENSE (% FIXED/ASSETS) INTEREST INCOME LAST YEAR ($) INTEREST EXPENSE LAST YEAR ($) INTEREST RATE - DEBT (%) INTEREST RATE - INVESTMENTS (%) TAX RATE (% OF B/T INCOME) LAST PREFERRED STOCK DIVIDEND ($) FUTURE PREFERRED STOCK DIVIDENDS ($) LAST COMMON STOCK DIVIDEND ($) FUTURE COMMON DIVIDENDS ($) BEGINNING CASH BALANCE MINIMUM CASH BALANCE (% SALES) BEGIN MARKETABLE SECURITIES ACCOUNTS RECEIVABLE (% SALES) INVENTORIES (% SALES) OTHER CURR. ASSETS (% SALES) BEGIN FIXED ASSETS DOLLAR AMOUNT CHANGE IN FIXED ASSETS YEAR +1 CHANGE IN FIXED ASSETS YEAR +2 CHANGE IN FIXED ASSETS YEAR +3 CHANGE IN FIXED ASSETS YEAR +4 ACCUMULATED DEPRECIATION ACCOUNTS PAYABLE (% SALES) TAXES PAYABLE & OTHER ACCRUALS BEGINNING SHORT TERM DEBT & NOTES SHORT-TERM LOANS - DO NOT ENTER --> OTHER CURR. LIABS. (% SALES) LONG-TERM DEBT & LIABILITIES NEW LONG TERM DEBT YEAR +1 NEW LONG TERM DEBT YEAR +2 NEW LONG TERM DEBT YEAR +3 NEW LONG TERM DEBT YEAR +4 PREFERRED STOCK STARTING COMMON STOCK STARTING RETAINED EARNINGS STOCK ISSUE YEAR +1 STOCK ISSUE YEAR +2 STOCK ISSUE YEAR +3 STOCK ISSUE YEAR +4 EXTRAORDINARY GAIN / (LOSS) YEAR 0 EXTRAORDINARY GAIN / (LOSS) YEAR +1 EXTRAORDINARY GAIN / (LOSS) YEAR +2 EXTRAORDINARY GAIN / (LOSS) YEAR +3 EXTRAORDINARY GAIN / (LOSS) YEAR +4
INPUT DATA INPUT DATA INPUT FORMAT FUTURE YEARS LAST YEAR 2001 $7,000 88.00% 7.00% 0.00% 5.00% $50 $140 6.00% 87.00% 7.00% 0.00% 5.00%
YEAR (LAST COMPLETED YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT (LAST YEAR)
36.00% $0 $86
8.50% PERCENTAGE - ANNUAL RATE (DECIMAL) 6.00% PERCENTAGE - ANNUAL RATE (DECIMAL) 36.00% PERCENTAGE (DECIMAL)
FIXED DOLLAR AMOUNT (LAST YEAR)
$0 $90 $100 $300 10.00% 13.00% 0.00% $3,463
FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT (LAST YEAR)
2.00% PERCENTAGE (DECIMAL)
FIXED DOLLAR AMOUNT (LAST YEAR)
10.00% PERCENTAGE (DECIMAL) 13.00% PERCENTAGE (DECIMAL) 0.00% PERCENTAGE (DECIMAL)
FIXED DOLLAR AMOUNT (LAST YEAR)
$338 $31 $42 $10 $0 10.00% 6.00% $0 PLUG 8.00% $1,523 10.00% 6.00% 0 PLUG 8.70% $0 $0 $0 $0 $0 $2,242 $0 $0 $0 $0 $0 ($118) $0 $0 $0 $0 1.11 5.00%
DOLLAR AMOUNT DOLLAR AMOUNT DOLLAR AMOUNT DOLLAR AMOUNT FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) COMPUTED BY SPREADSHEET PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT
INFORMATION FOR VALUATION OF FIRM BETA OF FIRM'S STOCK (EXISTING) (BL) ASSUMED RATE OF GROWTH
NUMBER PERCENTAGE - KD MUST > GROWTH
NOTE: ALL DATA BELOW ARE COMPUTER GENERATED 2001 LAST YEAR $8,000 $50 $7,040 $1,010 $560 $0 $173 ($118) $395 $140 $255 $92 $163 $0 $86 $77 2002 YEAR +1 $8,480 $28 $7,378 $1,131 $594 $0 $190 $0 $347 $140 $206 $74 $132 $0 $90 $42 2003 YEAR +2 $8,989 $35 $7,820 $1,203 $629 $0 $192 $0 $383 $129 $253 $91 $162 $0 $90 $72 2004 YEAR +3 $9,528 $51 $8,289 $1,289 $667 $0 $193 $0 $429 $129 $300 $108 $192 $0 $90 $102 2005 2006 YEAR +4 YEAR +5 $2,000 $2,120 $42 $43 $1,740 $1,844 $302 $319 $140 $148 $0 $0 $196 $194 $0 $0 ($33) ($24) $129 $129 ($163) ($154) ($59) ($55) ($104) ($98) $0 $0 $90 $90 ($194) ($188)
INCOME STATEMENT SALES INTEREST ON MARK. SECS. & CASH COGS GROSS PROFIT SALES/DISTRIBUTION EXPENSE ADMINISTRATIVE EXPENSE DEPRECIATION EXPENSE EXTRAORDINARY GAIN (LOSS) EBIT INTEREST EXPENSE (S/T & L/T DEBT) PROFIT BEFORE TAX TAX EXPENSE PROFIT AFTER TAX PREFERRED STOCK DIVIDENDS COMMON STOCK DIVIDENDS EARNINGS RETAINED BALANCE SHEET CASH MARKETABLE SECURITIES EXCESS CASH ACCOUNTS RECEIVABLE INVENTORIES OTHER CURRENT ASSETS FIXED ASSETS ACCUMULATED DEPRECIATION TOTAL ASSETS ACCOUNTS PAYABLE TAXES PAYABLE & OTHER ACCRUALS SHORT TERM LOANS & NOTES (With Interest) NEW SHORT-TERM LOANS REQUIRED OTHER CURRENT LIABILITIES LONG-TERM DEBT/LIABILITIES PREFERRED STOCK COMMON STOCK RETAINED EARNINGS TOTAL LIABILTIES AND EQUITY ESTIMATE DEBT/EXCESS CASH TRIAL ASSETS TRIAL LIABILITIES AND EQUITY PLUG: DEBT (EXCESS CASH) PERFORMANCE MEASURES ROE ROA RETURN ON SALES TIMES INTEREST EARNED TOTAL DEBT TO ASSETS LONG TERM DEBT TO ASSETS SHORT TERM DEBT TO ASSETS DEBT TO EQUITY EARNINGS PER SHARE CURRENT RATIO QUICK RATIO ACCOUNTS RECEIVABLE TURNOVER AVERAGE COLLECTION PERIOD INVENTORY TURNOVER FIXED ASSET TURNOVER CASH FLOW RETURN ON ASSETS
$100 $300 $0 $800 $1,040 $0 $3,463 $0 $5,703 $800 $480 $0 $18 $640 $1,523 $0 $2,242 $0 $5,703
$170 $300 $0 $848 $1,102 $0 $3,801 ($190) $6,031 $848 $509 $0 $129 $738 $1,523 $0 $2,242 $42 $6,031
$180 $300 $102 $899 $1,169 $0 $3,832 ($382) $6,099 $899 $539 $0 $0 $782 $1,523 $0 $2,242 $114 $6,099
$191 $300 $353 $953 $1,239 $0 $3,874 ($575) $6,334 $953 $572 $0 $0 $829 $1,523 $0 $2,242 $216 $6,334
$40 $300 $367 $200 $260 $0 $3,884 ($771) $4,281 $200 $120 $0 $0 $174 $1,523 $0 $2,242 $22 $4,281
$42 $300 $373 $212 $276 $0 $3,884 ($965) $4,122 $212 $127 $0 $0 $184 $1,523 $0 $2,242 ($166) $4,122
$5,703 $5,685 $18
$6,031 $5,902 $129
$5,998 $6,099 ($102)
$5,981 $6,334 ($353)
$3,913 $4,281 ($367)
$3,749 $4,122 ($373)
7.27% 2.86% 2.04% 2.82 27.02% 26.71% 0.32% 0.69 $0.73 1.16 0.62 10.00 36.50 6.77 2.31 ----
5.79% 2.19% 1.56% 2.47 27.40% 25.25% 2.14% 0.72 $0.59 1.09 0.59 10.00 36.50 6.69 2.35 #REF!
6.88% 2.66% 1.80% 2.96 24.97% 24.97% 0.00% 0.65 $0.72 1.19 0.67 10.00 36.50 6.69 2.61 #REF!
7.80% 3.03% 2.01% 3.32 24.04% 24.04% 0.00% 0.62 $0.86 1.29 0.76 10.00 36.50 6.69 2.89 #REF!
-4.60% -2.43% -5.21% -0.26 35.58% 35.58% 0.00% 0.67 ($0.46) 2.36 1.84 10.00 36.50 6.69 0.64 #REF!
-4.73% -2.38% -4.63% -0.19 36.95% 36.95% 0.00% 0.73 ($0.44) 2.30 1.77 10.00 36.50 6.69 0.73 #REF!
INSTRUCTIONS AND INFORMATION ABOUT FINANCIAL PROJECTIONS
NOTE: MUST SET NUMBER OF ITERATIONS ON EXCEL. (TOOLS: OPTIONS: SET NUMBER (50); .99 CHANGE LEVEL ) NOTE: ONLY CHANGE 1 VARIABLE AT A TIME; ENTER THE DATA, HIT RETURN/ENTER, AND ALLOW COMPUTER TO PROCESS THE CHANGE. AFTER THE CHANGE HAS BEEN PROCESSED, ANOTHER VARIABLE MAY BE CHANGED. This Sheet: Allows for New Long-Term Debt Shows L/T & S/T Debt Ratios Allows for Changes in Fixed Assets The Marketable Securities and Short-Term Loan Accounts are "Plug" Accounts; they are used to balance the Balance Sheet. Interest Income is credited for Cash, Marketable Securities, and Excess Cash at the Interest Rate for Investments Interest Expense is Charged on Long-Term Debt and Short-Term Loans at the Cost of Debt Interest Rate
Page 5
