Question

Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page.

A firm had the following abbreviated income statement for 2020 and abbreviated balance sheets at the end of 2020 and 2021.

	2020
Sales	$600,000
Less Cost of goods sold	320,000
Gross Profit	280,000
Less Operating expenses	190,000
Less Depreciation	30,000
Operating Income (EBIT)	60,000
Less Interest expense	20,000
Earnings Before Taxes (EBT)	40,000
Less Income tax	14,000
Earnings After Tax (Net Income)	$26,000

	2020	2021
Cash	$13,000	$15,000
Marketable securities	41,000	40,000
Accounts receivable	22,000	25,000
Inventory	60,000	60,000
Prepaid expenses	11,000	20,000
Buildings (net)	620,000	600,000
Equipment (net)	109,000	100,000
Total Assets	$876,000	$860,000
Accounts payable	20,000	40,000
Wages payable	5,000	3,000
Bonds payable	414,000	364,000
Total Liabilities	439,000	407,000
Common stock	200,000	200,000
Retained Earnings	237,000	253,000
Total Equity	437,000	453,000
Total Liabilities & Equity	$876,000	$860,000

The company paid a dividend in 2021 of $10,000. All sales are made on account.

Required:

A. Calculate the current ratio at the end of 2021. _______________

B. Calculate the quick ratio at the end of 2021. ________________

C. Calculate the working capital at the end of 2021. _______________

D. Calculate average age of accounts receivable during 2021. _______________

E. Calculate the inventory turnover during 2021. _________________

F. Calculate the average age of accounts payable during 2021. _____________

G. Calculate the accounts receivable turnover during 2021. _____________

H. Calculate the pre-tax profit margin for 2021. ____________

I. Calculate the cash conversion cycle, in days during 2021. _____________

J. Calculate the debt/equity ratio at the end of 2021. ________________

K. Calculate the debt ratio at the end of 2021. ______________

L. Calculate the times interest earned ratio for 2021. _______________

M. Calculate the dividend payout ratio for 2021. _________________

N. Calculate the gross profit margin for 2021. _____________

O. Calculate the operating profit margin for 2021. ____________

P. Calculate the net profit margin for 2021. _______________

Q. Calculate the return on assets (ROA) for 2021. _________________

R. Calculate the total asset turnover during 2021. _______________

S. Calculate the return on equity (ROE for 2021. ______________

T. Calculate the marginal tax rate for 2021. _________________

Answer 1

	2020
Sales	$6,00,000
Less Cost of goods sold	3,20,000
Gross Profit	2,80,000
Less Operating expenses	1,90,000
Less Depreciation	30,000
Operating Income (EBIT)	60,000
Less Interest expense	20,000
Earnings Before Taxes (EBT)	40,000
Less Income tax	14,000
Earnings After Tax (Net Income)	$26,000
	2020	2021
Cash	$13,000	$15,000
Marketable securities	41,000	40,000
Accounts receivable	22,000	25,000
Inventory	60,000	60,000
Prepaid expenses	11,000	20,000
Buildings (net)	6,20,000	6,00,000
Equipment (net)	1,09,000	1,00,000
Total Assets	$8,76,000	$8,60,000
Accounts payable	20,000	40,000
Wages payable	5,000	3,000
Bonds payable	4,14,000	3,64,000
Total Liabilities	4,39,000	4,07,000
Common stock	2,00,000	2,00,000
Retained Earnings	2,37,000	2,53,000
Total Equity	4,37,000	4,53,000
	8,76,000	8,60,000

Current Asset	$1,60,000
Current Liability	43,000
Quick assets	$1,00,000
Sales	$6,00,000
Accounts receivable	25,000
Cost of goods sold	3,20,000
Average inventory	60000
Accounts payable	40,000
Average receivables	23500
Pre-tax profit	40,000
Total debt	3,64,000
Equity	4,53,000
Total assets	8,60,000
EBIT	60,000
Operating profit	60,000
Gross profit	2,80,000
Tax	14,000
EAT	26,000
Total equity	4,53,000
Particulars	Equation	Ratio
A. Calculate the current ratio at the end of 2021	CA/CL	3.72
B. Calculate the quick ratio at the end of 2021.	Quick assets/ CL	2.33
C. Calculate the working capital at the end of 2021.	CA-CL	1,17,000.00
D. Calculate average age of accounts receivable during 2021.	Accounts receivable in an accounting period x 365 ÷ sales revenue in that period	15.21
E. Calculate the inventory turnover during 2021.	Cost of goods sold/ avg Inventory	5.33
F. Calculate the average age of accounts payable during 2021.	Accounts payable/COGS*365	45.63
G. Calculate the accounts receivable turnover during 2021.	Net credit sales/ avg receivables	25.53
H. Calculate the pre-tax profit margin for 2021.	pre-tax profit/ sales	6.67%
I. Calculate the cash conversion cycle, in days during 2021.	Days inventory o/s + Days sales o/s - Days payable o/s
Days inventory o/s	Inventory/ COGS* 365	68.44
Days sales o/s	Accounts receivable/sales *365	15.21
Days payable o/s	Accounts payable/COGS*365	45.625
		38.02
J. Calculate the debt/equity ratio at the end of 2021.	Total debt/ equity	0.80
K. Calculate the debt ratio at the end of 2021.	Debt / total assets	0.42
L. Calculate the times interest earned ratio for 2021.	EBIT/ total interest exp	3.00
M. Calculate the dividend payout ratio for 2021.	Dividend/net income	38%
N. Calculate the gross profit margin for 2021.	Gross profit/ sales	47%
O. Calculate the operating profit margin for 2021.	Operating profit/ sales	10%
P. Calculate the net profit margin for 2021.	Net income/ sales	4%
Q. Calculate the return on assets (ROA) for 2021.	Net income / total assets	3%
R. Calculate the total asset turnover during 2021.	Sales/ total assets	0.70
S. Calculate the return on equity (ROE for 2021.	net income/ total equity	6%
T. Calculate the marginal tax rate for 2021.	Tax/ Net income	35%