Finance

CaseStudy1-ExcelFile.xlsx

Home >Business & Finance homework help >Finance

C02

Chapter 2 Spreadsheet Problem Solutions (C02)	Cary	Industry
ROA	5.90%	9.10%
Ratio Analysis	TD/TA	54.81%	50.00%
PM	2.53%	3.50%
1. There are a number of instructions with which you should be familiar	M/B	65.33%	n.a.
to use these computerized models. These instructions appear in a
separate worksheet labeled INSTRUCTIONS. If you have not already
done so, you should read these instructions now. To read these	Quick	0.8	1.0
instructions, click on theworksheet labeled INSTRUCTIONS.	Current	2.3	2.7
T.A.turn.	2.3	2.6
2. We have entered the some data for you. All that is required of	P/E ratio	5.0	6.0
you is to enter the new dat and analyze the resulting ratios.	DSO	36.8	32.0
Stock Price	23.6	n.a.
3. A graph that shows selected ratios for Cary vs. the industry	EPS	$4.71	n.a.
average will be displayed if you click the worsheet labeled GRAPH
at the bottom of this spreadsheet. To return to this worksheet,
click on the worksheet labeled C02 at the bottom of the GRAPH worksheet.
4. First, page down and look over the model to see what's there. Then,
begin by putting Row 23 of the worksheet at the top of the screen.
Now, enter the appropriate data.
INPUT DATA:	KEY OUTPUT:
Cary	Industry
Balance Sheet
Cash	$ 72,000	Quick	0.85	1.00
A/R	439,000	Current	2.33	2.70
Inventories	894,000	Inv. turn.	4.00	5.80
Total current assets	1,405,000	DSO	36.84	32.00
Land and bldg	238,000	FA turnover	9.95	13.00
Machinery	132,000	TA turnover	2.34	2.60
Other F.A.	61,000	ROA	5.90%	9.10%
Total assets	$ 1,836,000	ROE	13.07%	18.20%
TD/TA	54.81%	50.00%
Accts & Notes Pay.	$ 432,000	PM	2.53%	3.50%
Accruals	170,000	EPS	$4.71	n.a.
Total current liabilities	602,000	Stock Price	$23.57	n.a.	792,000
Long-term debt	404,290	P/E ratio	5.0	6.0	792,000
Common stock	575,000	M/B	0.65	n.a.	-
Retained earnings (RE)	254,710
Total liab. & equity	$ 1,836,000	Tax rate	40%
-
RE last year	146,302	-
Income Statement
Sales	$ 4,290,000
Cost of G.S.	3,580,000
Gross operating profit	710,000
Adm. & sales exp.	236,320
Depreciation	159,000
Misc. expenses	134,000
Earnings before taxes	180,680
Taxes (40%)	72,272
Net income	$ 108,408
P/E ratio	5.0
No. of shares	23,000
Cash dividend	$ 0.95
© 2017 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

GRAPH

CFIN5

Cary Quick Current Inv. turn. DSO FA turnover TA turnover 0.84883720930232553 2.3338870431893688 4.0044742729306488 36.83916083916084 9.9535962877030162 2.3366013071895426 Industry Quick Current Inv. turn. DSO FA turnover TA turnover 1 2.7 5.8 32 13 2.6

Cary ROA ROE TD/TA PM 5.9045751633986926E-2 0.13065769967820082 0.54808823529411765 2.5269930069930072E-2 Industry ROA ROE TD/TA PM 9.0999999999999998E-2 0.182 0.5 3.5000000000000003E-2

INSTRUCTIONS

GENERAL INSTRUCTIONS FOR COMPUTERIZED PROBLEM SOLUTIONS

We have already entered the base case data for each model in this

file, and the models have performed the analysis for preceding parts

of the problem. You will need to enter the data for each of the

remaining parts of the problem--we indicate in each problem the parts

that should be done using the spreadsheet. However, there are several

points worth noting before you go into a model:

1. The input data are entered in specified cells in the INPUT DATA

section. When you change an input item, the model automatically

recalculates the values of appropriate output data items, unless

you are told otherwise. If the values do not change automatically,

press the F9 key to recompute the values.

2. The key output data are displayed to the right of the INPUT DATA

section or immediately below it. This placement permits you to

change an input and instantly see how that change affects the output

of the model. This is extremely useful in sensitivity analysis.

3. Input data items that you can change are distinguished from the

ones you should not change. The items that you can change are

highlighted in color (blue) whereas the other items are printed in black.

4. All percentages must be entered as decimals. Dollars and other

numbers must be entered without dollar signs or commas.

5. Instructions and comments concerning specific models accompany

each model. Graphs associated with the model are included in

another worksheet that can be accessed by clicking on the

worksheet labeled GRAPH at the bottom of the spreadsheet.

CFIN5

Chapter 2 Spreadsheet Problem Solutions (C02)

1. There are a number of instructions with which you should be familiar

to use these computerized models. These instructions appear in a

separate worksheet labeled INSTRUCTIONS. If you have not already

done so, you should read these instructions now. To read these

instructions, click on theworksheet labeled INSTRUCTIONS.

2. We have entered the some data for you. All that is required of

Ratio Analysis