someone to solve this accounting problems in excel

Yusef_Miller-Nobles_7e-13e-Using_Excel_ChF15-ChM2_Start2.xlsx

Home >Business & Finance homework help >Accounting homework help >someone to solve this accounting problems in excel

Instructions

Financial Statement Analysis
Using Excel for financial analysis
Riverside Sweets, a retail candy store chain, reported the following figures:
		RIVERSIDE SWEETS
		Balance Sheet
		June 30, 2018 and 2019
			2019	2018
		Assets
		Current Assets:
		Cash	$ 125,000	$ 119,000
		Short-term Investments	685,000	650,000
		Accounts Receivable	225,000	198,000
		Merchandise Inventory	65,000	70,000
		Other Current Assets	195,000	191,000
		Total Current Assets	1,295,000	1,228,000
		Property, Plant, and Equipment	875,000	832,000
		Total Assets	$ 2,170,000	$ 2,060,000
		Liabilities
		Current Liabilities:
		Accounts Payable	$ 265,000	$ 251,750
		Accrued Liabilities	641,000	725,523
		Total Current Liabilities	906,000	977,273
		Long-term Liabilities
		Bonds Payable	250,000	150,000
		Mortgage Payable	150,000	175,000
		Total Long-term Liabilities	400,000	325,000
		Total Liabilities	1,306,000	1,302,273
		Stockholders' Equity
		Common Stock, $1 par, 225,000 shares issued and outstanding	225,000	225,000
		Paid-In Capital in Excess of Par	58,000	58,000
		Retained Earnings	581,000	474,727
		Total Stockholders' Equity	864,000	757,727
		Total Liabilities and Stockholders' Equity	$ 2,170,000	$ 2,060,000
		RIVERSIDE SWEETS
		Income Statement
		Year Ended June 30, 2019
		Net Sales	$ 2,800,000
		Cost of Goods Sold	1,551,600
		Gross Profit	1,248,400
		Operating Expenses	450,540
		Operating Income	797,860
		Other Income and (Expenses)
		Interest Expense	15,000
		Income Before Income Taxes	782,860
		Income Tax Expense	153,529
		Net Income	$ 629,331
Additional financial information:
	a.	75% of net sales are on account.
	b.	Market price of stock is $36 per share on June 30, 2019.
	c.	Annual dividend for 2019 was $1.50 per share.
	d.	All short-term investments are cash equivalents.
Use the blue shaded areas on the ENTERANSWERS tab for inputs.
ALWAYS use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.
Requirements				Possible Points
1	Perform a horizontal analysis on the balance sheet for 2018 and 2019.			40
2	Perform a vertical analysis on the income statement.			9
3	Compute the following ratios. Do not round your calculations.			26
	a.	Working Capital
	b.	Current Ratio
	c.	Acid-Test (Quick) Ratio
	d.	Cash Ratio
	e.	Accounts Receivable Turnover
	f.	Days’ Sales in Receivables
	g.	Inventory Turnover
	h.	Days’ Sales in Inventory
	i.	Gross Profit Percentage
	j.	Debt Ratio
	k.	Debt to Equity Ratio
	l.	Times-Interest-Earned Ratio
	m.	Profit Margin Ratio
	n.	Rate of Return on Total Assets
	o.	Asset Turnover Ratio
	p.	Rate of Return on Common Stockholders’ Equity
	q.	Earnings per Share (EPS)
	r.	Price/Earnings Ratio
	s.	Dividend Yield
	t.	Dividend Payout
Excel Skills
1	Basic cell formulas
Saving & Submitting Solution
1	Save file to desktop.
	a.	Create folder on desktop, and label COMPLETED EXCEL PROJECTS
2	Upload and submit your file to be graded.
	a.	Navigate back to the activity window - screen where you downloaded the initial spreadsheet
	b.	Click Choose button under step 3; locate the file you just saved and click Open
	c.	Click Upload button under step 3
	d.	Click Submit button under step 4
Viewing Results
1	Click on Results tab in MyAccountingLab
2	Click on the Assignment you were working on
3	Click on Project link; this will bring up your Score Card
4	Within Score Card window, click on Live Comments Report (lower right) to download spreadsheet with feedback

ENTERANSWERS1

Perform a horizontal analysis on the balance sheets for 2018 and 2019.
(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.)
	RIVERSIDE SWEETS
	Balance Sheet
	June 30, 2018 and 2019
				Increase (Decrease)
		2019	2018	Amount	Percentage
	Assets
	Current Assets:
	Cash	$ 125,000	$ 119,000
	Short-term Investments	685,000	650,000
	Accounts Receivable	225,000	198,000
	Merchandise Inventory	65,000	70,000
	Other Current Assets	195,000	191,000
	Total Current Assets	1,295,000	1,228,000
	Property, Plant, and Equipment	875,000	832,000
	Total Assets	$ 2,170,000	$ 2,060,000
	Liabilities
	Current Liabilities:
	Accounts Payable	$ 265,000	$ 251,750
	Accrued Liabilities	641,000	725,523
	Total Current Liabilities	906,000	977,273
	Long-term Liabilities
	Bonds Payable	250,000	150,000
	Mortgage Payable	150,000	175,000
	Total Long-term Liabilities	400,000	325,000
	Total Liabilities	1,306,000	1,302,273
	Stockholders' Equity
	Common Stock, $1 par, 225,000 shares issued and outstanding	225,000	225,000
	Paid-In Capital in Excess of Par	58,000	58,000
	Retained Earnings	581,000	474,727
	Total Stockholders' Equity	864,000	757,727
	Total Liabilities and Stockholders' Equity	$ 2,170,000	$ 2,060,000
HINTS
1. For cell references, begin the formula with an equals sign (=), use the balance in this worksheet for your calculations, and press the enter key.

ENTERANSWERS2

Perform a vertical analysis on the income statement.
(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.)
	RIVERSIDE SWEETS
	Income Statement
	Year Ended June 30, 2019
	Net Sales	$ 2,800,000
	Cost of Goods Sold	1,551,600
	Gross Profit	1,248,400
	Operating Expenses	450,540
	Operating Income	797,860
	Other Income and (Expenses)
	Interest Expense	15,000
	Income Before Income Taxes	782,860
	Income Tax Expense	153,529
	Net Income	$ 629,331
HINTS
1. For cell references, begin the formula with an equals sign (=), use the income statement in this worksheet for your calculations, and press the enter key.

ENTERANSWERS3

Compute the following ratios. Do not round your calculations.
(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. All values should be added as positive numbers.)
		2019	2018
	Working Capital
	Current Ratio
	Acid-Test (Quick) Ratio
	Cash Ratio
	Accounts Receivable Turnover
	Days’ Sales in Receivables
	Inventory Turnover
	Days’ Sales in Inventory
	Gross Profit Percentage
	Debt Ratio
	Debt to Equity Ratio
	Times-Interest-Earned Ratio
	Profit Margin Ratio
	Rate of Return on Total Assets
	Asset Turnover Ratio
	Rate of Return on Common Stockholders’ Equity
	Earnings per Share (EPS)
	Price/Earnings Ratio
	Dividend Yield
	Dividend Payout
HINTS
Cell \| Hint:
C5 \| Begin the formula with an equals sign (=), use the balance in the horizontal analysis worksheet for your calculations, and press the enter key.
С7 \| Use the function =SUM( ) to calculate the numerator.
C9 \| =(0.75*'Vertical Analysis'!C8)/(('Horizontal Analysis'!C14+'Horizontal Analysis'!D14)/2)
C10 \| Assume 365 days in a year and use the correct ratio in this worksheet for your calculations.