Question

*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL AND ANY NECESSARY FORMULAS, thank you!*

Financial information on AAA Ltd. is shown below.

AAA Ltd. Income Statement
For the Year Ended December 31st,
	2018	2017
Sales	4,215,750	3,850,000
Cost Of Goods Sold	2,178,700	2,016,320
Other Expenses	1,005,200	986,500
Depreciation	9,800	8,550
Earnings Before Interest and Taxes	1,022,050	838,630
Interest Expense	56,735	46,870
Earnings Before Taxes	965,315	791,760
Taxes (30%)	289,595	237,528
Net Income	$ 675,721	$ 554,232

AAA Ltd. Balance Sheet
As at December 31st,
ASSETS	2018	2017
Cash & Equivalent	50,750	23,180
Short-term investments	202,834	186,014
Inventories	1,458,841	1,355,047
Accounts Receivable	218,500	131,221
Total Current Assets	1,930,925	1,695,462
Prop, Plant & Equip - Net	2,154,301	1,940,594
Total Assets	$ 4,085,226	$ 3,636,056
LIABILITIES & EQUITY
Accounts Payable	267,566	258,151
Notes Payable	82,823	62,310
Accruals	58,559	31,210
Short-term Debt	314,469	317,416
Total Current Liabilities	723,417	669,087
Long-Term Debt	833,547	844,139
Total Liabilities	1,556,964	1,513,226
Common Share Capital	650,000	650,000
Retained Earnings	1,878,263	1,472,830
Total Equity	2,528,263	2,122,830
Total Liabilities and Equity	$ 4,085,226	$ 3,636,056

Answer the following questions. Please note: short-term investments are non-operating current assets; notes payable and short-term debt are non-operating current liabilities.

1. What is Operating Cash Flow for 2018?

2. What is Free Cash Flow for 2018?

3. The stock is traded at $30 per share at the end of 2018 and there are 100,000 shares outstanding. What is MVA during 2018?

4. Given the firm’s WACC is 10%, what is EVA during 2018?

5. Create common size income statement and balance sheet for both 2018 and 2017.

6. Create income statement and balance sheet percentage change analysis for 2018. (Use 2017 as the base year)

Answer 1

a. Operating cash flow gives us an estimate of the amount of cash generated by day to day operations of a business. It tells us whether the company is able to generate enough cash flow so as to maintain and grow its day to day business.

Cash flow from operations can be calculated by various methods, one of which is mentioned below

Operating cash flow = Net income +Depreciation/Amortization (-)/(+) Increase/decrease in account receivable (-)/(+) Increase/decrease in inventory (+)/(-) Increase/decrease in account payable (+)Increase in other accrued liabilities OR (-) decrease in other accrued liabilities

Net Income	675,721
(+)Depreciation	9,800
(-)Increase in account receivable OR (+) decrease in Account receivable	-87,279
(-)Increase in inventory OR (+) decrease in inventory	-103,794
(+)Increase in account payable OR (-) decrease in account payable	9,415
(+)Increase in other accrued liabilities OR (-) decrease in other accrued liabilities	27,349
Operating Cash flow	531,212

LIABILITIES & EQUITY	Increase in Account payable and accruals	2018	2017
Accounts Payable	9,415 (=267,566 - 258,151)	267,566	258,151
Accruals	27,349 (=58,559 - 31,210)	58,559	31,210

ASSETS	Increase in Account receivables and inventory	2018	2017
Inventories	1,03,794 (=1,458,841-1,355,047)	1,458,841	1,355,047
Accounts Receivable	87,279 (=218,500-131,221)	218,500	131,221

Based on the above formulas Operating cash flow (OCF) = $531,212

--------------------------------------------------------------------------------------------------------------------------------------

b. Free cash flow(FCF) is the cash left with the company after all the expenses for operating activities and capital expenditure have been paid.

FCF = OCF - Fixed capital investment (+) Net borrowing

Capital Expenditure = Fixed assets (current period) – Fixed assets (prior period) + Depreciation (current period)

,= 2,154,301 - 1,940,594 + 9800 = 223,507

Operating cash flow	5,31,212
(-)Fixed capital investment	2,23,507
(+)increase in Net borrowings OR (-)decrease in net borrowing	-10,592
FCF	297,113

Free cash flow = $ 297,113

--------------------------------------------------------------------------------------------------------------------------------------

c. MVA is the market value added is the difference between the market value of the company and the invested capital

The market value of the company = Price per share * Total outstanding shares

Invested capital = Total equity

Therefore , MVA at the end of 2018 = $30 *100,000 - $ 2,122,830 = $3,000,000 - $ 2,122,830 = $877,170

--------------------------------------------------------------------------------------------------------------------------------------

d.

EVA = NOPAT – (WACC * capital invested)          

NOPAT = Net Operating Profits After Tax

WACC = Weighted Average Cost of Capital = 10%

Capital invested = Equity + long-term debt at the beginning of the period (i.e at the end of 2017)

=$ 2,122,830 + $844,139 = $2,966,969

NOPAT = EBIT * ( 1- Tax) = 1,022,050 *(1- 30%) = $715,435

EVA = $715,435 - ( 10% * 2,966,969) = $715,435 - $296,696 = $418,739

and (WACC* invested capital ) is also called as a finance charge

e. Common size income statement is prepared by considering revenue as 100% and calculating the rest of the items in terms of revenue

For the Year Ended December 31st,
	2018		2017
Sales	42,15,750	100%	38,50,000	100%
Cost Of Goods Sold	21,78,700	52%	20,16,320	52%
Other Expenses	10,05,200	24%	9,86,500	26%
Depreciation	9,800	0%	8,550	0.22%
Earnings Before Interest and Taxes	10,22,050	24%	8,38,630	22%
Interest Expense	56,735	1.35%	46,870	1%
Earnings Before Taxes	9,65,315	23%	7,91,760	21%
Taxes (30%)	2,89,595	7%	2,37,528	6%
Net Income	675721	16%	5,54,232	14%

Common size balance sheet is prepared by considering total assets and total liabilites as 100% and calculating the other items under assets and liabilities+ equity with respect to total aseets and total liabilities

AAA Ltd. Balance Sheet
As at December 31st,
ASSETS	2018		2017
Cash & Equivalent	50,750	1.2%	23,180	0.6%
Short-term investments	2,02,834	5%	1,86,014	5.1%
Inventories	14,58,841	36%	13,55,047	37.3%
Accounts Receivable	2,18,500	5%	1,31,221	3.6%
Total Current Assets	19,30,925	47%	16,95,462	46.6%
Prop, Plant & Equip - Net	21,54,301	53%	19,40,594	53.4%
Total Assets	40,85,226	100%	36,36,056	100%