ACCT Assignment 5

MrJinx29

acct300_9-04.xlsx

Home >Business & Finance homework help >Accounting homework help >ACCT Assignment 5

Pr. 9-4

Problem 9-4

* represents an incorrect N answer =COUNTIF(A14:H27,"~*")

Name:

Section:

" " represents an unanswered N box - counts as an incorrect. =COUNTIF(A14:H27," ")

Score:

" " represents a correct blank answer or N answer =COUNTIF(A14:H27," ")

Key Code:

Total SUM(AV13:AV15)

Instructions

Answers are entered in the cells with gray backgrounds.

Percentage =AD6/AD8

Cells with non-gray backgrounds are protected and cannot be edited.

A red asterisk (*) will appear in the row immediately beneath an incorrect answer.

Notes:

" " represents an unanswered N box - counts as an incorrect.

" " represents a correct blank answer or N answer

Total number of answers = sum of above

Working capital:

Craig Pence: The amount of working capital will appear here automatically when the correct amounts are entered in the adjacent cells.

Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.

Calculated

Ratio

Numerator

Denominator

Value

Steps:

Open this sheet and macro sheet

Current ratio

Craig Pence: The calculated values will appear automatically when the correct amounts are entered in the adjacent cells.

Open old templated, then change color palet to this sheet's

Insert new header - change problem number and reformat

Quick ratio

Copy these formulas (column AD) to new sheet.

Update to new edition's names and numbers

Accounts receivable turnover

Copy new error check formulas. For N-boxes

=IF(sol.!$C$5="OFF","",IF(AE25=""," ",IF(AE25<>sol.!AE25,"*"," ")))

Number of days' sales in receivables

Inventory turnover

For B-Boxes

Number of days' sales in inventory

=IF(sol.!$C$5="OFF","",IF(AC29<>sol.!AC29,"*"," "))

Ratio of fixed assets to long-term liabilities

Copy Score formula from this template to new sheet.

=IF(sol.!$C$5="OFF","","Score:")

=IF(sol.!$C$5="OFF","",AD10)

Ratio of liabilities to stockholders' equity

10.

Number of times interest charges earned

11.

Number of times preferred dividends earned

12.

Ratio of net sales to assets

13.

Rate earned on total assets

14.

Rate earned on stockholders' equity

15.

Rate earned on common stockholders' equity

16.

Earnings per share on common stock

17.

Price-earnings ratio

18.

Dividends per share of common stock

19.

Dividend yield

sol.

Problem 9-4								* represents an incorrect N answer =COUNTIF(A14:H27,"~*")
Name:		SOLUTION						0
Section:								" " represents an unanswered N box - counts as an incorrect. =COUNTIF(A14:H27," ")
Score:		See student sheet for student's score						0
Scoring:		ON						" " represents a correct blank answer or N answer =COUNTIF(A14:H27," ")
								0
								Total SUM(AV13:AV15)
Instructions								0
Answers are entered in the cells with gray backgrounds.								Percentage =AD6/AD8
Cells with non-gray backgrounds are protected and cannot be edited.								ERROR:#DIV/0!
A red asterisk (*) will appear in the row immediately beneath an incorrect answer.								Notes:
								" " represents an unanswered N box - counts as an incorrect.
								" " represents a correct blank answer or N answer
								Total number of answers = sum of above
	1.	Working capital:	$965,000	-	$200,000	=	$765,000 Craig Pence: The amount of working capital will appear here automatically when the correct amounts are entered in the adjacent cells.	Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.
							Calculated
		Ratio	Numerator	÷	Denominator		Value	Steps:
								Open this sheet and macro sheet
	2.	Current ratio	$ 965,000	÷	$ 200,000	=	4.8 Craig Pence: The calculated values will appear automatically when the correct amounts are entered in the adjacent cells.	Open old templated, then change color palet to this sheet's
								Insert new header - change problem number and reformat
	3.	Quick ratio	$ 615,000	÷	$ 200,000	=	3.1	Copy these formulas (column AD) to new sheet.
								Update to new edition's names and numbers
	4.	Accounts receivable turnover	$ 1,925,000	÷	$ 175,000	=	11.0	Copy new error check formulas. For N-boxes
								=IF(sol.!$C$5="OFF","",IF(AE25=""," ",IF(AE25<>sol.!AE25,"*"," ")))
	5.	Number of days' sales in receivables	$ 175,000	÷	$ 5,274	=	33.2
	6.	Inventory turnover	$ 780,000	÷	$ 280,000	=	2.8
								For B-Boxes
	7.	Number of days' sales in inventory	$ 280,000	÷	$ 2,137	=	131.0	=IF(sol.!$C$5="OFF","",IF(AC29<>sol.!AC29,"*"," "))
	8.	Ratio of fixed assets to long-term liabilities	$ 1,135,000	÷	$ 1,250,000	=	0.9	Copy Score formula from this template to new sheet.
								=IF(sol.!$C$5="OFF","","Score:")
	9.	Ratio of liabilities to stockholders' equity	$ 1,450,000	÷	$ 1,050,000	=	1.4
									=IF(sol.!$C$5="OFF","",AD10)
	10.	Number of times interest charges earned	$ 570,000	÷	$ 115,000	=	5.0
	11.	Number of times preferred dividends earned	$ 364,000	÷	$ 5,000	=	72.8
	12.	Ratio of net sales to assets	$ 1,925,000	÷	$ 1,950,000	=	1.0
	13.	Rate earned on total assets	$ 479,000	÷	$ 2,200,000	=	21.8%
	14.	Rate earned on stockholders' equity	$ 364,000	÷	$ 890,500	=	40.9%
	15.	Rate earned on common stockholders' equity	$ 359,000	÷	$ 790,500	=	45.4%
	16.	Earnings per share on common stock	$ 359,000	÷	50,000	=	$7.18
	17.	Price-earnings ratio	$ 89.75	÷	$ 7.18	=	12.5
	18.	Dividends per share of common stock	$ 40,000	÷	50,000	=	$0.80
	19.	Dividend yield	$ 0.80	÷	$ 89.75	=	0.9%