31 You are responsible for developing a model that analyzes the potential investment, providing valuations of...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
31 You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. 32 The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. 33 Please enter formula in the yellow boxes below to develop the model. 34 25 35 2021 2022 2023 2024 2025 2026 Hints 26 36 Income Statement 27 37 Finished Goods Expense. Shown as positive because it is an expense that will cease to exist 400,000 (60,000) 38 20 Depreciation 400,000 (60,000) (200,000) (10,000) 400,000 (60,000) (200,000) (10,000) 29 400,000 (60,000) (200,000) (10,000) 400,000 (60,000) (200,000) 39 Labor 10 (200,000) (10,000) 40 Overhead (10,000) 11 41 FRIT EBIT 130,000 130,000 130,000 130,000 130,000 12 42 Interest Income (5% interest rate) Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) 12 43 Protay Pretax 130,000 130,000 130,000 130,000 130,000 ^^ Tax 44 Tax (30% tax rate) 39,000 39,000 39,000 39,000 39,000 91,000 45 45 Net Income 91,000 91,000 91,000 91,000 46 16 47 Balance Sheet (change in account) 48 Cash (150,000) Cash is equal to that of the previous year plus Cash Generated. 49 PP&E 300,000 PP&E is equal to that of the previous year plus CapEx. (However, CapEx is shown as a negative on the Cash Flow Statement so remember to change the sign to make it a positive.) Debt is equal to that of the previous year plus Issues (Retirement) of Debt 50 50 Debt 150,000 51 Equity Equity is equal to that of the previous year plus Net Income (there are no Dividends) 52 52 53 Cash Flow Statement CA 54 Net Income Net Income from the Income Statement 55 Depreciation Investment Required spread of the useful life (5 years) 56 Cash Flow from Operations. 57 57 CapEx (300,000) 0 0 0 0 0 58 Cash Flow from Investing (300,000) 59 Issues (Retirement) Debt 150,000 Debt based on Percent Debt Financed with 1/5th retired each year. Must be a formula based on D50 enabling changed assumptions of percent debt financed. 60 Cash Flow from Financing 150,000 61 Cash Generated (150,000) 62 63 Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 61 using a 10% discount factor. 64 04 65 66 Change the 'Investment Required', cell D27, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. 67 68 69 Keeping the $200,000 'Investment Required' change the 'Percent Debt Financed' to 0% and enter the resulting NPV value (not the formula) in the yellow box below. 70 71 52 31 You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. 32 The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. 33 Please enter formula in the yellow boxes below to develop the model. 34 25 35 2021 2022 2023 2024 2025 2026 Hints 26 36 Income Statement 27 37 Finished Goods Expense. Shown as positive because it is an expense that will cease to exist 400,000 (60,000) 38 20 Depreciation 400,000 (60,000) (200,000) (10,000) 400,000 (60,000) (200,000) (10,000) 29 400,000 (60,000) (200,000) (10,000) 400,000 (60,000) (200,000) 39 Labor 10 (200,000) (10,000) 40 Overhead (10,000) 11 41 FRIT EBIT 130,000 130,000 130,000 130,000 130,000 12 42 Interest Income (5% interest rate) Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) 12 43 Protay Pretax 130,000 130,000 130,000 130,000 130,000 ^^ Tax 44 Tax (30% tax rate) 39,000 39,000 39,000 39,000 39,000 91,000 45 45 Net Income 91,000 91,000 91,000 91,000 46 16 47 Balance Sheet (change in account) 48 Cash (150,000) Cash is equal to that of the previous year plus Cash Generated. 49 PP&E 300,000 PP&E is equal to that of the previous year plus CapEx. (However, CapEx is shown as a negative on the Cash Flow Statement so remember to change the sign to make it a positive.) Debt is equal to that of the previous year plus Issues (Retirement) of Debt 50 50 Debt 150,000 51 Equity Equity is equal to that of the previous year plus Net Income (there are no Dividends) 52 52 53 Cash Flow Statement CA 54 Net Income Net Income from the Income Statement 55 Depreciation Investment Required spread of the useful life (5 years) 56 Cash Flow from Operations. 57 57 CapEx (300,000) 0 0 0 0 0 58 Cash Flow from Investing (300,000) 59 Issues (Retirement) Debt 150,000 Debt based on Percent Debt Financed with 1/5th retired each year. Must be a formula based on D50 enabling changed assumptions of percent debt financed. 60 Cash Flow from Financing 150,000 61 Cash Generated (150,000) 62 63 Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 61 using a 10% discount factor. 64 04 65 66 Change the 'Investment Required', cell D27, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. 67 68 69 Keeping the $200,000 'Investment Required' change the 'Percent Debt Financed' to 0% and enter the resulting NPV value (not the formula) in the yellow box below. 70 71 52
Expert Answer:
Answer rating: 100% (QA)
2021 2022 2023 2024 2025 2026 Income Statement Finished Goods Expense 500000 500000 500000 500000 50... View the full answer
Posted Date:
Students also viewed these accounting questions
-
As Beacon Company controller, you are responsible for informing the board of directors about its financial activities. At the board meeting, you present the following information. After the meeting,...
-
As Baldwin Company controller, you are responsible for informing the board of directors about its financial activities. At the board meeting, you present the following information. After the meeting,...
-
You work for Gregory Plumbing Supply. You are responsible for training a new accounting clerk. She has the following questions for you to answer about this invoice. 1. Who is the buyer? 2. Who is...
-
In 2017, Barlow moved from Chicago to Miami to start a new job, incurring costs of $1,200 to move household goods and $2,500 in temporary living expenses. Barlow was not reimbursed for any of these...
-
1. Describe the service organization culture at Amy's Ice Cream. 2. What are the personality attributes of the employees who are sought by Amy's Ice Cream? 3. Design a personnel selection procedure...
-
Design a process chart for printing a short document on a laser printer at an office. Unknown to you, the printer in the hallway is out of paper. The paper is located in a supply room at the other...
-
Split the Bill? When the time comes for a group of people eating together at a restaurant to pay their bill, sometimes they might agree to split the costs equally and other times will pay...
-
In Oregon, employers who are covered by the state workers' compensation law withhold employee contributions from the wages of covered employees at the rate of 2.8 for each hour or part of an hour...
-
Mando Enterprises is expected to pay a dividend of $2.50 a year from today. Annual dividends are expected to grow by 10% in year 2, by 7% in year 3, and thereafter by 4.50% per year. Stockholders...
-
Consider a household that possesses $ 160,000 worth of valuables such as jewelry. This household faces a 0.2 probability of burglary, in which case it loses $ 70,000 worth of the valuables. Suppose...
-
Non-discrimination is one of the two principles that guided the General Agreement on Trade and Tariffs and its successor, the World Trade Organization. This principle stems from a long standing legal...
-
What is the method measures the amount of time it will take for net cash flows of an investment to equal the cash outlay?
-
In financial management terms, what are things owed by a company referred ?
-
Seema is a member of her employer's extended health care plan, which has a $75. annual family deductible based on a calendar year and provides for the employee to pay a 20% co-insurance. If Seema's...
-
Solve the inequality. 04 7(x + 1) < 18
-
A Honey Corp sold another of its honey trucks for $8,000. They had accumulated $20,000 of depreciation on the truck and recognized a $1,000 gain when they sold it. How much did AA originally pay for...
-
Lab coats provide an additional layer of protection in the laboratory andhelp prevent insidious exposure to potential biological, chemical, and other contaminants. In choosing a basic lab coat, which...
-
Define the term utility software and give two examples.
-
A loan of $\$ 50,000$ is due 10 years from today. The borrower wants to make annual payments at the end of each year into a sinking fund that will earn interest at an annual rate of 10 percent. What...
-
The Dallas Development Corporation is considering the purchase of an apartment project for $\$ 100,000$. They estimate that they will receive $\$ 15,000$ at the end of each year for the next 10...
-
Consider an investment that will pay $\$ 680$ per month for the next 15 years and will be worth $\$ 28,000$ at the end of that time. How much is this investment worth to you today at a $5.25 \%$...
Study smarter with the SolutionInn App