spreadsheet

 

I have the answers just not in the right format

 

open the “Build a Model” Excel spreadsheet for that problem and save it to your computer with your own name included in the file name. This spreadsheet contains an Excel model that has been customized for this specific problem and contains instructions, a data section, partially filled in formulas, hints, and notes. Versions of these spreadsheets that were not downloaded from Weekly Materials will not be accepted for grading.

3.     
Solve the problems or answer the questions by inserting your answers in each of the highlighted worksheet cells. Your answer must consist entirely of Excel formulas or equations, cell references, or Excel functions. No purely numeric answers will be accepted for credit.

 

4.     
Save your “Build a Model” spreadsheet when it has been completed.

Ch02 P14 Build a Model

,000; operating costs (excluding depreciation) were equal to 85% of sales; net fixed assets were

,000; depreciation amounted to 10% of net fixed assets; interest expenses were

,000; the state-plus-federal corporate tax rate was 40% and Cumberland paid

of its net income out in dividends. Given this information, construct Cumberland’s income statement. Also calculate total dividends and the addition to retained earnings.

Revenue

$455,000

$67,000

$8,550

25%

2010

Sales

DA

EBIT
Interest expense

,000 of new common stock in the most recent year. Using this information and the results from part a, fill in the missing values for common stock, retained earnings, total common equity, and total liabilities and equity.

$10,000

(in thousands of dollars)
2010

67,000

$252,670

(in thousands of dollars)

Mike Ehrhardt: An increase in debt is a positive cash flow.

$91,450

Spring 1, 2013
	7/22/12
Chapter 2. Ch 02 P14 Build a Model
Except for charts and answers that must be written, only Excel formulas that use cell references or functions will be accepted for credit.
Numeric answers in cells will not be accepted.
a. Cumberland Industries’ most recent sales were	$455,000	$	67,000	$8,550	25%
The input information required for the problem is outlined in the “Key Input Data” section below. Using this data and the balance sheet above, we constructed the income statement shown below.
Key Input Data for Cumberland Industries			2010
(Thousands of dollars)
	Sales
Expenses (excluding depreciation) as a percent of sales	85.0%
Net fixed assest
Depr. as a % of net fixed assets	10.0%
Tax rate	40.0%
	Interest expense
Dividend Payout Ratio
Cumberland Industries: Income Statement (Thousands of dollars)
Operating costs excluding depreciation
	EBIT
Depreciation (Cumberland has no amortization charges)
EBT
Taxes (40%)
Net income
Common dividends
Addition to retained earnings
b. Cumberland Industries’ partial balance sheets are shown below. Cumberland issued	$10,000
Dollar value of common stock issued		(in thousands of dollars)
Cumberland Industries December 31 Balance Sheets
2009
Assets
Cash and cash equivalents		$91,450	$74,625
Short-term investments	11,400	15,100
Accounts Receivable	108,470	85,527
Inventories	38,450	34,982
Total current assets	$249,770	$210,234
Net fixed assets	42,436
Total assets	$316,770		$252,670
Liabilities and equity
Accounts payable	$30,761	$23,109
Accruals	30,405	22,656
Notes payable	12,717	14,217
Total current liabilities	$73,883	$59,982
Long-term debt	80,263	63,914
Total liabilities	$154,146	$123,896
Common stock	$90,000
Retained earnings	38,774
Total common equity	$128,774
Total liabilities and equity
Check for balancing (this should be zero):
c. Construct the statement of cash flows for the most recent year.
Statement of Cash Flows
Operating Activities
Net Income
Adjustments:
Noncash adjustment:
Depreciation
Due to changes in working capital:
Due to change in accounts receivable
Kenneth D. Jackson: An increase in accounts receivable from the pevious year to the current year reduces the net cash provided by operating activities	Due to change in inventories
Kenneth D. Jackson: An increase in Inventory from the previous year to the current year reduces the net cash provided by operation activities	Due to change in accounts payable
Mike Ehrhardt: An increase in accounts payable increases cash flow.	Due to change in accruals
Mike Ehrhardt: An increase in accruals is a positive cash flow.	Net cash provided (used) by operating activities
Investing Activities
Cash used to acquire gross fixed assets
Christopher Buzzard: Remember, to calculate cash used to acquire fixed assets, we must include depreciation, i.e., assets purchased are equal to the increase in net assets plus depreciation.	Due to change in short-term investments
Mike Ehrhardt: Selling securities is a positive cash flow, buying securities is a negative cash flow.	Net cash provided (used) by investing activities
Financing Activities
Due to change in long-term debt
	Mike Ehrhardt: An increase in debt is a positive cash flow.	Due to change in notes payable
Due to change in common stock
Mike Ehrhardt: An increase in common stock is a positive cash flow.	Payment of common dividends
Net cash provided (used) by financing activities
Net increase/decrease in cash
Add: Cash balance at the beginning of the year
Cash balance at the end of the year
Check: cash balance in statement of cash flows should equal the cash on balance sheets as shown here:

Sheet2

7/22/12

Ch03 P15 Build a Model

$379,659 $327,240

(Thousands of Dollars)

2010 2009

DA

EBIT

Michael C. Ehrhardt: Due to rounding, the numbers calculated in the Chapter 2 problem may differ slightly from these.

$17,262

2010 2009

4,000

$20,000 $20,000

$0

2010 2009

coverage ratio

NA

NA

2010

2009

Assets 2010 2009
Cash and cash equivalents
Short-term investments
Accounts Receivable
Inventories
Total current assets
Net fixed assets
Total assets
Liabilities and equity 2010 2009
Accounts payable
Accruals
Notes payable
Total current liabilities
Long-term debt
Total liabilities
Common stock
Retained Earnings
Total common equity
Total liabilities and equity

2010 2009

Sales
Expenses excluding depr. and amort.
EBITDA
Depreciation and Amortization
EBIT
Interest Expense
EBT
Taxes (40%)

Net Income

Assets 2010 2009
Cash and cash equivalents
Short-term investments
Accounts Receivable
Inventories
Total current assets
Net fixed assets
Total assets

Base

Liabilities and equity 2010 2009
Accounts payable
Accruals
Notes payable
Total current liabilities
Long-term debt
Total liabilities
Common stock
Retained Earnings
Total common equity
Total liabilities and equity

Base

2010 2009

Sales
Expenses excluding depr. and amort.
EBITDA
Depreciation and Amortization
EBIT
Interest Expense
EBT
Taxes (40%)
Net Income

																																																																																																																																																																																																Spring 1, 2013
	7/22/12
Chapter 3. Ch 03 P15 Build a Model
Except for charts and answers that must be written, only Excel formulas that use cell references or functions will be accepted for credit.
Numeric answers in cells will not be accepted.
Joshua & White Technologies: December 31 Balance Sheets
	(Thousands of Dollars)
		Assets											2010
										2009
		Cash and cash equivalents	$21,000			$20,000
		Short-term investments	3,759	3,240
		Accounts Receivable	52,500	48,000
		Inventories	8	4,000	56,000
		Total current assets	$161,259	$127,240
		Net fixed assets	218,400	200,000
		Total assets		$379,659		$327,240
		Liabilities and equity
		Accounts payable	$33,600	$32,000
		Accruals	12,600	12,000
		Notes payable	19,929	6,480
		Total current liabilities	$66,129	$50,480
		Long-term debt	67,662	58,320
		Total liabilities	$133,791	$108,800
		Common stock	183,793	178,440
		Retained Earnings	62,075	40,000
		Total common equity	$245,868	$218,440
		Total liabilities and equity
Joshua & White Technologies December 31 Income Statements
		Sales	$420,000	$400,000
		Expenses excluding depr. and amort.	327,600	320,000
			EBIT	$92,400	$80,000
		Depreciation and Amortization	19,660	18,000
$72,740	$62,000
		Interest Expense	5,740	4,460
		EBT	$67,000	$57,540
		Taxes (40%)	26,800	23,016
	Net Income
$40,200	$34,524
Common dividends	$18,125		$17,262
Addition to retained earnings	$22,075
Other Data
Year-end Stock Price	$100.00	$96.00
# of shares (Thousands)	4,052
Lease payment (Thousands of Dollars)
Sinking fund payment (Thousands of Dollars)		$0
Ratio Analysis	Industry Avg
Liquidity Ratios
Current Ratio	2.58
Quick Ratio	1.53
Asset Management Ratios
Inventory Turnover	7.69
Days Sales Outstanding	47.45
Fixed Assets Turnover	2.04
Total Assets Turnover	1.23
Debt Management Ratios
Debt Ratio	32.1%
Times-interest-earned ratio	15.33
		EBITDA	4.18
Profitability Ratios
Profit Margin	8.86%
Basic Earning Power	19.48%
Return on Assets	10.93%
Return on Equity	16.10%
Market Value Ratios
Earnings per share			NA
Price-to-earnings ratio	10.65
Cash flow per share
Price-to-cash flow ratio	7.11
Book Value per share
Market-to-book ratio	1.72
a. Has Joshua & White’s liquidity position improved or worsened? Explain.
b. Has Joshua & White’s ability to manage its assets improved or worsened? Explain.
c. How has Joshua & White’s profitability changed during the last year?
d. Perform an extended Du Pont analysis for Joshua & White for 2008 and 2009. What did you find?
ROE =	PM x	TA Turnover x Equity Multiplier
e. Perform a common size analysis. What has happened to the composition
(that is, percentage in each category) of assets and liabilities?
Common Size Balance Sheets
Common Size Income Statements
f. Perform a percent change analysis. What does this tell you about the change in profitability
and asset utilization?
Percent Change Balance Sheets			Base
Percent Change Income Statements

Sheet2

7/22/12