Ceo Financial Assistant
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Ceo Financial Assistant as PDF for free.
More details
- Words: 5,359
- Pages: 45
THE CEO FINANCIAL ASSISTANT™ Simple to operate and especially designed for the CEO, The CEO Financial Assistant™ creates a concise corporate financial analysis report. It has at its core a framework that can be altered to specific needs. Accompanying The CEO Financial Assistant™ is a linked PowerPoint® Presentation of key charts. Proficiency in Microsoft Excel® and PowerPoint® is assumed.
For Best Viewing, please set your Screen Resolution to the highest resolution.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Please Note: There is No Support For Free Downloads
© Copyright, 2009, JaxWorks, All Rights Reserved.
Please Note: There is No Support For Free Downloads
© Copyright, 2009, JaxWorks, All Rights Reserved.
TABLE OF CONTENTS Introduction Auto Charts Net Income Chart Current Ratio Chart Instructions Optimal Performance Assets Chart Quick Ratio Chart Master Data Entry Break-Even Liabilities Chart Working Capital Chart Turnover Data Chart Annual Income Chart Balance Sheet Chart Stock Evaluation Expenses Data ChartQuarterly Income ChartCash Flow Statement Dashboard What-If Period 1 Support Sales Chart Cash Flow Chart What-If Period 2 Direct Expenses ChartCash Flow Projections Copyright What-If Period 3 Total Expenses Chart Cash Flow Projections Chart License Agreement What-If Period 4 Gross Profit Chart Ratios What-If Overview EBIT Chart Z-Score Chart © Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
INSTRUCTIONS You will be posting numbers from your financial statements to The Master Data Entry worksheet. This worksheet drives all other analysis sheets and charts. The s haded c ells contain formulas and are automatically calculated by Exc el. DO N OT enter any information into them. Enter numbers where they are BLUE. You may want to group all of the charts together at the end of the workbook by moving them to the order you prefer. This will not effect the Table of Contents worksheet, however, changing tab names will corrupt the hyperlinks. Each worksheet has been preset for printing at "Fit To 1:1", however, you may have to adjust some settings to your printer. The experienced spreadsheet operator can alter this product to produce a 4 year analysis rather than by quarter.
For Best Viewing Please Set Your Screen Resolution to 1024 X 768 Pixels.
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Clear Worksheet Add Sample Data
MASTER DATA ENTRY SHEET Year of Projection Corporation Type (C or S)? Operating Data Days sales in accounts receivable Days materials cost in inventory Days finished goods in inventory Days materials cost in payables Days payroll expense accrued Days operating expense accrued Expense Data Direct labor as % of sales Other payroll as % of sales Payroll taxes as % of payroll Insurance as % of payroll Legal/accounting as % of sales Office overhead as % of sales Financing Data (0 on) Long term debt Short-term debt Capital stock issued Additional paid-in capital Accumulated depreciation (as of 1999)
• Table of Contents
2000 C
'C' Corporation format selected; income taxes WILL be computed 1st Qtr 2nd Qtr 3rd Qtr 30 30 30 30 30 30 45 45 45 60 60 60 7 7 7 20 20 20
16.00% of sales 12.00% of sales 10.00% of payroll 5.00% of payroll 2.00% of sales 3.00% of sales
$320,000 $240,000 $56,000 $28,000 $40,000 $60,000
Depreciation
Capital
$100,000 $50,000 $400,000
© Copyright, 2009, JaxWorks, All Rights Reserved.
$240,000 $180,000 $42,000 $21,000 $30,000 $45,000
$208,000 $156,000 $36,400 $18,200 $26,000 $39,000
Current Portion $100,000 $50,000
LT Portion $500,000
4th Qtr 30 30 45 60 7 20
$321,616 $241,212 $56,283 $28,141 $40,202 $60,303 Rate 10.00% 10.00%
INCOME STATEMENT
4th Qtr
Total Annualized
$2,000,000 $945,000
$1,500,000 $865,000
$1,300,000 $833,000
$2,010,100 $1,071,616
$6,810,100 $3,714,616
Gross profit
$1,055,000
$635,000
$467,000
$938,484
$3,095,484
Total expenses
$424,000 $16,250 $32,500 $1,250 $474,000
Forecasted 2nd Qtr
$318,000 $16,250 $33,958 $1,250 $369,458
$275,600 $16,250 $33,958 $1,250 $327,058
$426,141 $16,250 $33,958 $1,250 $477,599
$1,443,741 $65,000 $134,374 $5,000 $1,648,115
Operating income
$581,000
$265,542
$139,942
$460,885
$1,447,369
Subtotal
$100,000 $20,000 $120,000
$10,000 $50,000 $60,000
$3,000 $100,000 $103,000
$405,700 $200,000 $605,700
$518,700 $370,000 $888,700
$2,336,069
1st Qtr Sales Sales Cost of sales
Expenses Operating expenses Interest Depreciation Amortization
Other income and expenses Gain (loss) on sale of assets Other (net)
Income before tax Please enter a tax percentage Taxes @
30% Net income
Retained earnings-beginning
3rd Qtr
$701,000
$325,542
$242,942
$1,066,585
$210,300
$97,663
$72,883
$319,975
$700,821
$490,700
$227,879
$170,059
$746,609
$1,635,248
$1,400,000
$1,890,700
$2,118,579
$2,288,638
$1,400,000
Dividends paid
$50,000
$50,000
$1,890,700
$0
$2,118,579
$2,288,638
$2,985,247
$2,985,248
$320,000 $500,000 $125,000
$240,000 $500,000 $125,000
$208,000 $500,000 $125,000
$321,616 $500,000 $250,000
$1,089,616 $2,000,000 $625,000
Depreciation: Enter the numbers of years. 30 year Buildings 10 year Equipment
$12,500 $20,000
$12,083 $21,875
$12,083 $21,875
$12,083 $21,875
$48,749 $85,625
Interest: Percentages from Data sheet 10.00% 10.00%
$1,250 $15,000
$1,250 $15,000
$1,250 $15,000
$1,250 $15,000
$5,000 $60,000
Retained earnings-ending
$0
$0
Detailed Supporting Information Cost of sales Direct labor Direct Materials Other costs
Long-Term Short-Term
BALANCE SHEET
Actual 1999 ASSETS Current Assets Cash and cash equivalents Accounts receivable Inventory Other current assets Total Current Assets Fixed Assets Land Buildings Equipment Subtotal Less-accumulated depreciation Total Fixed Assets Intangible Assets Cost Less-accumulated amortization Total Intangible Assets Other assets Total Assets
1st Qtr
Total Current Liabilities Non-Current Liabilities Long-term debt Deferred income Deferred income taxes Other long-term liabilities Total Liabilities Stockholders' Equity Capital stock issued Additional paid in capital Retained earnings Other Stockholders Equity Total Liabilities and Equity "C" Corporation (Y/N) Cash balance positive or (negative) Amount sheet is out-of-balance Amount cash flow out-of-balance
4th Qtr
$451,000 $350,000 $400,000 $10,000 $1,211,000
$90,360 $657,534 $630,411 $60,000 $1,438,305
$289,233 $493,151 $590,959 $45,090 $1,418,433
$614,196 $427,397 $575,178 $76,320 $1,693,091
$1,267,364 $660,855 $692,852 $50,000 $2,671,071
$100,000 $1,500,000 $800,000 $2,400,000 $400,000 $2,000,000
$112,500 $1,450,000 $875,000 $2,437,500 $432,500 $2,005,000
$125,000 $1,450,000 $875,000 $2,450,000 $466,458 $1,983,542
$137,500 $1,450,000 $875,000 $2,462,500 $500,416 $1,962,084
$150,000 $1,450,000 $875,000 $2,475,000 $534,374 $1,940,626
$50,000 $20,000 $30,000
$50,000 $21,250 $28,750
$25,000 $3,266,000
$33,000 $3,505,055
Actual 1999
LIABILITIES AND STOCKHOLDERS' EQUITY Current Liabilities Accounts payable Notes payable Current portion of long-term debt Income taxes Accrued expenses Other current liabilities
Forecast 2nd Qtr 3rd Qtr
1st Qtr
$50,000 $22,500 $27,500
$50,000 $23,750 $26,250
$50,000 $25,000 $25,000
$120,000 $3,549,475
$5,000 $3,686,425
$23,000 $4,659,697
Forecast 2nd Qtr 3rd Qtr
4th Qtr
$600,000 $100,000 $100,000 $30,000 $90,000 $16,000 $936,000
$328,767 $50,000 $100,000 $183,300 $83,288 $12,000 $757,355
$328,767 $50,000 $100,000 $70,663 $62,466 $12,000 $623,896
$328,767 $50,000 $100,000 $45,883 $54,137 $12,000 $590,787
$600,000 $100,000 $30,000 $50,000
$500,000 $90,000 $27,000 $90,000
$500,000 $90,000 $27,000 $40,000
$500,000 $90,000 $27,000 $40,000
$500,000 $90,000 $27,000 $40,000
$1,716,000
$1,464,355
$1,280,896
$1,247,787
$1,524,450
$100,000 $50,000 $1,400,000 $0 $1,550,000
$100,000 $50,000 $1,890,700 $0 $2,040,700
$100,000 $50,000 $2,118,579 $0 $2,268,579
$100,000 $50,000 $2,288,638 $0 $2,438,638
$100,000 $50,000 $2,985,247 $0 $3,135,247
$3,266,000
$3,505,055
$3,549,475
$3,686,425
$4,659,697
Y Positive $0
Positive $0 $0
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Positive $0 $0
Positive $0 $0
$328,767 $50,000 $100,000 $292,975 $83,708 $12,000 $867,450
Positive $0 $0
DATA CHART - TURNOVER
• Table of Contents
Number of Days
60 55 50 45 40 35 30 25 20 15 10 5 0
1st Qtr
Days payroll expense accrued Days finished goods in inventory
2nd Qtr
Days operating expense accrued Days materials cost in payables
3rd Qtr
4th Qtr
Days sales in accounts receivable
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Days materials cost in inventory
DATA CHART - EXPENSES $325,000 $300,000 $275,000 $250,000 $225,000 $200,000 $175,000 $150,000 $125,000 $100,000 $75,000 $50,000 $25,000 $0
1st Qtr
• Table of Contents
2nd Qtr
Row 21
Row 22
3rd Qtr
Row 20
Row 23
4th Qtr
Row 19
Row 18
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
What-If Period 1
• Table of Contents
Reset This Worksheet
1st Qtr Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$2,000,000 $945,000 $1,055,000 $120,000 $474,000 $701,000 $210,300 $490,700
$2,000,000 $945,000 $1,055,000 $120,000 $474,000 $701,000 $210,300 $490,700
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
$750.0K
Net Income (Loss)
$1.0M
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.3M
$1.5M
Net Sales Revenue
$1.8M
$2.0M
What-If Period 2
• Table of Contents
Reset This Worksheet
2nd Qtr Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$1,500,000 $865,000 $635,000 $60,000 $369,458 $325,542 $97,663 $227,879
$1,500,000 $865,000 $635,000 $60,000 $369,458 $325,542 $97,663 $227,879
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
Net Income (Loss)
$750.0K
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.0M
Net Sales Revenue
$1.3M
$1.5M
What-If Period 3
• Table of Contents
Reset This Worksheet
3rd Qtr Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$1,300,000 $833,000 $467,000 $103,000 $327,058 $242,942 $72,883 $170,059
$1,300,000 $833,000 $467,000 $103,000 $327,058 $242,942 $72,883 $170,059
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
Net Income (Loss)
$750.0K
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.0M
Net Sales Revenue
$1.3M
$1.5M
What-If Period 4
Reset This Worksheet
• Table of Contents 4th Qtr
Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$2,010,100 $1,071,616 $938,484 $605,700 $477,599 $1,066,585 $319,975 $746,609
$2,010,100 $1,071,616 $938,484 $605,700 $477,599 $1,066,585 $319,975 $746,609
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
$750.0K
Net Income (Loss)
$1.0M
$1.3M
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.5M
Net Sales Revenue
$1.8M
$2.0M
$2.3M
What-If Summary
• Table of Contents 1st Qtr
Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $2,000,000 $945,000 $1,055,000 $120,000 $474,000 $701,000 $210,300 $490,700
What-If Scenario $2,000,000 Actuals $945,000 $1,055,000 What$120,000 If Scen $474,000 ario $701,000 $0 $210,300 $490,700
Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $1,500,000 $865,000 $635,000 $60,000 $369,458 $325,542 $97,663 $227,879
What-If Scenario $1,500,000 Actuals $865,000 $635,000 What$60,000 If Scen $369,458 ario $0 $325,542 $97,663 $227,879
$250.0K $500.0K $750.0K Net Income (Loss)
$1.0M
Gross Profit
$1.3M
$1.5M
$1.8M
$2.0M
$1.8M
$2.0M
Sales Revenue
2nd Qtr
$250.0K $500.0K $750.0K $1.0M Net Income (Loss)
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.3M
$1.5M
Sales Revenue
3rd Qtr Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $1,300,000 $833,000 $467,000 $103,000 $327,058 $242,942 $72,883 $170,059
What-If Scenario $1,300,000 Actuals $833,000 $467,000 What$103,000 If Scen $327,058 ario $0 $242,942 $72,883 $170,059
Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $2,010,100 $1,071,616 $938,484 $605,700 $477,599 $1,066,585 $319,975 $746,609
What-If Scenario $2,010,100 Actuals $1,071,616 $938,484 What$605,700 If Scen $477,599 ario $0 $1,066,585 $319,975 $746,609
$250.0K $500.0K $750.0K $1.0M Net Income (Loss)
Gross Profit
$1.3M
$1.5M
$1.8M
$2.0M
$1.8M
$2.0M
Sales Revenue
4th Qtr
$250.0K $500.0K $750.0K $1.0M Net Income (Loss)
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.3M
$1.5M
Sales Revenue
Sales and Total Cost of Sales
AUTO CHARTING From Financial Statement Sales Direct Labor Cost Direct Materials Cost Other Direct Costs Total Cost of Sales Gross Profit Total Expenses Operating income Income Before Tax Income Taxes Net Income
• Table of Contents 1st Qtr 2,000,000 320,000 500,000 125,000 945,000 1,055,000 474,000 581,000 701,000 210,300 490,700
2nd Qtr 1,500,000 240,000 500,000 125,000 865,000 635,000 369,458 265,542 325,542 97,663 227,879
3rd Qtr 1,300,000 208,000 500,000 125,000 833,000 467,000 327,058 139,942 242,942 72,883 170,059
4th Qtr 2,010,100 321,616 500,000 250,000 1,071,616 938,484 477,599 460,885 1,066,585 319,975 746,609
2,250,000 2,000,000 1,750,000 1,500,000 1,250,000 1,000,000 750,000 500,000 250,000 0 1st Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Sales
3rd Qtr 4th Qtr Total Cost of Sales
Optimal Quarterly Performance Analysis
• Table of Contents
Average Qtr Performance $ Total Sales
Optimal Qtr Performance
%
$
Variance
%
$ 307,575
$1,702,525
100.00%
2,010,100
100.00%
Total Cost of Goods Sold
928,654
54.55%
833,000
41.44%
(95,654)
Gross Profit
773,871
45.45%
1,055,000
52.48%
281,129
Total Operating Expenses
412,029
24.20%
327,058
16.27%
(84,971)
Operating Income
361,842
21.25%
581,000
28.90%
219,158
Net Income (Loss)
408,812
24.01%
746,609
37.14%
337,797
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Break-Even Analysis
• Table of Contents 1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Annual
Sales
2,000,000
1,500,000
1,300,000
2,010,100
6,810,100
Contribution Margin
1,055,000
635,000
467,000
938,484
3,095,484
Contribution Margin Ratio
52.75%
Total Fixed Expenses
474,000
Total Fixed Expenses Ratio
23.70%
Break-Even Sales
898,578
Break-Even %
44.93%
42.33% 369,458
35.92% 327,058
24.63% 872,735
25.16% 910,440
58.18%
70.03%
46.69% 477,599
45.45% 1,648,115
23.76% 1,022,950
24.20% 3,625,872
50.89%
53.24%
Annual Date of Break-Even
7/12
Operating Profit
581,000
265,542
139,942
460,885
1,447,369
Break-Even Analysis $7,000,000 $6,000,000 $5,000,000 $4,000,000 $3,000,000 $2,000,000
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Row 22
Row 21
Row 20
Row 19
Row 18
Row 17
Row 16
Row 15
Row 14
Row 13
Row 12
Row 10
$0
Row 11
$1,000,000
INCOME SUMMARY CHART - ANNUALIZED
• Table of Contents
$7,000,000 $6,000,000 $5,000,000 $4,000,000 $3,000,000 $2,000,000 $1,000,000 $0 Annualized
Row 48
Row 66
Row 62
Row 45
Row 42
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
INCOME SUMMARY CHART - 4 QTRS $2,250,000 $2,000,000 $1,750,000 $1,500,000 $1,250,000 $1,000,000 $750,000 $500,000 $250,000 $0
1st Qtr
2nd Qtr
Row 48
• Table of Contents
3rd Qtr
Row 54
Row 45
4th Qtr
Row 42
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
SALES CHART
• Table of Contents
$2,250,000 $2,000,000 $1,750,000 $1,500,000 $1,250,000 $1,000,000 $750,000 $500,000 $250,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
DIRECT OPERATING EXPENSES CHART $500,000 $450,000 $400,000 $350,000 $300,000 $250,000 $200,000 $150,000 $100,000 $50,000 $0
1st Qtr
2nd Qtr
Other costs
• Table of Contents
3rd Qtr
Direct labor
4th Qtr
Direct Materials
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
TOTAL OPERATING EXPENSES CHART
• Table of Contents
$500,000 $450,000 $400,000 $350,000 $300,000 $250,000 $200,000 $150,000 $100,000 $50,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
GROSS PROFIT CHART $1,100,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
1st Qtr
2nd Qtr
• Table of Contents
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
EARNING BEFORE INCOME TAX CHART $1,100,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
1st Qtr
2nd Qtr
3rd Qtr
• Table of Contents
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
NET INCOME CHART
• Table of Contents
$800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
ASSETS CHART $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0 Column H
• Table of Contents
Column I
Row 120
Column J
Row 107
Row 115
Column K
Row 123
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
LIABILITIES CHART $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0
1st Qtr
• Table of Contents
2nd Qtr
Row 135
3rd Qtr
Row 143
Row 150
4th Qtr
Row 152
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
BALANCE SHEET SUMMARY CHART $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0
Row 120
1999
1st Qtr
Row 135
Row 107
2nd Qtr
Row 150
• Table of Contents
3rd Qtr
Row 143
Row 115
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
Row 123
Row 152
CASH FLOW STATEMENT
• Table of Contents
1st Qtr Cash from operations Net earnings (loss) Add-depreciation and amortization
Forecasted 2nd Qtr 3rd Qtr
Total 4 Quarters
4th Qtr
$490,700 $33,750
$227,879 $35,208
$170,059 $35,208
$746,609 $35,208
$1,635,247 $139,374
$524,450
$263,087
$205,267
$781,817
$1,774,621
($307,534) ($230,411) ($50,000) ($8,000) ($271,233) $0 $153,300 ($6,712) ($4,000) $0
$164,383 $39,452 $14,910 ($87,000) $0 $0 ($112,637) ($20,822) $0 $0
$65,754 $15,781 ($31,230) $115,000 $0 $0 ($24,780) ($8,329) $0 $0
($233,458) ($117,674) $26,320 ($18,000) $0 $0 $247,092 $29,571 $0 ($50,000)
($310,855) ($292,852) ($40,000) $2,000 ($271,233) $0 $262,975 ($6,292) ($4,000) ($50,000)
($724,590)
($1,714)
$132,196
($116,149)
($710,257)
$12,500 ($50,000) $75,000 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$50,000 ($50,000) $75,000 $0
$37,500
$12,500
$12,500
$12,500
$75,000
($50,000) ($100,000) ($10,000) ($3,000) $40,000 $0
$0 $0 $0 $0 ($50,000) $0
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
($50,000) ($100,000) ($10,000) ($3,000) ($10,000) $0
Net cash from financing
($123,000)
($50,000)
$0
$0
($173,000)
Net increase (decrease) in cash
($360,640)
$198,873
$324,963
$653,168
$816,364
Cash at beginning of period
$451,000
$90,360
$289,233
$614,196
$451,000
Cash at the end of period
$90,360
$289,233
$614,196
$1,267,364
$1,267,364
Net cash from operations Cash provided (used) by operating activities Accounts Receivable Inventory Other current assets Other non-current assets Accounts payable Current portion of long-term debt Income taxes Accrued expenses Other current liabilities Dividends paid Net cash from operations Investment transactions Increases (decreases) Land Buildings and improvements Equipment Intangible assets Net cash from investments Financing transactions Increases (decreases) Short term notes payable Long term debt Deferred income Deferred income taxes Other long-term liabilities Capital stock and paid in capital
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
CASH FLOW CHART
• Table of Contents
$1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Cash at the end of period © Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
CASH FLOW PROJECTIONS - 8 YEARS 1st Qtr 1 $490,700 $33,750
Cash from operations Net earnings (loss) Add-depreciation and amortization Net cash from operations Cash provided (used) by operating activities Accounts Receivable Inventory Other current assets Other non-current assets Accounts payable Current portion of long-term debt Income taxes Accrued expenses Other current liabilities Dividends paid Net cash from operations Investment transactions Increases (decreases) Land Buildings and improvements Equipment Intangible assets Net cash from investments Financing transactions Increases (decreases) Short term notes payable Long term debt Deferred income Deferred income taxes Other long-term liabilities Capital stock and paid in capital
• Table of Contents
Current 2nd Qtr 3rd Qtr 2 3 $227,879 $170,059 $35,208 $35,208
4th Qtr 4 $746,609 $35,208
Forecasted-Linear Regression Analysis 5th Qtr 6th Qtr 7th Qtr 8th Qtr 5 6 7 8 $586,289 $657,279 $728,270 $799,261 $35,937 $36,374 $36,812 $37,249
$524,450
$263,087
$205,267
$781,817
$622,226
$693,654
$765,082
$836,510
($307,534) ($230,411) ($50,000) ($8,000) ($271,233) $0 $153,300 ($6,712) ($4,000) $0
$164,383 $39,452 $14,910 ($87,000) $0 $0 ($112,637) ($20,822) $0 $0
$65,754 $15,781 ($31,230) $115,000 $0 $0 ($24,780) ($8,329) $0 $0
($233,458) ($117,674) $26,320 ($18,000) $0 $0 $247,092 $29,571 $0 ($50,000)
($46,814) $5,422 $35,705 $43,500 $135,617 $0 $158,052 $28,763 $2,000 ($50,000)
($34,454) $36,876 $53,987 $60,700 $216,986 $0 $194,975 $40,897 $3,200 ($65,000)
($22,094) $68,330 $72,269 $77,900 $298,356 $0 $231,899 $53,031 $4,400 ($80,000)
($9,734) $99,784 $90,551 $95,100 $379,726 $0 $268,822 $65,165 $5,600 ($95,000)
($724,590)
($1,714)
$132,196
($116,149)
$312,244
$508,167
$704,091
$900,014
$12,500 ($50,000) $75,000 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$12,500 $25,000 ($37,500) $0
$12,500 $40,000 ($60,000) $0
$12,500 $55,000 ($82,500) $0
$12,500 $70,000 ($105,000) $0
$37,500
$12,500
$12,500
$12,500
($7,500)
($15,000)
($22,500)
($50,000) ($100,000) ($10,000) ($3,000) $40,000 $0
$0 $0 $0 $0 ($50,000) $0
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
$40,000 $80,000 $8,000 $2,400 ($27,000) $0
$55,000 $110,000 $11,000 $3,300 ($34,000) $0
$70,000 $140,000 $14,000 $4,200 ($41,000) $0
$0
$25,000 $50,000 $5,000 $1,500 ($20,000) $0
Net cash from financing
($123,000)
($50,000)
$0
$0
$61,500
$103,400
$145,300
$187,200
Net increase (decrease) in cash
($360,640)
$198,873
$324,963
$653,168
$995,970
$1,312,721
$1,629,472
$1,946,224
Cash at beginning of period
$451,000
$90,360
$289,233
$614,196
$533,313
$602,159
$671,005
$739,851
Cash at the end of period
$90,360
$289,233
$614,196
$1,267,364
$1,529,282
$1,914,880
$2,300,477
$2,686,075
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
CASH FLOW PROJECTIONS - 8 YEARS
• Table of Contents
$2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0 1st Qtr
3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr
8th Qtr
Cash at the end of period © Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
FINANCIAL RATIOS Description Altman Z Score Liquidity Ratios Current Ratio Gross Margin Percentage Quick Ratio Activity Ratios Days Sales in AR Inventory Turnover Days Inventory Net Sales to Inventory Days Purchases in AP Working Capital Net Sales to Working Capital Total Assets to Net Sales Net Sales to AR Net Sales to Net Fixed Assets Net Sales to Total Assets Net Sales to Net Worth Amortization and Depreciation Expense to Net Sales Profitability Ratios Gross Profit Percentage Operating Expenses as % of Net Sales Return on Total Assets Return on Net Worth Return on Net Sales Income before tax to Net Worth Income before tax to Total Assets Retained Earning to Net Income Coverage Ratios Times Interest Earned Interest Expense to Net Sales Current Liabilities to Net Worth Current Liabilities to Inventory AP to Net Sales Total Liabilities to Net Worth Net Worth to Total Liabilities
• Table of Contents 1st Qtr 3.74
2nd Qtr 3.58
3rd Qtr 3.57
4th Qtr 4.24
1.90 107% 1.07
2.27 133% 1.33
2.87 189% 1.89
3.08 228% 2.28
29.59 1.50 60.04 3.17 31.31 $680,950 2.94 1.75 3.04 1.00 0.57 0.98 0.001
29.59 1.46 61.49 2.54 34.21 $794,537 1.89 2.37 3.04 0.76 0.42 0.66 0.001
29.59 1.45 62.14 2.26 35.52 $1,102,304 1.18 2.84 3.04 0.66 0.35 0.53 0.001
29.59 1.55 58.19 2.90 27.61 $1,803,621 1.11 2.32 3.04 1.04 0.43 0.64 0.001
52.75% 21.20% 14.00% 24.05% 24.54% 34% 20% 385%
42.33% 21.20% 6.42% 10.05% 15.19% 14% 9% 930%
35.92% 21.20% 4.61% 6.97% 13.08% 10% 7% 1346%
46.69% 21.20% 16.02% 23.81% 37.14% 34% 23% 400%
35.75 0.01 0.37 1.20 0.16 0.72 1.39
16.34 0.01 0.28 1.06 0.22 0.56 1.77
8.61 0.01 0.24 1.03 0.25 0.51 1.95
28.36 0.01 0.28 1.25 0.16 0.49 2.06
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Z-SCORE CHART
• Table of Contents
4.50 4.00 3.50 3.00 2.50 2.00 1.50 1.00 0.50 1st Qtr
2nd Qtr
3rd Qtr Altman Z Score
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
CURRENT RATIO CHART
• Table of Contents
3.25 3.00 2.75 2.50 2.25 2.00 1.75 1.50 1.25 1.00 0.75 0.50 0.25 1st Qtr
2nd Qtr
3rd Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
QUICK RATIO CHART
• Table of Contents
2.50 2.25 2.00 1.75 1.50 1.25 1.00 0.75 0.50 0.25 1st Qtr
2nd Qtr
3rd Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
WORKING CAPITAL CHART
• Table of Contents
$2,000,000 $1,750,000 $1,500,000 $1,250,000 $1,000,000 $750,000 $500,000 $250,000 $0 1st Qtr
2nd Qtr
3rd Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
COMPANY STOCK VALUATION
• Table of Contents
Enter Data Formatted in Blue - Shaded Cells Contain Formula. Given the following assumptions, compute the value per share of the firm.
Return on incremental investment beginning 3rd Quarter Plowback rate beginning 3rd Quarter Weighted average cost of capital Tax rate
20% 30% 12% 30%
1st Qtr Sales Earnings before interest and taxes (EBIT) Taxes on EBIT @ 40% EBIAT
2nd Qtr
3rd Qtr
$1,500,000 $325,542 ($97,663) $227,879
$1,300,000 $242,942 ($72,883) $170,059
4th Qtr $2,010,100 $1,066,585 ($319,975) $746,609
Current assets less marketable securities Current liabilities Adjusted net working capital
$1,338,305 $757,355 $580,950
$1,418,433 $623,896 $794,537
$1,693,091 $590,787 $1,102,304
Gross property, plant and equipment Accumulated depreciation Net property, plant and equipment
$2,437,500 $432,500 $2,005,000
$2,450,000 $466,458 $1,983,542
$2,462,500 $500,416 $1,962,084
Invested capital
$2,585,950
$2,778,079
$3,064,388
$3,288,371
$227,879 ($192,129) $35,750
$170,059 ($286,309) ($116,250)
$35,750 89.29% $31,920 $31,920
($116,250) 79.72% ($92,673) ($60,753)
$746,609 ($223,983) $522,627 $9,233,069 $9,755,696 71.18% $6,943,911 $6,883,158
EBIAT Less change in invested capital Free cash flow Terminal value Total PV factor PV of cash flow and terminal value Cumulative PV Enterprise value Plus marketable securities Less short-term debt Less long-term debt Equity value Divide by number of shares outstanding Value per share
$6,883,158 $100,000 ($328,767) ($500,000) $6,154,391 $100,000 $61.54
© Copyright, 2009, Jaxworks, All Rights Reserved
© Copyright, 2009, JaxWorks, All Rights Reserved.
DASHBOARD Altman Z-Score 4.50 4.00 3.50 3.00 2.50 2.00 1.50 1.00 0.50 1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Expenses
Break-Even Analysis $7,000,000 $6,500,000
$325,000 $300,000 $275,000 $250,000 $225,000 $200,000 $175,000 $150,000 $125,000 $100,000 $75,000 $50,000 $25,000 $0
$6,000,000 $5,500,000 $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000
Annual Income
Row 22
Row 21
Row 20
Row 19
Row 18
Row 18
Row 19
Row 17
Row 23
Row 16
Row 20
Row 11
Row 22
Row 10
$0 Row 21
Row 15
$500,000
4th Qtr
Row 14
3rd Qtr
Row 13
2nd Qtr
Row 12
1st Qtr
Quarterly Income
$7,000,000 $6,500,000 $6,000,000 $5,500,000 $5,000,000
$2,250,000 $2,000,000 $1,750,000 $1,500,000
$4,000,000
$1,250,000 $1,000,000
$3,000,000
$750,000
$2,000,000
$500,000
$1,000,000
$250,000
$0
$0
Annualized Row 48
Row 66
Row 62
Row 45
1st Qtr
Row 42
2nd Qtr
Row 48
Sales
3rd Qtr
Row 54
Row 45
4th Qtr
Row 42
Direct Expenses
$2,250,000
$500,000
$2,000,000
$450,000
$1,750,000
$400,000
$1,500,000
$350,000
$1,250,000
$300,000 $250,000
$1,000,000
$200,000
$750,000
$150,000
$500,000
$100,000
$250,000
$50,000
$0
$0
1st Qtr 2nd Qtr 3rd Qtr
1st Qtr
4th Qtr
2nd Qtr
Total Expenses
3rd Qtr
4th Qtr
Gross Profit
$500,000
$1,100,000
$450,000
$1,000,000
$400,000
$900,000
$350,000
$800,000 $700,000
$300,000
$600,000
$250,000
$500,000 $400,000 $300,000
$200,000 $150,000 $100,000
$200,000 $100,000 $0
$50,000 $0
1st Qtr 2nd Qtr 3rd Qtr
1st Qtr 2nd Qtr 3rd Qtr
4th Qtr
Earnings Before Income Taxes
4th Qtr
Net Income
$1,100,000
$800,000 $700,000
$750,000 $700,000 $650,000 $600,000 $550,000 $500,000
$600,000
$400,000
$1,000,000 $900,000
$500,000 $400,000 $300,000 $200,000
$300,000 $200,000 $100,000
$100,000 $0
$0
1st Qtr 2nd Qtr 3rd Qtr
1st Qtr 2nd Qtr 3rd Qtr
4th Qtr
Assets
4th Qtr
Liabilities
$5,000,000
$5,000,000
$4,500,000
$4,500,000
$4,000,000
$4,000,000
$3,500,000
$3,500,000
$3,000,000
$3,000,000
$2,500,000
$2,500,000
$2,000,000 $1,500,000
$2,000,000 $1,500,000
$1,000,000 $500,000 $0
Column H
$1,000,000 $500,000 $0
Column I
Row 120
Column J
Row 107
1st Qtr
Row 115
Row 123
Row 135
Balance Sheet Summary $1,300,000 $1,200,000 $1,100,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
$4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000
1999
Row 120
1st Qtr
Row 135
2nd Qtr
Row 107
Row 150
3rd Qtr
3rd Qtr
Row 143
Row 143
Row 150
4th Qtr
Row 152
Cash Flow - 4 Quarters
$5,000,000
$1,000,000 $500,000 $0
2nd Qtr
Column K
4th Qtr
Row 115
Row 123
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Row 152
Cash Flow Projections - 8 Periods
Working Capital $2,000,000
$2,750,000
$1,800,000
$2,500,000 $2,250,000
$1,600,000
$2,000,000
$1,400,000
$1,750,000
$1,200,000
$1,500,000 $1,250,000
$1,000,000
$1,000,000
$800,000
$750,000 $500,000
$600,000
$250,000 $0
$400,000
1st Qtr 2nd Qtr 3rd Qtr
4th Qtr
5th Qtr
6th Qtr
7th Qtr
$200,000 8th Qtr
$0 1st Qtr
2nd Qtr
Current Ratio
3rd Qtr
4th Qtr
Quick Ratio
3.25
2.50
3.00
2.25
2.75 2.50
2.00
2.25
1.75
2.00
1.50
1.75
1.25
1.50 1.25
1.00
1.00
0.75
0.75
0.50
0.50
0.25
0.25 -
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
1st Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
2nd Qtr
3rd Qtr
4th Qtr
Since 1996, JaxWorks has offered a suite of Free Excel workbooks and spreadsheets, and asso HTML documents, that cover a number of financial, accounting and sales functions. These are i Also included Free are: - business plan tools, including spreadsheets and excellent instructions - Excel functions glossary and guide; - free training courses for most Microsoft Office applications. These guides are in PDF format and rival commercial books! - comprehensive list of acronyms, ratios and formulas in customer financial analysis, and financial terms; - suite of online calculators, including, breakeven analysis, productivity analysis, business evaluation; - Altman Z-Score (covering publicly and privately held firms, and small businesses); - and payroll analysis.
If you are involved in financial analysis at any level, or want to learn more about MS Excel and o suite this site is invaluable.
JaxWorks Small Business Spreadsheet Factory
spreadsheets, and associated MS Word, PDF and s functions. These are invaluable small business tools.
ns
uides are in
ancial
y analysis,
l
e about MS Excel and other applications in the Office
sheet Factory
• Table of Contents
There is no support for Free downloads.
© Copyright, 2009, JaxWorks, All Rights Reserved.
• Table of Contents
Copyright Notice © 2009, JaxWorks All rights reserved. Specifications are subject to change without notice. The CEO Financial Assistant™ is a registered trademark of Jaxworks. All other brands or products are trademarks or registered trademarks of their respective holders and should be treated as such. NOTICES REGARDING SOFTWARE, DOCUMENTS AND SERVICES. IN NO EVENT SHALL JAXWORKS BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF SOFTWARE, DOCUMENTS, PROVISION OF OR FAILURE TO PROVIDE SERVICES, OR INFORMATION AVAILABLE FROM THIS PROGRAM.
© Copyright, 2009, JaxWorks, All Rights Reserved.
License Agreement • Table of Contents
Products by JaxWorks Copyright (C) 2009 JaxWorks All Rights Reserved WARNING ================= This program is protected by copyright law and international treaties. Unauthorized reproduction or distribution of this program, or any portion of it may result in severe civil and criminal penalties, and will be prosecuted to the maximum extent possible under law. LICENSE AGREEMENT ================= You should read the following terms and conditions carefully before using this software. Your use of this software indicates your full acceptance of this license agreement and warranty. LICENSE AGREEMENT ====================================== 1. GRANTING OF LICENSE. JaxWorks, as Licenser, grants to you, the Licensee, a non-exclusive license to use this software program (hereinafter referred to as the "SOFTWARE") in accordance with the terms contained in this license. You may use the SOFTWARE on a single computer. You may access the SOFTWARE through a network, provided that you have obtained individual licenses for the software to cover all workstations that will access the software through the network. 2. COPYRIGHT. All title and copyrights in and to the SOFTWARE, including but not limited to any images, texts, and sounds incorporated into the SOFTWARE, are owned by JaxWorks. 3. REDISTRIBUTION. You CAN NOT freely distribute the SOFTWARE. 4. DISCLAIMER OF WARRANTY. THE SOFTWARE AND THE ACCOMPANYING FILES ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, JAXWORKS DISCLAIMS ALL WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, ANY IMPLIED WARRANTIES OF PERFORMANCE, MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NONINFRINGEMENT. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL JAXWORKS BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL OR INCIDENTAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION OR LOSS OF BUSINESS INFORMATION) ARISING OUT OF THE USE OF OR INABILITY TO USE THE SOFTWARE, EVEN IF JAXWORKS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
JaxWorks http://www.jaxworks.com
© Copyright, 2009, JaxWorks, All Rights Reserved.
For Best Viewing, please set your Screen Resolution to the highest resolution.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Please Note: There is No Support For Free Downloads
© Copyright, 2009, JaxWorks, All Rights Reserved.
Please Note: There is No Support For Free Downloads
© Copyright, 2009, JaxWorks, All Rights Reserved.
TABLE OF CONTENTS Introduction Auto Charts Net Income Chart Current Ratio Chart Instructions Optimal Performance Assets Chart Quick Ratio Chart Master Data Entry Break-Even Liabilities Chart Working Capital Chart Turnover Data Chart Annual Income Chart Balance Sheet Chart Stock Evaluation Expenses Data ChartQuarterly Income ChartCash Flow Statement Dashboard What-If Period 1 Support Sales Chart Cash Flow Chart What-If Period 2 Direct Expenses ChartCash Flow Projections Copyright What-If Period 3 Total Expenses Chart Cash Flow Projections Chart License Agreement What-If Period 4 Gross Profit Chart Ratios What-If Overview EBIT Chart Z-Score Chart © Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
INSTRUCTIONS You will be posting numbers from your financial statements to The Master Data Entry worksheet. This worksheet drives all other analysis sheets and charts. The s haded c ells contain formulas and are automatically calculated by Exc el. DO N OT enter any information into them. Enter numbers where they are BLUE. You may want to group all of the charts together at the end of the workbook by moving them to the order you prefer. This will not effect the Table of Contents worksheet, however, changing tab names will corrupt the hyperlinks. Each worksheet has been preset for printing at "Fit To 1:1", however, you may have to adjust some settings to your printer. The experienced spreadsheet operator can alter this product to produce a 4 year analysis rather than by quarter.
For Best Viewing Please Set Your Screen Resolution to 1024 X 768 Pixels.
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Clear Worksheet Add Sample Data
MASTER DATA ENTRY SHEET Year of Projection Corporation Type (C or S)? Operating Data Days sales in accounts receivable Days materials cost in inventory Days finished goods in inventory Days materials cost in payables Days payroll expense accrued Days operating expense accrued Expense Data Direct labor as % of sales Other payroll as % of sales Payroll taxes as % of payroll Insurance as % of payroll Legal/accounting as % of sales Office overhead as % of sales Financing Data (0 on) Long term debt Short-term debt Capital stock issued Additional paid-in capital Accumulated depreciation (as of 1999)
• Table of Contents
2000 C
'C' Corporation format selected; income taxes WILL be computed 1st Qtr 2nd Qtr 3rd Qtr 30 30 30 30 30 30 45 45 45 60 60 60 7 7 7 20 20 20
16.00% of sales 12.00% of sales 10.00% of payroll 5.00% of payroll 2.00% of sales 3.00% of sales
$320,000 $240,000 $56,000 $28,000 $40,000 $60,000
Depreciation
Capital
$100,000 $50,000 $400,000
© Copyright, 2009, JaxWorks, All Rights Reserved.
$240,000 $180,000 $42,000 $21,000 $30,000 $45,000
$208,000 $156,000 $36,400 $18,200 $26,000 $39,000
Current Portion $100,000 $50,000
LT Portion $500,000
4th Qtr 30 30 45 60 7 20
$321,616 $241,212 $56,283 $28,141 $40,202 $60,303 Rate 10.00% 10.00%
INCOME STATEMENT
4th Qtr
Total Annualized
$2,000,000 $945,000
$1,500,000 $865,000
$1,300,000 $833,000
$2,010,100 $1,071,616
$6,810,100 $3,714,616
Gross profit
$1,055,000
$635,000
$467,000
$938,484
$3,095,484
Total expenses
$424,000 $16,250 $32,500 $1,250 $474,000
Forecasted 2nd Qtr
$318,000 $16,250 $33,958 $1,250 $369,458
$275,600 $16,250 $33,958 $1,250 $327,058
$426,141 $16,250 $33,958 $1,250 $477,599
$1,443,741 $65,000 $134,374 $5,000 $1,648,115
Operating income
$581,000
$265,542
$139,942
$460,885
$1,447,369
Subtotal
$100,000 $20,000 $120,000
$10,000 $50,000 $60,000
$3,000 $100,000 $103,000
$405,700 $200,000 $605,700
$518,700 $370,000 $888,700
$2,336,069
1st Qtr Sales Sales Cost of sales
Expenses Operating expenses Interest Depreciation Amortization
Other income and expenses Gain (loss) on sale of assets Other (net)
Income before tax Please enter a tax percentage Taxes @
30% Net income
Retained earnings-beginning
3rd Qtr
$701,000
$325,542
$242,942
$1,066,585
$210,300
$97,663
$72,883
$319,975
$700,821
$490,700
$227,879
$170,059
$746,609
$1,635,248
$1,400,000
$1,890,700
$2,118,579
$2,288,638
$1,400,000
Dividends paid
$50,000
$50,000
$1,890,700
$0
$2,118,579
$2,288,638
$2,985,247
$2,985,248
$320,000 $500,000 $125,000
$240,000 $500,000 $125,000
$208,000 $500,000 $125,000
$321,616 $500,000 $250,000
$1,089,616 $2,000,000 $625,000
Depreciation: Enter the numbers of years. 30 year Buildings 10 year Equipment
$12,500 $20,000
$12,083 $21,875
$12,083 $21,875
$12,083 $21,875
$48,749 $85,625
Interest: Percentages from Data sheet 10.00% 10.00%
$1,250 $15,000
$1,250 $15,000
$1,250 $15,000
$1,250 $15,000
$5,000 $60,000
Retained earnings-ending
$0
$0
Detailed Supporting Information Cost of sales Direct labor Direct Materials Other costs
Long-Term Short-Term
BALANCE SHEET
Actual 1999 ASSETS Current Assets Cash and cash equivalents Accounts receivable Inventory Other current assets Total Current Assets Fixed Assets Land Buildings Equipment Subtotal Less-accumulated depreciation Total Fixed Assets Intangible Assets Cost Less-accumulated amortization Total Intangible Assets Other assets Total Assets
1st Qtr
Total Current Liabilities Non-Current Liabilities Long-term debt Deferred income Deferred income taxes Other long-term liabilities Total Liabilities Stockholders' Equity Capital stock issued Additional paid in capital Retained earnings Other Stockholders Equity Total Liabilities and Equity "C" Corporation (Y/N) Cash balance positive or (negative) Amount sheet is out-of-balance Amount cash flow out-of-balance
4th Qtr
$451,000 $350,000 $400,000 $10,000 $1,211,000
$90,360 $657,534 $630,411 $60,000 $1,438,305
$289,233 $493,151 $590,959 $45,090 $1,418,433
$614,196 $427,397 $575,178 $76,320 $1,693,091
$1,267,364 $660,855 $692,852 $50,000 $2,671,071
$100,000 $1,500,000 $800,000 $2,400,000 $400,000 $2,000,000
$112,500 $1,450,000 $875,000 $2,437,500 $432,500 $2,005,000
$125,000 $1,450,000 $875,000 $2,450,000 $466,458 $1,983,542
$137,500 $1,450,000 $875,000 $2,462,500 $500,416 $1,962,084
$150,000 $1,450,000 $875,000 $2,475,000 $534,374 $1,940,626
$50,000 $20,000 $30,000
$50,000 $21,250 $28,750
$25,000 $3,266,000
$33,000 $3,505,055
Actual 1999
LIABILITIES AND STOCKHOLDERS' EQUITY Current Liabilities Accounts payable Notes payable Current portion of long-term debt Income taxes Accrued expenses Other current liabilities
Forecast 2nd Qtr 3rd Qtr
1st Qtr
$50,000 $22,500 $27,500
$50,000 $23,750 $26,250
$50,000 $25,000 $25,000
$120,000 $3,549,475
$5,000 $3,686,425
$23,000 $4,659,697
Forecast 2nd Qtr 3rd Qtr
4th Qtr
$600,000 $100,000 $100,000 $30,000 $90,000 $16,000 $936,000
$328,767 $50,000 $100,000 $183,300 $83,288 $12,000 $757,355
$328,767 $50,000 $100,000 $70,663 $62,466 $12,000 $623,896
$328,767 $50,000 $100,000 $45,883 $54,137 $12,000 $590,787
$600,000 $100,000 $30,000 $50,000
$500,000 $90,000 $27,000 $90,000
$500,000 $90,000 $27,000 $40,000
$500,000 $90,000 $27,000 $40,000
$500,000 $90,000 $27,000 $40,000
$1,716,000
$1,464,355
$1,280,896
$1,247,787
$1,524,450
$100,000 $50,000 $1,400,000 $0 $1,550,000
$100,000 $50,000 $1,890,700 $0 $2,040,700
$100,000 $50,000 $2,118,579 $0 $2,268,579
$100,000 $50,000 $2,288,638 $0 $2,438,638
$100,000 $50,000 $2,985,247 $0 $3,135,247
$3,266,000
$3,505,055
$3,549,475
$3,686,425
$4,659,697
Y Positive $0
Positive $0 $0
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Positive $0 $0
Positive $0 $0
$328,767 $50,000 $100,000 $292,975 $83,708 $12,000 $867,450
Positive $0 $0
DATA CHART - TURNOVER
• Table of Contents
Number of Days
60 55 50 45 40 35 30 25 20 15 10 5 0
1st Qtr
Days payroll expense accrued Days finished goods in inventory
2nd Qtr
Days operating expense accrued Days materials cost in payables
3rd Qtr
4th Qtr
Days sales in accounts receivable
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Days materials cost in inventory
DATA CHART - EXPENSES $325,000 $300,000 $275,000 $250,000 $225,000 $200,000 $175,000 $150,000 $125,000 $100,000 $75,000 $50,000 $25,000 $0
1st Qtr
• Table of Contents
2nd Qtr
Row 21
Row 22
3rd Qtr
Row 20
Row 23
4th Qtr
Row 19
Row 18
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
What-If Period 1
• Table of Contents
Reset This Worksheet
1st Qtr Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$2,000,000 $945,000 $1,055,000 $120,000 $474,000 $701,000 $210,300 $490,700
$2,000,000 $945,000 $1,055,000 $120,000 $474,000 $701,000 $210,300 $490,700
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
$750.0K
Net Income (Loss)
$1.0M
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.3M
$1.5M
Net Sales Revenue
$1.8M
$2.0M
What-If Period 2
• Table of Contents
Reset This Worksheet
2nd Qtr Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$1,500,000 $865,000 $635,000 $60,000 $369,458 $325,542 $97,663 $227,879
$1,500,000 $865,000 $635,000 $60,000 $369,458 $325,542 $97,663 $227,879
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
Net Income (Loss)
$750.0K
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.0M
Net Sales Revenue
$1.3M
$1.5M
What-If Period 3
• Table of Contents
Reset This Worksheet
3rd Qtr Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$1,300,000 $833,000 $467,000 $103,000 $327,058 $242,942 $72,883 $170,059
$1,300,000 $833,000 $467,000 $103,000 $327,058 $242,942 $72,883 $170,059
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
Net Income (Loss)
$750.0K
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.0M
Net Sales Revenue
$1.3M
$1.5M
What-If Period 4
Reset This Worksheet
• Table of Contents 4th Qtr
Actuals Net Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
What-If Scenario
$2,010,100 $1,071,616 $938,484 $605,700 $477,599 $1,066,585 $319,975 $746,609
$2,010,100 $1,071,616 $938,484 $605,700 $477,599 $1,066,585 $319,975 $746,609
Actuals WhatIf Scen ario $0
$250.0K
$500.0K
$750.0K
Net Income (Loss)
$1.0M
$1.3M
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.5M
Net Sales Revenue
$1.8M
$2.0M
$2.3M
What-If Summary
• Table of Contents 1st Qtr
Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $2,000,000 $945,000 $1,055,000 $120,000 $474,000 $701,000 $210,300 $490,700
What-If Scenario $2,000,000 Actuals $945,000 $1,055,000 What$120,000 If Scen $474,000 ario $701,000 $0 $210,300 $490,700
Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $1,500,000 $865,000 $635,000 $60,000 $369,458 $325,542 $97,663 $227,879
What-If Scenario $1,500,000 Actuals $865,000 $635,000 What$60,000 If Scen $369,458 ario $0 $325,542 $97,663 $227,879
$250.0K $500.0K $750.0K Net Income (Loss)
$1.0M
Gross Profit
$1.3M
$1.5M
$1.8M
$2.0M
$1.8M
$2.0M
Sales Revenue
2nd Qtr
$250.0K $500.0K $750.0K $1.0M Net Income (Loss)
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.3M
$1.5M
Sales Revenue
3rd Qtr Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $1,300,000 $833,000 $467,000 $103,000 $327,058 $242,942 $72,883 $170,059
What-If Scenario $1,300,000 Actuals $833,000 $467,000 What$103,000 If Scen $327,058 ario $0 $242,942 $72,883 $170,059
Sales Revenue LESS Cost of Sales Gross Profit PLUS Other Income LESS All Expenses Income Before Tax LESS Income Taxes Net Income (Loss)
Actuals $2,010,100 $1,071,616 $938,484 $605,700 $477,599 $1,066,585 $319,975 $746,609
What-If Scenario $2,010,100 Actuals $1,071,616 $938,484 What$605,700 If Scen $477,599 ario $0 $1,066,585 $319,975 $746,609
$250.0K $500.0K $750.0K $1.0M Net Income (Loss)
Gross Profit
$1.3M
$1.5M
$1.8M
$2.0M
$1.8M
$2.0M
Sales Revenue
4th Qtr
$250.0K $500.0K $750.0K $1.0M Net Income (Loss)
Gross Profit
© Copyright, 2009, JaxWorks, All Rights Reserved.
$1.3M
$1.5M
Sales Revenue
Sales and Total Cost of Sales
AUTO CHARTING From Financial Statement Sales Direct Labor Cost Direct Materials Cost Other Direct Costs Total Cost of Sales Gross Profit Total Expenses Operating income Income Before Tax Income Taxes Net Income
• Table of Contents 1st Qtr 2,000,000 320,000 500,000 125,000 945,000 1,055,000 474,000 581,000 701,000 210,300 490,700
2nd Qtr 1,500,000 240,000 500,000 125,000 865,000 635,000 369,458 265,542 325,542 97,663 227,879
3rd Qtr 1,300,000 208,000 500,000 125,000 833,000 467,000 327,058 139,942 242,942 72,883 170,059
4th Qtr 2,010,100 321,616 500,000 250,000 1,071,616 938,484 477,599 460,885 1,066,585 319,975 746,609
2,250,000 2,000,000 1,750,000 1,500,000 1,250,000 1,000,000 750,000 500,000 250,000 0 1st Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Sales
3rd Qtr 4th Qtr Total Cost of Sales
Optimal Quarterly Performance Analysis
• Table of Contents
Average Qtr Performance $ Total Sales
Optimal Qtr Performance
%
$
Variance
%
$ 307,575
$1,702,525
100.00%
2,010,100
100.00%
Total Cost of Goods Sold
928,654
54.55%
833,000
41.44%
(95,654)
Gross Profit
773,871
45.45%
1,055,000
52.48%
281,129
Total Operating Expenses
412,029
24.20%
327,058
16.27%
(84,971)
Operating Income
361,842
21.25%
581,000
28.90%
219,158
Net Income (Loss)
408,812
24.01%
746,609
37.14%
337,797
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Break-Even Analysis
• Table of Contents 1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Annual
Sales
2,000,000
1,500,000
1,300,000
2,010,100
6,810,100
Contribution Margin
1,055,000
635,000
467,000
938,484
3,095,484
Contribution Margin Ratio
52.75%
Total Fixed Expenses
474,000
Total Fixed Expenses Ratio
23.70%
Break-Even Sales
898,578
Break-Even %
44.93%
42.33% 369,458
35.92% 327,058
24.63% 872,735
25.16% 910,440
58.18%
70.03%
46.69% 477,599
45.45% 1,648,115
23.76% 1,022,950
24.20% 3,625,872
50.89%
53.24%
Annual Date of Break-Even
7/12
Operating Profit
581,000
265,542
139,942
460,885
1,447,369
Break-Even Analysis $7,000,000 $6,000,000 $5,000,000 $4,000,000 $3,000,000 $2,000,000
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Row 22
Row 21
Row 20
Row 19
Row 18
Row 17
Row 16
Row 15
Row 14
Row 13
Row 12
Row 10
$0
Row 11
$1,000,000
INCOME SUMMARY CHART - ANNUALIZED
• Table of Contents
$7,000,000 $6,000,000 $5,000,000 $4,000,000 $3,000,000 $2,000,000 $1,000,000 $0 Annualized
Row 48
Row 66
Row 62
Row 45
Row 42
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
INCOME SUMMARY CHART - 4 QTRS $2,250,000 $2,000,000 $1,750,000 $1,500,000 $1,250,000 $1,000,000 $750,000 $500,000 $250,000 $0
1st Qtr
2nd Qtr
Row 48
• Table of Contents
3rd Qtr
Row 54
Row 45
4th Qtr
Row 42
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
SALES CHART
• Table of Contents
$2,250,000 $2,000,000 $1,750,000 $1,500,000 $1,250,000 $1,000,000 $750,000 $500,000 $250,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
DIRECT OPERATING EXPENSES CHART $500,000 $450,000 $400,000 $350,000 $300,000 $250,000 $200,000 $150,000 $100,000 $50,000 $0
1st Qtr
2nd Qtr
Other costs
• Table of Contents
3rd Qtr
Direct labor
4th Qtr
Direct Materials
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
TOTAL OPERATING EXPENSES CHART
• Table of Contents
$500,000 $450,000 $400,000 $350,000 $300,000 $250,000 $200,000 $150,000 $100,000 $50,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
GROSS PROFIT CHART $1,100,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
1st Qtr
2nd Qtr
• Table of Contents
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
EARNING BEFORE INCOME TAX CHART $1,100,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
1st Qtr
2nd Qtr
3rd Qtr
• Table of Contents
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
NET INCOME CHART
• Table of Contents
$800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
ASSETS CHART $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0 Column H
• Table of Contents
Column I
Row 120
Column J
Row 107
Row 115
Column K
Row 123
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
LIABILITIES CHART $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0
1st Qtr
• Table of Contents
2nd Qtr
Row 135
3rd Qtr
Row 143
Row 150
4th Qtr
Row 152
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
BALANCE SHEET SUMMARY CHART $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0
Row 120
1999
1st Qtr
Row 135
Row 107
2nd Qtr
Row 150
• Table of Contents
3rd Qtr
Row 143
Row 115
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
Row 123
Row 152
CASH FLOW STATEMENT
• Table of Contents
1st Qtr Cash from operations Net earnings (loss) Add-depreciation and amortization
Forecasted 2nd Qtr 3rd Qtr
Total 4 Quarters
4th Qtr
$490,700 $33,750
$227,879 $35,208
$170,059 $35,208
$746,609 $35,208
$1,635,247 $139,374
$524,450
$263,087
$205,267
$781,817
$1,774,621
($307,534) ($230,411) ($50,000) ($8,000) ($271,233) $0 $153,300 ($6,712) ($4,000) $0
$164,383 $39,452 $14,910 ($87,000) $0 $0 ($112,637) ($20,822) $0 $0
$65,754 $15,781 ($31,230) $115,000 $0 $0 ($24,780) ($8,329) $0 $0
($233,458) ($117,674) $26,320 ($18,000) $0 $0 $247,092 $29,571 $0 ($50,000)
($310,855) ($292,852) ($40,000) $2,000 ($271,233) $0 $262,975 ($6,292) ($4,000) ($50,000)
($724,590)
($1,714)
$132,196
($116,149)
($710,257)
$12,500 ($50,000) $75,000 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$50,000 ($50,000) $75,000 $0
$37,500
$12,500
$12,500
$12,500
$75,000
($50,000) ($100,000) ($10,000) ($3,000) $40,000 $0
$0 $0 $0 $0 ($50,000) $0
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
($50,000) ($100,000) ($10,000) ($3,000) ($10,000) $0
Net cash from financing
($123,000)
($50,000)
$0
$0
($173,000)
Net increase (decrease) in cash
($360,640)
$198,873
$324,963
$653,168
$816,364
Cash at beginning of period
$451,000
$90,360
$289,233
$614,196
$451,000
Cash at the end of period
$90,360
$289,233
$614,196
$1,267,364
$1,267,364
Net cash from operations Cash provided (used) by operating activities Accounts Receivable Inventory Other current assets Other non-current assets Accounts payable Current portion of long-term debt Income taxes Accrued expenses Other current liabilities Dividends paid Net cash from operations Investment transactions Increases (decreases) Land Buildings and improvements Equipment Intangible assets Net cash from investments Financing transactions Increases (decreases) Short term notes payable Long term debt Deferred income Deferred income taxes Other long-term liabilities Capital stock and paid in capital
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
CASH FLOW CHART
• Table of Contents
$1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 $0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Cash at the end of period © Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
CASH FLOW PROJECTIONS - 8 YEARS 1st Qtr 1 $490,700 $33,750
Cash from operations Net earnings (loss) Add-depreciation and amortization Net cash from operations Cash provided (used) by operating activities Accounts Receivable Inventory Other current assets Other non-current assets Accounts payable Current portion of long-term debt Income taxes Accrued expenses Other current liabilities Dividends paid Net cash from operations Investment transactions Increases (decreases) Land Buildings and improvements Equipment Intangible assets Net cash from investments Financing transactions Increases (decreases) Short term notes payable Long term debt Deferred income Deferred income taxes Other long-term liabilities Capital stock and paid in capital
• Table of Contents
Current 2nd Qtr 3rd Qtr 2 3 $227,879 $170,059 $35,208 $35,208
4th Qtr 4 $746,609 $35,208
Forecasted-Linear Regression Analysis 5th Qtr 6th Qtr 7th Qtr 8th Qtr 5 6 7 8 $586,289 $657,279 $728,270 $799,261 $35,937 $36,374 $36,812 $37,249
$524,450
$263,087
$205,267
$781,817
$622,226
$693,654
$765,082
$836,510
($307,534) ($230,411) ($50,000) ($8,000) ($271,233) $0 $153,300 ($6,712) ($4,000) $0
$164,383 $39,452 $14,910 ($87,000) $0 $0 ($112,637) ($20,822) $0 $0
$65,754 $15,781 ($31,230) $115,000 $0 $0 ($24,780) ($8,329) $0 $0
($233,458) ($117,674) $26,320 ($18,000) $0 $0 $247,092 $29,571 $0 ($50,000)
($46,814) $5,422 $35,705 $43,500 $135,617 $0 $158,052 $28,763 $2,000 ($50,000)
($34,454) $36,876 $53,987 $60,700 $216,986 $0 $194,975 $40,897 $3,200 ($65,000)
($22,094) $68,330 $72,269 $77,900 $298,356 $0 $231,899 $53,031 $4,400 ($80,000)
($9,734) $99,784 $90,551 $95,100 $379,726 $0 $268,822 $65,165 $5,600 ($95,000)
($724,590)
($1,714)
$132,196
($116,149)
$312,244
$508,167
$704,091
$900,014
$12,500 ($50,000) $75,000 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$12,500 $0 $0 $0
$12,500 $25,000 ($37,500) $0
$12,500 $40,000 ($60,000) $0
$12,500 $55,000 ($82,500) $0
$12,500 $70,000 ($105,000) $0
$37,500
$12,500
$12,500
$12,500
($7,500)
($15,000)
($22,500)
($50,000) ($100,000) ($10,000) ($3,000) $40,000 $0
$0 $0 $0 $0 ($50,000) $0
$0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0
$40,000 $80,000 $8,000 $2,400 ($27,000) $0
$55,000 $110,000 $11,000 $3,300 ($34,000) $0
$70,000 $140,000 $14,000 $4,200 ($41,000) $0
$0
$25,000 $50,000 $5,000 $1,500 ($20,000) $0
Net cash from financing
($123,000)
($50,000)
$0
$0
$61,500
$103,400
$145,300
$187,200
Net increase (decrease) in cash
($360,640)
$198,873
$324,963
$653,168
$995,970
$1,312,721
$1,629,472
$1,946,224
Cash at beginning of period
$451,000
$90,360
$289,233
$614,196
$533,313
$602,159
$671,005
$739,851
Cash at the end of period
$90,360
$289,233
$614,196
$1,267,364
$1,529,282
$1,914,880
$2,300,477
$2,686,075
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
CASH FLOW PROJECTIONS - 8 YEARS
• Table of Contents
$2,500,000 $2,000,000 $1,500,000 $1,000,000 $500,000 $0 1st Qtr
3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr
8th Qtr
Cash at the end of period © Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
FINANCIAL RATIOS Description Altman Z Score Liquidity Ratios Current Ratio Gross Margin Percentage Quick Ratio Activity Ratios Days Sales in AR Inventory Turnover Days Inventory Net Sales to Inventory Days Purchases in AP Working Capital Net Sales to Working Capital Total Assets to Net Sales Net Sales to AR Net Sales to Net Fixed Assets Net Sales to Total Assets Net Sales to Net Worth Amortization and Depreciation Expense to Net Sales Profitability Ratios Gross Profit Percentage Operating Expenses as % of Net Sales Return on Total Assets Return on Net Worth Return on Net Sales Income before tax to Net Worth Income before tax to Total Assets Retained Earning to Net Income Coverage Ratios Times Interest Earned Interest Expense to Net Sales Current Liabilities to Net Worth Current Liabilities to Inventory AP to Net Sales Total Liabilities to Net Worth Net Worth to Total Liabilities
• Table of Contents 1st Qtr 3.74
2nd Qtr 3.58
3rd Qtr 3.57
4th Qtr 4.24
1.90 107% 1.07
2.27 133% 1.33
2.87 189% 1.89
3.08 228% 2.28
29.59 1.50 60.04 3.17 31.31 $680,950 2.94 1.75 3.04 1.00 0.57 0.98 0.001
29.59 1.46 61.49 2.54 34.21 $794,537 1.89 2.37 3.04 0.76 0.42 0.66 0.001
29.59 1.45 62.14 2.26 35.52 $1,102,304 1.18 2.84 3.04 0.66 0.35 0.53 0.001
29.59 1.55 58.19 2.90 27.61 $1,803,621 1.11 2.32 3.04 1.04 0.43 0.64 0.001
52.75% 21.20% 14.00% 24.05% 24.54% 34% 20% 385%
42.33% 21.20% 6.42% 10.05% 15.19% 14% 9% 930%
35.92% 21.20% 4.61% 6.97% 13.08% 10% 7% 1346%
46.69% 21.20% 16.02% 23.81% 37.14% 34% 23% 400%
35.75 0.01 0.37 1.20 0.16 0.72 1.39
16.34 0.01 0.28 1.06 0.22 0.56 1.77
8.61 0.01 0.24 1.03 0.25 0.51 1.95
28.36 0.01 0.28 1.25 0.16 0.49 2.06
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
Z-SCORE CHART
• Table of Contents
4.50 4.00 3.50 3.00 2.50 2.00 1.50 1.00 0.50 1st Qtr
2nd Qtr
3rd Qtr Altman Z Score
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
CURRENT RATIO CHART
• Table of Contents
3.25 3.00 2.75 2.50 2.25 2.00 1.75 1.50 1.25 1.00 0.75 0.50 0.25 1st Qtr
2nd Qtr
3rd Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
QUICK RATIO CHART
• Table of Contents
2.50 2.25 2.00 1.75 1.50 1.25 1.00 0.75 0.50 0.25 1st Qtr
2nd Qtr
3rd Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
WORKING CAPITAL CHART
• Table of Contents
$2,000,000 $1,750,000 $1,500,000 $1,250,000 $1,000,000 $750,000 $500,000 $250,000 $0 1st Qtr
2nd Qtr
3rd Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
4th Qtr
COMPANY STOCK VALUATION
• Table of Contents
Enter Data Formatted in Blue - Shaded Cells Contain Formula. Given the following assumptions, compute the value per share of the firm.
Return on incremental investment beginning 3rd Quarter Plowback rate beginning 3rd Quarter Weighted average cost of capital Tax rate
20% 30% 12% 30%
1st Qtr Sales Earnings before interest and taxes (EBIT) Taxes on EBIT @ 40% EBIAT
2nd Qtr
3rd Qtr
$1,500,000 $325,542 ($97,663) $227,879
$1,300,000 $242,942 ($72,883) $170,059
4th Qtr $2,010,100 $1,066,585 ($319,975) $746,609
Current assets less marketable securities Current liabilities Adjusted net working capital
$1,338,305 $757,355 $580,950
$1,418,433 $623,896 $794,537
$1,693,091 $590,787 $1,102,304
Gross property, plant and equipment Accumulated depreciation Net property, plant and equipment
$2,437,500 $432,500 $2,005,000
$2,450,000 $466,458 $1,983,542
$2,462,500 $500,416 $1,962,084
Invested capital
$2,585,950
$2,778,079
$3,064,388
$3,288,371
$227,879 ($192,129) $35,750
$170,059 ($286,309) ($116,250)
$35,750 89.29% $31,920 $31,920
($116,250) 79.72% ($92,673) ($60,753)
$746,609 ($223,983) $522,627 $9,233,069 $9,755,696 71.18% $6,943,911 $6,883,158
EBIAT Less change in invested capital Free cash flow Terminal value Total PV factor PV of cash flow and terminal value Cumulative PV Enterprise value Plus marketable securities Less short-term debt Less long-term debt Equity value Divide by number of shares outstanding Value per share
$6,883,158 $100,000 ($328,767) ($500,000) $6,154,391 $100,000 $61.54
© Copyright, 2009, Jaxworks, All Rights Reserved
© Copyright, 2009, JaxWorks, All Rights Reserved.
DASHBOARD Altman Z-Score 4.50 4.00 3.50 3.00 2.50 2.00 1.50 1.00 0.50 1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Expenses
Break-Even Analysis $7,000,000 $6,500,000
$325,000 $300,000 $275,000 $250,000 $225,000 $200,000 $175,000 $150,000 $125,000 $100,000 $75,000 $50,000 $25,000 $0
$6,000,000 $5,500,000 $5,000,000 $4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000 $1,000,000
Annual Income
Row 22
Row 21
Row 20
Row 19
Row 18
Row 18
Row 19
Row 17
Row 23
Row 16
Row 20
Row 11
Row 22
Row 10
$0 Row 21
Row 15
$500,000
4th Qtr
Row 14
3rd Qtr
Row 13
2nd Qtr
Row 12
1st Qtr
Quarterly Income
$7,000,000 $6,500,000 $6,000,000 $5,500,000 $5,000,000
$2,250,000 $2,000,000 $1,750,000 $1,500,000
$4,000,000
$1,250,000 $1,000,000
$3,000,000
$750,000
$2,000,000
$500,000
$1,000,000
$250,000
$0
$0
Annualized Row 48
Row 66
Row 62
Row 45
1st Qtr
Row 42
2nd Qtr
Row 48
Sales
3rd Qtr
Row 54
Row 45
4th Qtr
Row 42
Direct Expenses
$2,250,000
$500,000
$2,000,000
$450,000
$1,750,000
$400,000
$1,500,000
$350,000
$1,250,000
$300,000 $250,000
$1,000,000
$200,000
$750,000
$150,000
$500,000
$100,000
$250,000
$50,000
$0
$0
1st Qtr 2nd Qtr 3rd Qtr
1st Qtr
4th Qtr
2nd Qtr
Total Expenses
3rd Qtr
4th Qtr
Gross Profit
$500,000
$1,100,000
$450,000
$1,000,000
$400,000
$900,000
$350,000
$800,000 $700,000
$300,000
$600,000
$250,000
$500,000 $400,000 $300,000
$200,000 $150,000 $100,000
$200,000 $100,000 $0
$50,000 $0
1st Qtr 2nd Qtr 3rd Qtr
1st Qtr 2nd Qtr 3rd Qtr
4th Qtr
Earnings Before Income Taxes
4th Qtr
Net Income
$1,100,000
$800,000 $700,000
$750,000 $700,000 $650,000 $600,000 $550,000 $500,000
$600,000
$400,000
$1,000,000 $900,000
$500,000 $400,000 $300,000 $200,000
$300,000 $200,000 $100,000
$100,000 $0
$0
1st Qtr 2nd Qtr 3rd Qtr
1st Qtr 2nd Qtr 3rd Qtr
4th Qtr
Assets
4th Qtr
Liabilities
$5,000,000
$5,000,000
$4,500,000
$4,500,000
$4,000,000
$4,000,000
$3,500,000
$3,500,000
$3,000,000
$3,000,000
$2,500,000
$2,500,000
$2,000,000 $1,500,000
$2,000,000 $1,500,000
$1,000,000 $500,000 $0
Column H
$1,000,000 $500,000 $0
Column I
Row 120
Column J
Row 107
1st Qtr
Row 115
Row 123
Row 135
Balance Sheet Summary $1,300,000 $1,200,000 $1,100,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0
$4,500,000 $4,000,000 $3,500,000 $3,000,000 $2,500,000 $2,000,000 $1,500,000
1999
Row 120
1st Qtr
Row 135
2nd Qtr
Row 107
Row 150
3rd Qtr
3rd Qtr
Row 143
Row 143
Row 150
4th Qtr
Row 152
Cash Flow - 4 Quarters
$5,000,000
$1,000,000 $500,000 $0
2nd Qtr
Column K
4th Qtr
Row 115
Row 123
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Row 152
Cash Flow Projections - 8 Periods
Working Capital $2,000,000
$2,750,000
$1,800,000
$2,500,000 $2,250,000
$1,600,000
$2,000,000
$1,400,000
$1,750,000
$1,200,000
$1,500,000 $1,250,000
$1,000,000
$1,000,000
$800,000
$750,000 $500,000
$600,000
$250,000 $0
$400,000
1st Qtr 2nd Qtr 3rd Qtr
4th Qtr
5th Qtr
6th Qtr
7th Qtr
$200,000 8th Qtr
$0 1st Qtr
2nd Qtr
Current Ratio
3rd Qtr
4th Qtr
Quick Ratio
3.25
2.50
3.00
2.25
2.75 2.50
2.00
2.25
1.75
2.00
1.50
1.75
1.25
1.50 1.25
1.00
1.00
0.75
0.75
0.50
0.50
0.25
0.25 -
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
1st Qtr
© Copyright, 2009, JaxWorks, All Rights Reserved.
© Copyright, 2009, JaxWorks, All Rights Reserved.
2nd Qtr
3rd Qtr
4th Qtr
Since 1996, JaxWorks has offered a suite of Free Excel workbooks and spreadsheets, and asso HTML documents, that cover a number of financial, accounting and sales functions. These are i Also included Free are: - business plan tools, including spreadsheets and excellent instructions - Excel functions glossary and guide; - free training courses for most Microsoft Office applications. These guides are in PDF format and rival commercial books! - comprehensive list of acronyms, ratios and formulas in customer financial analysis, and financial terms; - suite of online calculators, including, breakeven analysis, productivity analysis, business evaluation; - Altman Z-Score (covering publicly and privately held firms, and small businesses); - and payroll analysis.
If you are involved in financial analysis at any level, or want to learn more about MS Excel and o suite this site is invaluable.
JaxWorks Small Business Spreadsheet Factory
spreadsheets, and associated MS Word, PDF and s functions. These are invaluable small business tools.
ns
uides are in
ancial
y analysis,
l
e about MS Excel and other applications in the Office
sheet Factory
• Table of Contents
There is no support for Free downloads.
© Copyright, 2009, JaxWorks, All Rights Reserved.
• Table of Contents
Copyright Notice © 2009, JaxWorks All rights reserved. Specifications are subject to change without notice. The CEO Financial Assistant™ is a registered trademark of Jaxworks. All other brands or products are trademarks or registered trademarks of their respective holders and should be treated as such. NOTICES REGARDING SOFTWARE, DOCUMENTS AND SERVICES. IN NO EVENT SHALL JAXWORKS BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF SOFTWARE, DOCUMENTS, PROVISION OF OR FAILURE TO PROVIDE SERVICES, OR INFORMATION AVAILABLE FROM THIS PROGRAM.
© Copyright, 2009, JaxWorks, All Rights Reserved.
License Agreement • Table of Contents
Products by JaxWorks Copyright (C) 2009 JaxWorks All Rights Reserved WARNING ================= This program is protected by copyright law and international treaties. Unauthorized reproduction or distribution of this program, or any portion of it may result in severe civil and criminal penalties, and will be prosecuted to the maximum extent possible under law. LICENSE AGREEMENT ================= You should read the following terms and conditions carefully before using this software. Your use of this software indicates your full acceptance of this license agreement and warranty. LICENSE AGREEMENT ====================================== 1. GRANTING OF LICENSE. JaxWorks, as Licenser, grants to you, the Licensee, a non-exclusive license to use this software program (hereinafter referred to as the "SOFTWARE") in accordance with the terms contained in this license. You may use the SOFTWARE on a single computer. You may access the SOFTWARE through a network, provided that you have obtained individual licenses for the software to cover all workstations that will access the software through the network. 2. COPYRIGHT. All title and copyrights in and to the SOFTWARE, including but not limited to any images, texts, and sounds incorporated into the SOFTWARE, are owned by JaxWorks. 3. REDISTRIBUTION. You CAN NOT freely distribute the SOFTWARE. 4. DISCLAIMER OF WARRANTY. THE SOFTWARE AND THE ACCOMPANYING FILES ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, JAXWORKS DISCLAIMS ALL WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, ANY IMPLIED WARRANTIES OF PERFORMANCE, MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NONINFRINGEMENT. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL JAXWORKS BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL OR INCIDENTAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION OR LOSS OF BUSINESS INFORMATION) ARISING OUT OF THE USE OF OR INABILITY TO USE THE SOFTWARE, EVEN IF JAXWORKS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
JaxWorks http://www.jaxworks.com
© Copyright, 2009, JaxWorks, All Rights Reserved.
Related Documents
Ceo Financial Assistant
May 2020 4
Ceo Finanacial Assistant
May 2020 5
Financial Assistant Tool Con Dati
October 2019 6
Assistant
October 2019 46
Assistant+ +individual+document+assistant
June 2020 17