Question

ch3-04 student_name.xis (h Print each completed worksheet in Vallle printed in the lower left footer and the file name in the lower right foote Chapter 3 Case Problem 2: WINE DEPOT You are to create Wine Depot's financial analyses as of December 31, 2020, and as of December 31, 2021. Following the Chapter 3 examples, use the stu- dent file ch3-05.xls to create a vertical and horizontal analysis of the balance sheet and income statement in the columns provided. (Note: The horizontal analysis will compare December 31, 2020 with December 31, 2021.) Also cre- ate a pie chart of expenses for the year ended December 31, 2021; a column chart of expenses for the years ended December 31, 2020, and December 31,

Answer 1

wine depot inc.

Income Statement

for the period ended

	december	2020	%sale	december	2021	%sale		%change
sales		$375000	100		425000	100		13.33
less: cost of goods sold		150000	40		191250	45		27.5
gross margin		225000	60		233750	55		3.88
expenses
advertising	$8000		2.133	$18250		4.29		128.125
marketing	6000		1.6	3000		.71		-50
deperication	9000		2.4	8000		1.88		-11.11
interest	3000		0.8	1500		.35		-50
salary	52000		13.87	85000		20		63.46
wages	18000		4.8	15000		3.53		16.667
supplies	1200		0.32	7000		1.65		483.333
utilities	2800	100000(26.67)	.75	8500	146250(34.41)	2		202.57
net income before taxes		125000	33.33		87500	20.59		-30
income tax		37500	10		26250	6.18		-30
net income		$87500	23.33		61250	14.41		-30

wine depot

balance sheet as of

assets	december	2020	%assets	december	2021	%assets	%change
current assets
cash	$5000		5.05	7000		5.833	40
short term investment	2000		2.02	3500		2.92	75
account receivable	6000		6.06	8500		7.08	41.67
inventory	6500		6.57	10500		8.75	61.54
prepaid expenses	700		0.71	500		.42	-28.57
suuplies	1800	22000	1.82	2300	32300	1.92	27.78
Equipments	85000			103700
Less deperication	8000	77000	77.78	16000	87700	73.08	13.9
Total		99000	100		120000	100	21.21
Liability and stock holders equilty
liabilities
current liabilities
accounts payable	$16000		16.16	$3000		2.5	-81.25
income tax payable	6000		6.06	1000		0.833	-83.33
notes payable - equipments	9000		9.09	-		0	-100
notes payable - others	18000		18.18	3000		2.5	-83.33
payroll liabilities	2000	51000	2.02	3750	10750	3.125	87.5
stockholders equilty
common stock	1000		1.01	1000		.83	0
retained earning	47000	48000	47.47	108250	109250	90.21	130.32
total		$99000	100		$120000	100	21.21

wine depot inc.

ratio analysis

december 2021

Profitability ratio

return on owners investment = net income/shareholders equity = 61250/109250 = 0.56

return on total investment = return before interest and tax / total assets = 89000/120000 = 0.74

profit margin = net income/net sale = 61250/425000 = 0.144

gross margin = gross profit/ net sale = 233750/425000 = 0.55

liquidity ratio

current ratio = current assets/current liability = 32300/10750 = 3.00465

quick ratio = current assets - stock / current liability = 19000/10750 = 1.77

average receivable = opening receivable + closing receivable / 2 = 6000+8500/2 = 7250

receivable turnover ratio = netsale/ average receivable = 425000/7250 = 58.62

inventory turnover= cogs/average stock = 191250/8500 = 22.5

average stock = opening stock + closing stock / 2 = 6500+10500/2 = 8500

solvency ratio

debt to equity = total liability / total share holder equity = 10750/109250 = 0.098

liability ratio = total liability / total assets = 10750/120000 = 0.0896