University of Indianapolis Cash Flow Ratios Excel Task

Description

2 attachmentsSlide 1 of 2attachment_1attachment_1attachment_2attachment_2

Unformatted Attachment Preview

2018
To record issue
700
To record secured note to shareholder
3 year note
Issue blend of debt and equity totalling
To record
Amortize/dpr
60000
5
Note Receivable
Professional Service Revenue
To record 6-Year note @
In Exchange for Professional Service Revenue
To record purchase of
To record sale of
To record
Amortize/dpr
To Assign
Discount
Fee
Rate
50000
5
170000
0.28
0.12
0.08
Common scriptions offered
Shares
Subscription pr.
Cash received on subscriptions
Declare script dividend on
of EPS
Interest rate
To discount Note Receivable on last day of yr
Record tax expense @
2019
To record purchase of
To settle 2015 Accounts Payable
To record sale of
To record receipt of
To record receipt of
To record purchase of
To record sale of Equipment 1
To record sale of
To record
70000
To record declaration of
Record tax expense @
Accounts Receivable Aging Summary
3.0
Healthy, low probability of bankrup
1.80 – 2.99
Caution
< 1.80 High probability of bankruptcy Receivables Ratios Year 2 (1.0 point) Collection Efficiency Beg Rec + Cr Sales - Ending Rec beg Rec + Cr Sales - Current Rec Bad Debt Ratio Bad debt Cr Sales Percent Current Current Rec Tot Rec Receivable/Sales Ratio Accts Rec Total Revenue Plus 60 Ratio Rec > 60 Days
Tot Rec
Accts Rec Turnover
Cr Sales
Ave Rec
Days in Receivables
Ave Rec
Daily Cr Sls
Days Sales Outstanding
Accts Rec
Cr Sales
times
–
2018
Cash
Common
Paid in Excess
To record issue
126,000
700 shares @
Cash
Secured Note to Shareholder
2018
Equipment
Note Payable
To record
Amortize/dpr
7,000
119,000
10 par
180 Mkt
54000
54000
180,000
60,000
60,000
7% annual
60,000 Note @
5 years ddb
2018
Note Receivable
Professional Service Revenue
125,000
125,000
To record 6-Year note @
11%
In Exchange for Professional Service Revenue
125,000
2018
Inventory
Accounts Payable
To record purchase of
2018
Accounts Receivable
Revenue
COGS
Inventory
To record sale of
2018
Equipment
Note Payable
To record
Amortize/dpr
2015
200,000
400
200,000
units @
500
315,000
315,000
180,000
360
180,000
units @
50,000
50,000 Note @
5 years SL
50,000
9% annual
875
Assigned Rec
Accts Rec
To Assign
Discount
Fee
Rate
170,000
170,000
170,000 Receivables
0.28
0.12
0.08
Cash
Fee
Payable to Factor
Cash
Loss
Discounted Note
To discount Note Receivable on last day of yr
Subscriptions rec
Common subscribable
Pd in Excess
Cash
Subscriptions rec
Common subscribable
Common
Retained Earn
Note payable shareholders
Declare script dividend on
of EPS
Interest rate
@
18%
30000
30000
shares @
0.35
343,750
units @
625
0.08
Pay largest dividend possible at year end
Interest Exp.
Secured Note
Cash
2019
Inventory
Accounts Payable
To record purchase of
2019
Accts Payable
Cash
To settle 2018 Accounts Payable
343,750
550
2019
Accounts Receivable
Revenue
COGS
Inventory
To record sale of
412,500
412,500
375
units @
1100
2019
Cash
Assigned Receivables
102000
102000
Factor Payable
Interest exp
Cash
To record receipt of
102,000.00
2019
Cash
Accts Receivables
To record receipt of
102,000
Assigned receivables
270,000
270,000
2016
TS
Cash
To record purchase of
270,000
Receivables
21120
110
21120
Treasury shares
192
2016
Cash
Equip
AD
Gain
38000
To record sale of Equipment 1
38000
60,000
7/25/2016
2016
Cash
TS
Pd in Ts
To record sale of
2016
Retained Earnings
Cash
To record
2016
14350
70 Treasure shares
70,000.00
70,000 Cash
70,000.00
dividend
205
Retained Earnings
Common Distributable
Paid in Excess
To record declaration of
10%
stock dividend @
205
Interest exp
NP shareholders
Interest Exp.
Secured Note
Cash
Question Set:
15.0 Points
Analyze and discuss options for transactions listed below:
1. Blend of debt and equity (transaction #2)
a. Impact on Balance Sheet
b. Impact on Financial Ratios
c. Impact on Altman Z-Score
d. What is the ‘cost’ of maintaining a
300,000 minimum cash balance?
e. How would key ratios and Z-Score be affected if threshold were reduced to
f. Impact on stock price, market capitalization
2. Decision to assign receivables (transaction # 8)
a. Impact on Working Capital
b. Impact on dividend potential
c. Cost/benefit trade-off of converting Receivables to immediate cash
3. Impact of script dividend (transaction #10)
a. Alt Z Score impact
b. Cost/benefit considerations
c. Discuss effect of changing % of EPS on (a) and (b) above
4. Impact of discounting note receivable at end of Year 1
a. Balance Sheet impact
b. Discount costs vs. cash requirements (reference 1d above)
5. Amount of the 2016 Cash Dividend
6. Effect of 2016 Stock Dividend
a. Permenant effect on Retained Earnings
b. Alt Z impact
c. Impact on Cash Flow ratios
2. Discuss strategic implications for:
a. Degree of Operating Leverage, DOL
b. Degree of Financial Leverage, DFL
c. Degree of Combined Leverage, DCL
3. What does the difference beween Return on Assets and Return on Operating Assest sug
about the capital structure of this firm, and what would be your recommendations to m
4. What assumptions underlie the Outside Funds Forecast, and how is the efficacy of this m
governed by stage in product/industry life-cycle?
shares @
10 par
180 mkt
e to shareholder
54,000
d equity totalling
5% annual
180,000.00
Note @
years ddb
0.07 annual
on equipment purchase
125000
125000
125000
11
sional Service Revenue
400
units @
500 on account
360
units @
875 on account
Note @
years SL
0.09 annual
on equipment purchase
Receivables
300
144
30000
shares @
0.35
@
0.18
0.08
ivable on last day of yr
0.35 paid in cash
550
units @
625
375
units @
1100
102,000.00
Assigned receivables
270,000.00
Receivables
110
Treasury shares
38000
7/25/2016
70 Treasure shares
Cash
192
205
dividend
0.1
stock dividend @
205
0.35 paid in cash
ging Summary
30 – 60
61 – 90
0.2
0.12
90+
0.15
0.36
Accounts Payable
Contingent Payable
Payable to Factor
Note payable to shareholders
Current Portion
Secured Note
Note 1
Note2
Common
Common subscribable
Common distributable
0.1
0.5
1
2018
2019
Paid in excess
TS Stock
Pd in TS
Retained Earn
Total
Cash Flows
2018
Net Income
Depr
Accts Rec
Assigned Rec
Allowance
Note Rec
Not Rec Disc
Loss add back
Gain subtract
Inventory
Accts Pay
Factor Pay
Contingent Pay
Opr Cash
Investing
Financing
Sec Note amt
Secured Note
100,000.00 Net Income
Note 1
Note 2
Note Rec Disc
Dividend
Paid in
TS
Common Subsc.
Note sharehldrs
Beginning
Net Cash
Ending
2019
Return on Assets
1.0 Point (combined)
#DIV/0!
DuPont Proof ROA
Sales
Assets
Net
Sales
#VALUE!
#VALUE!
#VALUE!
–
Cash FlowAdequacy
Cash from Operating Activities
Capital Expenditures + Dividends
EBIDA Coverage
EBIDA
Interest Expense
Free Operating Cash Flow/Total Debt
Cash from Operating Activities – CAPEX
Total Debt
Quick Ratio
Liquid Current Assets
Current Liabilities
Weight
Retained Earn
Total Assets
Weight
1.4
Weight
–
EBIT
Total Assets
3.3
Sales
Total Assets
Z – Score
1
hy, low probability of bankruptcy
probability of bankruptcy
ear 2 (1.0 point)
(2.0 Points)
Shares Outstanding
BV/Share
ROE
EPS (BV/share * ROE)
Beginning
End
EPS (Net/Shares Ost)
Payout Ratio
Plowback Ratio
Growth rate
Dividend/share
Investor return rate
Share Price
Market Capitalization
Market Value Added
0.35
Book value/share
0.35
Valuation
2018 EBIDA
2018 EBDAT
NOPAT
NOPAT (net interest, tax)
2018 EPS (Net/Shares)
2.0 Points
Between Stated and Target Income Levels for 2019
DOL
% Change Inc
% Change Vol
DFL
Present
rd
Debt
Equity
Total
Shares
BV/share
Days in
Period
365.000
Unlevered
0.1
–
Present
Present
EBIT
Interest
EBT
Tax
Net Income
Shares
0.1
75,000
(75,000)
–
Unlevered
–
7,500.00
(7,500)
–
(2,625.00)
–
(4,875)
EPS
At 100,000 Net EBIT
Interest
EBT
Tax
Net Income
Shares
EPS
–
7,500.00
(7,500.00)
(2,625.00)
–
(4,875.00)
Between
–
DFL
Present
% Change in E.P.S.
% Change in Operating Income
DFL Unlevered
% Change in E.P.S.
% Change in Operating Income
Present
Sales
EBIT
Interest
EBT
Tax
Net Income
Shares
EPS
Present
Unlevered
EBIT
Interest
EBT
Tax
Net Income
Shares
EPS
Present
Target
–
–
7,500.00
(7,500.00)
(2,625.00)
(4,875.00)
Target
7,500.00
(7,500.00)
(2,625.00)
(4,875.00)
–
mum cash balance?
100,000
#DIV/0!
#VALUE!
Per share value
els for 2019
#DIV/0!
–
#DIV/0!
#DIV/0!
DCL
Present
Percent Change in E.P.S.
Percent Change in Sales
Unlevered
Percent Change in E.P.S.
Percent Change in Sales
DCL
Present
Percent Change in E.P.S.
Percent Change in Sales
Unlevered
Percent Change in E.P.S.
Percent Change in Sales
#DIV/0!
#DIV/0!
2018
To record issue
700
To record secured note to shareholder
3 year note
Issue blend of debt and equity totalling
To record
Amortize/dpr
60000
5
Note Receivable
Professional Service Revenue
To record 6-Year note @
In Exchange for Professional Service Revenue
To record purchase of
To record sale of
To record
Amortize/dpr
To Assign
Discount
Fee
Rate
50000
5
170000
0.28
0.12
0.08
Common scriptions offered
Shares
Subscription pr.
Cash received on subscriptions
Declare script dividend on
of EPS
Interest rate
To discount Note Receivable on last day of yr
2019
To record purchase of
To settle 2015 Accounts Payable
To record sale of
To record receipt of
To record receipt of
To record purchase of
To record sale of Equipment 1
To record sale of
To record
70000
To record declaration of
Accounts Receivable Aging Summary
3.0
Healthy, low probability of bankru
1.80 – 2.99
Caution
< 1.80 High probability of bankruptcy Receivables Ratios Year 2) Collection Efficiency Beg Rec + Cr Sales - Ending Rec beg Rec + Cr Sales - Current Rec Bad Debt Ratio Bad debt Cr Sales Percent Current Current Rec Tot Rec Receivable/Sales Ratio Accts Rec Total Revenue Plus 60 Ratio Rec > 60 Days
Tot Rec
Accts Rec Turnover
Cr Sales
Ave Rec
Days in Receivables
Ave Rec
Daily Cr Sls
Days Sales Outstanding
Accts Rec
Cr Sales
times
–
TA0/NS0 * ΔNS – (AP0 + LOC0 + AL0)/NS0 *
Build the cash flow forecast for outside funds, com
and discuss this firm”s strategy for raising cash.
Forecast
Actual
Analyze and discuss options for transactions listed below:
1. Blend of debt and equity (transaction #2)
a. Impact on Balance Sheet
b. Impact on Financial Ratios
c. Impact on Altman Z-Score
d. What is the ‘cost’ of maintaining a
300,000 minimum cash balance?
e. How would key ratios and Z-Score be affected if threshold were reduced to
f. Impact on stock price, market capitalization
2. Decision to assign receivables (transaction # 8)
a. Impact on Working Capital
b. Impact on dividend potential
c. Cost/benefit trade-off of converting Receivables to immediate cash
3. Impact of script dividend (transaction #10)
a. Alt Z Score impact
b. Cost/benefit considerations
c. Discuss effect of changing % of EPS on (a) and (b) above
4. Impact of discounting note receivable at end of Year 1
a. Balance Sheet impact
b. Discount costs vs. cash requirements (reference 1d above)
5. Amount of the 2016 Cash Dividend
6. Effect of 2016 Stock Dividend
a. Permenant effect on Retained Earnings
b. Alt Z impact
c. Impact on Cash Flow ratios
2018
Cash
Common
Paid in Excess
To record issue
126,000
7,000
119,000
10 par
180 Mkt
700 shares @
Cash
Secured Note to Shareholder
54000
54000
180,000
2018
Equipment
Note Payable
To record
Amortize/dpr
60,000
60,000
7% annual
60,000 Note @
5 years ddb
2018
Note Receivable
Professional Service Revenue
125,000
125,000
To record 6-Year note @
11%
In Exchange for Professional Service Revenue
125,000
2018
Inventory
Accounts Payable
To record purchase of
2018
Accounts Receivable
Revenue
COGS
Inventory
To record sale of
200,000
400
200,000
units @
500
315,000
315,000
180,000
360
180,000
units @
875
2018
Equipment
Note Payable
To record
Amortize/dpr
50,000
50,000
9% annual
50,000 Note @
5 years SL
2015
Assigned Rec
Accts Rec
To Assign
Discount
Fee
Rate
170,000
170,000
170,000 Receivables
0.28
0.12
0.08
Cash
Fee
Payable to Factor
107,712
14,688
122,400
Cash
$92,398.74
Loss
16,804.19
Note rec discount
109,202.93
To discount Note Receivable on last day of yr
@
Subscriptions rec
Common subscribable
Pd in Excess
Cash
Subscriptions rec
Common subscribable
Common
43,200.00
3,000.00
40,200.00
30000
30000
2,083.33
2,083.33
Retained Earn
68,900.235
Note payable shareholders
Declare script dividend on
908.33
of EPS
Interest rate
0.08
216.7241977 Net INC/ #shares
Pay largest dividend possible at year end
Interest Exp.
Secured Note
Cash
18%
2,700
$17,129.26
$19,829.26
68,900.235
shares @
208.33
0.35
2019
Inventory
Accounts Payable
To record purchase of
2019
Accts Payable
Cash
To settle 2018 Accounts Payable
2019
Accounts Receivable
Revenue
COGS
Inventory
To record sale of
343,750
550
343,750
units @
625
200,000
200,000
412,500
412,500
229,375
375
229,375
units @
1100
2019
Cash
Assigned Receivables
102000
Factor Payable
Interest exp
Cash
To record receipt of
92,208
9,792
102000
102,000.00
102,000
Assigned receivables
2019
Cash
Accts Receivables
To record receipt of
270,000
270,000
270,000
Receivables
2016
TS
Cash
To record purchase of
21120
110
21120
Treasury shares
2016
Cash
Equip
AD
Gain
38000
60,000
32,127
10,127
To record sale of Equipment 1
38000
2016
Cash
14350
7/25/2016
192
TS
Pd in Ts
To record sale of
13440
910
70 Treasure shares
2016
Retained Earnings
Cash
To record
70,000.00
70,000.00
dividend
70,000 Cash
2016
Retained Earnings
Common Distributable
Paid in Excess
To record declaration of
Interest exp
NP shareholders
205
17,800.83
10%
–
Interest Exp.
Secured Note
Cash
1,844
$17,985.73
Cont loss
note re Dis
cont pay
12,012
$17,534.75
$19,829.26
$29,547.07
868.33
16,932.50
stock dividend @
205
shares @
10 par
180 mkt
e to shareholder
54,000
d equity totalling
5% annual
180,000.00
Note @
years ddb
0.07 annual
on equipment purchase
125000
125000
125000
11
sional Service Revenue
400
units @
500 on account
360
units @
875 on account
Note @
years SL
0.09 annual
on equipment purchase
Receivables
300
144
30000
shares @
0.35
@
0.18
0.08
ivable on last day of yr
550
units @
625
375
units @
1100
102,000.00
Assigned receivables
270,000.00
Receivables
110
Treasury shares
38000
7/25/2016
192
70 Treasure shares
Cash
205
dividend
0.1
stock dividend @
205
ging Summary
30 – 60
61 – 90
0.2
0.12
6,900.00
90+
0.15
0.36
15,525.00
0.1
0.5
14,375.00
2018
200,000
Accounts Payable
Contingent Payable
Payable to Factor
0
122,400
Note payable to shareholders
–
Current Portion
Secured Note
Note 1
Note2
$
1
39,962.50
2019
343,750
$29,547.07
30,192
–
$38,256.05
$40,756.39
18,885.01
38,402.78
32,538.84
$0.00
26,457.53
22,612.71
450,483
Common
Common subscribable
Common distributable
Paid in excess
TS Stock
Pd in TS
Retained Earn
9,083
917
159,200
0
0
127,957.58
9,083
917
868
176,133
-7680
910
144,453.64
493,316
Total
747,640.25
Cash Flows
2018
Net Income
Depr
Accts Rec
Assigned Rec
Allowance
Note Rec
Not Rec Disc
Loss add back
Gain subtract
Inventory
Accts Pay
Factor Pay
Contingent Pay
Opr Cash
Investing
Financing
Sec Note amt
Secured Note
Note 1
Note 2
Note Rec Disc
Dividend
Paid in
TS
Common Subsc.
Note sharehldrs
Beginning
Net Cash
Ending
196,857.81
34,000
145,000
170,000
109,203
(109,203)
20,000
200,000
14,688
780,545.81
2019
104,296.90
18,127
287,500
238,000
39,963
109,203
(91,668)
(114,375)
143,750
29,547
764,343.34
38,000
(17,985.73)
(11,163.78)
(9,106.54)
(17,534.75)
(70,000.00)
(6,770.00)
818,000.17
Cash FlowAdequacy
780,545.8129
196,858
3.96502329
Cash from Operating Activities
Capital Expenditures + Dividends
EBIDA Coverage
226,000
11,400
19.8245614
EBIDA
Interest Expense
Free Operating Cash Flow/Total Debt
780,546
Cash from Operating Activities – CAPEX
Total Debt
Quick Ratio
886,900
493,316
Weight
1.797835369
Retained Earn
Total Assets
1.4
Weight
Weight
EBIT
Total Assets
3.3
Sales
Total Assets
1
hy, low probability of bankruptcy
probability of bankruptcy
Liquid Current Assets
Current Liabilities
Z – Score
866,900
493,316
Shares Outstanding
Beginning
End
908.33
BV/Share
ROE
EPS (BV/share * ROE)
EPS (Net/Shares Ost)
327.15
0.66247
Payout Ratio
Plowback Ratio
Growth rate
Dividend/share
Investor return rate
Share Price
Market Capitalization
Market Value Added
0.35
(297,157.58)
868.33
Days in
Period
365.000
AP0 + LOC0 + AL0)/NS0 * ΔNS – NS1 * NI0/NS0 * Plowback
recast for outside funds, compare the forecast based on the formula above,
s strategy for raising cash.
mum cash balance?
100,000
Date
INT
0
1
2
3
Date
PMT
2700
1843.536875
944.2505948
INT
0
1
2
3
4
5
4,200
3,470
2,688
1,852
957
Year
0
1
2
3
13,750
12,012
10,083
7,943
5,566
2,928
$14,633.44
$14,633.44
$14,633.44
$14,633.44
$14,633.44
$17,129.26
$17,985.73
$18,885.01
Note1
54000
$36,870.74
$18,885.01
$0.00
5%
Balance
$10,433.44
$11,163.78
$11,945.25
$12,781.41
$13,676.11
carry value
60,000 Equip 1
49,566.56
38,402.78
26,457.53
13,676.11
–
AMT
$29,547.07
$29,547.07
$29,547.07
$29,547.07
$29,547.07
$29,547.07
(1/5)*2
Book value
Rate
60,000
36,000
21,600
12,960
Balance
AMT
exp
int rev
PMT
INT
0
1
2
3
4
5
6
$19,829.26
$19,829.26
$19,829.26
PMT
int
Date
AMT
Balance
Note RECIEVABLE
125,000
$15,797.07
109,202.93
$17,534.75
91,668.18
$19,463.57
72,204.61
$21,604.56
50,600.05
$23,981.07
26,618.98
$26,618.98
douple declining dep
Dep
0.4
0.4
0.4
equip 1 (Douple decline)
24,000
14,400
8,640
8,127.12
32,127.12
4
5
Date
7,776
–
INT
0
1
2
3
4
5
Year
0
1
2
3
4
5
0.4
PMT
4,500
3,748
2,928
2,035
1,061
Book value
Dep
50,000
40,000
30,000
20,000
10,000
–
5,184
7,776
AMT
$12,854.62
$12,854.62
$12,854.62
$12,854.62
$12,854.62
$8,354.62
$9,106.54
$9,926.13
$10,819.48
$11,793.23
Balance
50,000 Equip 2
41,645.38
32,538.84
22,612.71
11,793.23
–
equip 2 Dep
10,000
10,000
10,000
10,000
10,000
780,546
244,127
11,400
–
1.757293379
21.41

Purchase answer to see full
attachment

Tags:
current liabilities

Cash from Operating Activities

Total Debt

User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.