Read the instructions to know

profileJad Agil
FA17_36054_ExcelProject_Ch9_Template.xlsx
Home>Business & Finance homework help>Accounting homework help>Read the instructions to know

BaseCase

AFN: $0.00
Key Input Data: Used in the If AFN > 0 If AFN < 0
forecast % from each % from each
Tax rate New notes payable Reduce notes payable
Dividend growth rate New long-term debt Reduce long-term debt
S-T rd New common stock Reduce common stock
L-T rd Sell marketable securities Buy marketable securities
Sales growth rate TOTAL PERCENT 0% Pay special dividend
TOTAL PERCENT 0%
December 31 Income Statements: Forecasting Avg 2016 2016
2013 2014 2015 basis Ratios Inputs Without AFN AFN With AFN
Sales $409,635.00 $434,213.10 $482,459.00 Growth
Cost of goods sold $352,286.10 $379,936.46 $410,090.15 % of sales
EBITDA $57,348.90 $54,276.64 $72,368.85
Depreciation and Amortization $13,100.13 $14,200.80 $15,500.69 % of fixed assets
EBIT $44,248.77 $40,075.84 $56,868.16
Net Interest Expense $15,000.00 $15,175.63 $16,139.86
EBT $29,248.77 $24,900.21 $40,728.31
Taxes (40%) $11,699.51 $9,960.08 $16,291.32
Net Income $17,549.26 $14,940.12 $24,436.98
Common dividends (regular dividends) $8,000.00 $8,480.00 $9,073.60 Growth
Special Dividends $0.00 $0.00 $0.00
Addition to retained earnings (DRE) $9,549.26 $6,460.12 $15,363.38
Number of Shares 100,000 100,000 100,000
Market Price Per Share $30.43 $32.96 $38.86
December 31 Balance Sheets Forecasting Average 2016 2016
basis Ratios Inputs Without AFN AFN With AFN
2013 2014 2015
Assets:
Cash $18,023.94 $18,236.95 $19,298.36 % of sales
Marketable Securities $13,057.00 $14,021.00 $18,645.00 Previous $0.00
Accounts Receivable $102,408.75 $100,133.00 $106,140.98 % of sales
Inventories $45,059.85 $45,515.00 $48,245.90 % of sales
Total current assets $178,549.54 $177,905.95 $192,330.24
Fixed assets $159,757.65 $182,060.00 $192,983.60 % of sales
Total assets $338,307.19 $359,965.95 $385,313.84
Liabilities and equity
Accounts payable $27,855.18 $31,860.50 $33,772.13 % of sales
Accruals $35,177.29 $41,330.00 $47,592.54 % of sales
Notes payable $20,000.00 $21,951.47 $32,665.06 Previous
Total current liabilities $83,032.47 $95,141.97 $114,029.73
Long-term debt $120,000.00 $120,000.00 $120,000.00 Previous
Total liabilities $203,032.47 $215,141.97 $234,029.73
Common stock $60,000.00 $60,000.00 $60,000.00 Previous
Retained Earnings $75,274.72 $84,823.98 $91,284.11 Previous + DRE
Total common equity $135,274.72 $144,823.98 $151,284.11
Total liabilities and equity $338,307.19 $359,965.95 $385,313.84
SHOULD BE $0

Modification

AFN: $0.00
Key Input Data: Used in the If AFN > 0 If AFN < 0
forecast % from each % from each
Tax rate New notes payable Reduce notes payable
Dividend growth rate New long-term debt Reduce long-term debt
S-T rd New common stock Reduce common stock
L-T rd Sell marketable securities Buy marketable securities
Sales growth rate TOTAL PERCENT 0% Pay special dividend
TOTAL PERCENT 0%
December 31 Income Statements: Forecasting Avg 2016 2016
2013 2014 2015 basis Ratios Inputs Without AFN AFN With AFN
Sales $409,635.00 $434,213.10 $482,459.00 Growth
Cost of goods sold $352,286.10 $379,936.46 $410,090.15 % of sales
EBITDA $57,348.90 $54,276.64 $72,368.85
Depreciation and Amortization $13,100.13 $14,200.80 $15,500.69 % of fixed assets
EBIT $44,248.77 $40,075.84 $56,868.16
Net Interest Expense $15,000.00 $15,175.63 $16,139.86
EBT $29,248.77 $24,900.21 $40,728.31
Taxes (40%) $11,699.51 $9,960.08 $16,291.32
Net Income $17,549.26 $14,940.12 $24,436.98
Common dividends (regular dividends) $8,000.00 $8,480.00 $9,073.60 Growth
Special Dividends $0.00 $0.00 $0.00
Addition to retained earnings (DRE) $9,549.26 $6,460.12 $15,363.38
Number of Shares 100,000 100,000 100,000
Market Price Per Share $30.43 $32.96 $38.86
December 31 Balance Sheets Forecasting Average 2016 2016
basis Ratios Inputs Without AFN AFN With AFN
2013 2014 2015
Assets:
Cash $18,023.94 $18,236.95 $19,298.36 % of sales
Marketable Securities $13,057.00 $14,021.00 $18,645.00 Previous
Accounts Receivable $102,408.75 $100,133.00 $106,140.98 % of sales
Inventories $45,059.85 $45,515.00 $48,245.90 % of sales
Total current assets $178,549.54 $177,905.95 $192,330.24
Fixed assets $159,757.65 $182,060.00 $192,983.60 % of sales
Total assets $338,307.19 $359,965.95 $385,313.84
Liabilities and equity
Accounts payable $27,855.18 $31,860.50 $33,772.13 % of sales
Accruals $35,177.29 $41,330.00 $47,592.54 % of sales
Notes payable $20,000.00 $21,951.47 $32,665.06 Previous
Total current liabilities $83,032.47 $95,141.97 $114,029.73
Long-term debt $120,000.00 $120,000.00 $120,000.00 Previous
Total liabilities $203,032.47 $215,141.97 $234,029.73
Common stock $60,000.00 $60,000.00 $60,000.00 Previous
Retained Earnings $75,274.72 $84,823.98 $91,284.11 Previous + DRE
Total common equity $135,274.72 $144,823.98 $151,284.11
Total liabilities and equity $338,307.19 $359,965.95 $385,313.84
SHOULD BE $0

Ratios

Actual Values Projected 2016
2013 2014 2015 Base Case Modification Provide a discussion of the liquidity ratios here
Liquidity Ratios Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios.
Current Ratio
Quick Ratio
Asset Management Ratios
Inventory Turnover
Fixed Asset Turnover Provide a discussion of the asset management ratios here
Days Sales Outstanding Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios.
Total Asset Turnover
Debt Management Ratios
Debt-to-Assets Ratio
Debt-to-Equity Ratio
Times Interest Earned Provide a discussion of the debt management ratios here
Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios.
Profitability Ratios
Net Profit Margin
Operating Profit Margin
Basic Earning Power
Return on Assets
Return on Equity Provide a discussion of the profitability ratios here
Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios.
Market Value Ratios
Price/Earnings Ratio
Market/Book Ratio
Price/Cash Flow Ratio
Provide a discussion of the market value ratios here
Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios. Here is my discussion of the ratios.