Worksheet

Worksheet

The purpose of the second part of the comprehensive project is to compute financial statement ratios. Based on the company you selected.

DO ONLY Part – B (DONT do Part – A)

Attached Part – A

Company: ‘Target corporation’ 

 Part – A

1. Based on formulas in your textbook, compute the following ratios for two years. You may use Excel to compute your ratios.
   1. Debt ratio
   2. Gross profit margin
   3. Free cash flow
   4. Times interest earned
   5. Accounts receivable turnover
   6. Inventory turnover
2. Prepare a DuPont Analysis of ROE for two years, including computations of
   1. Return on Sales
   2. Asset Turnover
   3. Return on Assets
   4. Financial Leverage
   5. Return on Equity

Part – B

1. Briefly evaluate the ratio trends. Indicate on your worksheet whether each ratio is:
   1. stronger / weaker
   2. quicker /slower
   3. more / less liquid
   4. more / less risk

1. Write a 3-6 page report evaluating trends in all of the above ratios. Discuss whether your company’s profitability, efficiency, liquidity, and solvency are improving or deteriorating. Suggest ways the company can improve the ratios that show problems. The report should be well written with cover page, introduction, the body of the paper (with appropriate subheadings), conclusion, and reference page. References must be appropriately cited.

Format: Double-spaced, one-inch margins, using a 12-point Times New Roman font. Use APA throughout. 

TARGET CORPORATION 2

1) computed the following ratios for two years

1)	The debt ratio can be calculated as follows
		2020	2019
	Total Liabilities	$30,946	$29,993
	Total Assets	$42,779	$41,290
	Total Debt ratio	72.34%	72.64%

2)	Gross Profit margin
		2020	2019
	Gross Profit margin	$23,248	$22,057
	Total Sales	$78,112	$75,356
	Gross Profit margin	29.76%	29.27%

3)	Free Cash Flow
		2020	2019
	Cash Flow from operations	$7,117	$5,973
	Capital Expenditure	$2,944	$3,416
	Free Cash Flow	$4,173	$2,557

4)	Time Interest Earned ratio
		2020	2019
	EBIT	$4,658	$4,110
	Interest Expense	$468	$434
	Time Interest Earned Ratio	9.95299145	9.47004608

5)	Receivables Turnover
		2020	2019	2018
	Credit Sale	$78,112	$75,356
	Receivables Turnover	0	0	0
	Average Receivable	0	0
	Receivables Turnover	Not defined	Not defined
	Since receivables are zero, therefore the receivable turnover is not defined

6)	Inventory Turnover
		2020	2019	2018
	Cost of goods sold	$54,864	$53,299
	Inventory Turnover	$8,992	$9,497	8597
	Average Inventory	$8,992	$9,497
	Inventory Turnover	6.10142349	5.61219332

2) DuPont Analysis of ROE for two years

To perform DuPont analysis for the return on assets, we first compute the return on sales and the asset turnover. The formula for return on sales is net income divided by revenues. Meanwhile, asset turnover is calculated as revenues divided by total assets. Suppose the return on sales and asset turnover is multiplied by each other. In that case, the result will be equal to the return on assets, calculated as net income divided by total assets.

To perform DuPont analysis for the return on equity, we first compute the return on sales, total asset turnover, and financial leverage (equity multiplier). As said earlier, the return on sales is equal to net income divided by revenues. Total asset turnover is equal to revenues divided by total assets. Financial leverage (equity multiplier) is computed as total assets divided by total equity. If you multiply the return on sales by the total asset turnover and by the financial leverage, we can calculate the return on equity, which can alternatively be computed as net income divided by total equity

DuPont Analysis of ROE for two years	2020	2019
Net Income	3,281.00	2,937.00
Revenue	78,112.00	75,356.00
Total Assets	42,779.00	41,290.00
Total Equity	11,833.00	11,297.00
Return on Sales	4.20%	3.90%
Asset Turnover	1.83	1.83
Return on Assets (Return on Sales * Asset Turnover)	7.67%	7.11%
Financial Leverage (Equity Multiplier)	3.62	3.65
Return on Equity (Net Income / Total Equity)	27.73%	26.00%

TARGET CORPORATION

1

1) computed the following ratios for two years

1)

The debt ratio can be calculated as follows

2020

2019

Total Liabilities

$30,946

$29,993

Total Assets

$42,779

$41,290

Total Debt ratio

72.34%

72.64%

2)

Gross Profit margin

2020

2019

Gross Profit margin

$23,248

$22,057

Total Sales

$78,112

$75,356

Gross Profit margin

29.76%

29.27%

3)

Free Cash Flow

2020

2019

Cash Flow from operations

$7,117

$5,973

Capital Expenditure

$2,944

$3,416

Free Cash

Flow

$4,173

$2,557

TARGET CORPORATION 1

1) computed the following ratios for two years

1) The debt ratio can be calculated as follows

2020 2019

Total Liabilities $30,946 $29,993

Total Assets $42,779 $41,290

Total Debt ratio 72.34% 72.64%

2) Gross Profit margin

2020 2019

Gross Profit margin $23,248 $22,057

Total Sales $78,112 $75,356

Gross Profit margin 29.76% 29.27%

3) Free Cash Flow

2020 2019

Cash Flow from operations $7,117 $5,973

Capital Expenditure $2,944 $3,416

Free Cash Flow $4,173 $2,557