Question

Please show me how to do this analysis in excel

Income and Cash Flow Analysis

The Berndt Corporation expects to have sales of $12 million. Costs other than depreciation are expected to be 75% of sales, and depreciation is expected to be $1.5 million. All sales revenues will be collected in cash, and costs other than depreciation must be paid for during the year. Berndt’s federal-plus-state tax rate is 40%. Berndt has no debt.

1. Set up an income statement. What is Berndt’s expected net income? Its expected cash flow?
2. Suppose Congress changed the tax laws so that Berndt’s depreciation expenses doubled. No changes in operations occurred. What would happen to be reported profit and to net cash flow?
3. Now suppose that Congress changed the tax laws such that, instead of doubling Berndt’s depreciation, it was reduced by 50%. How would profit and net cash flow be affected?
4. If this were your company, would you prefer Congress to cause your depreciation expense to be doubled of halved? Why?

Answer 1

Requirement-a
Income statement
Particular		Amount $ in Million)
Sales		$12.00
Less:Costs (75% of sales)		$9.00
Depreciation		$1.50
Profit Before Tax		$1.50
Tax @ 40%		$0.60
PAT		$0.90
Cash Flow ( $0.9+1.5)		$2.40

Requirement-b
Income statement
Particular		Amount $ in Million)
Sales		$12.00
Less:Costs (75% of sales)		$9.00
Depreciation		$3.00
Profit Before Tax		$0.00
Tax @ 40%		$0.00
PAT		$0.00
Cash Flow		$3.00

Requirement-c
Income statement
Particular		Amount $ in Million)
Sales		$12.00
Less:Costs (75% of sales)		$9.00
Depreciation		$0.75
Profit Before Tax		$2.25
Tax @ 40%		$0.90
PAT		$1.35
Cash Flow (0.75+1.35))		$2.10

d. It recommended to follow strategy b, by double the depreciation because cash flow will be increase .