You should use MS Excel for this assignment.
PROBLEM
Congratulations! You just finished a successful year (YEAR NUMBER 2) as Chief Financial Officer of "Body Fumigation Development" (BFD) and animal extermination facility. The CEO
just asked you for a quick, summarized copy of the financial statements. Of course you say, no problem boss, it will only take me a few minutes----NOT!! However, like a good employee, you tell him you will produce this summary for him as soon as possible.
a) Construct an Income Statement
Your current year figures are as follows:
Revenues, $100,000
Expenses, $45,000
Depreciation Expense, oops¿you still have to figure that out. Two pieces of equipment have been purchased--1
piece in year 1 for $70,000 that has a 5 year life with no salvage value, 1 piece in year 2 for $500,000 that has a 25 year life with no salvage value. You realize that your boss needs this info quickly, so you are just going to figure out the depreciation expense for the current year (year 2). You will use the straight line method. Your tax rate is estimated at 30%.
b) Construct a Balance Sheet
You have current balances as follows: Accounts Payable, $10,000; Accounts Receivable, $50,000; Cash, $20,000; Wages Payable, $30,000; And of course your equipment as stated in the preceding paragraph. Your prior year net income was $0 and prior year paid in capital was $83,000. You do have some stockholder¿s equity (hint: in this section you will have two line items-- 1) retained earnings, for which you need to figure out on your own; and 2) paid in capital¿current year paid in capital balance is $537,300)
c) Construct a Cash Flow
All prior year balance sheet items (asset and liability sections only) were 10% lower with the exception of equipment & depreciation, which you have already figured out from the prior sections. I am looking forward to this discussion since (I have heard rumored through the cyber grapevine) you are all so bright and intelligent!! :-)
You should use MS Excel for this assignment.
PROBLEM
Congratulations! You just finished a successful year (YEAR NUMBER 2) as Chief Financial Officer of "Body Fumigation Development" (BFD) and animal extermination facility. The CEO
just asked you for a quick, summarized copy of the financial statements. Of course you say, no problem boss, it will only take me a few minutes----NOT!! However, like a good employee, you tell him you will produce this summary for him as soon as possible.
a) Construct an Income Statement
Your current year figures are as follows:
Revenues, $100,000
Expenses, $45,000
Depreciation Expense, oops¿you still have to figure that out. Two pieces of equipment have been purchased--1
piece in year 1 for $70,000 that has a 5 year life with no salvage value, 1 piece in year 2 for $500,000 that has a 25 year life with no salvage value. You realize that your boss needs this info quickly, so you are just going to figure out the depreciation expense for the current year (year 2). You will use the straight line method. Your tax rate is estimated at 30%.
b) Construct a Balance Sheet
You have current balances as follows: Accounts Payable, $10,000; Accounts Receivable, $50,000; Cash, $20,000; Wages Payable, $30,000; And of course your equipment as stated in the preceding paragraph. Your prior year net income was $0 and prior year paid in capital was $83,000. You do have some stockholder¿s equity (hint: in this section you will have two line items-- 1) retained earnings, for which you need to figure out on your own; and 2) paid in capital¿current year paid in capital balance is $537,300)
c) Construct a Cash Flow
All prior year balance sheet items (asset and liability sections only) were 10% lower with the exception of equipment & depreciation, which you have already figured out from the prior sections. I am looking forward to this discussion since (I have heard rumored through the cyber grapevine) you are all so bright and intelligent!! :-)
Related questions
Management Accounting Application
In this assignment you will demonstrate your understanding ofcapital investment techniques by
evaluating the following three case studies.
Case Analysis 1:
You work for a small, local telecommunications company. In fiveyears, the company plans to undertake a major upgrade to itsservers and other IT infrastructure. Management estimates that itwill need up to
$450,000 to cover all related costs; however, as a fairly youngcompany, the goal is to pay for the
upgrade with cash and not to take out loans.
Right now, you have $300,000 in a bank account established forCapital Investments. This account pays
6% interest, compounded annually.
A member of the finance department has approached you with aninvestment opportunity for the
$300,000 that covers a five-year period and has the followingprojected after-tax cash flows:
Year Projected Cash Flow
1 $94,000
2 $114,000
3 $134,000
4 $114,000
5 $94,000
Based on this information, answer the following questions:
1) How much money will be in the bank account if you leave the$300,000 alone until you need it in five
years?
2) If you undertake the investment opportunity, what is theNominal Payback Period?
3) Using the factors for 6%, what is the Discounted PaybackPeriod?
4) What is the Present Value of the benefits from this 5-yearinvestment opportunity?
5) What is the Net Present Value of this investmentopportunity?
6) If you leave the money in the bank and earn 6% compoundedannually, will you have at least
$450,000 in 5 years to fund the server and IT upgrades? By howmuch will you be âoverâ or âshortâ of
what you need?
7) If you undertake the investment, will you have at least$450,000 in your checking account in 5 years?
By how much will you be âoverâ or âshortâ?
Case Analysis 2 :
The CEO of Dynamic Manufacturing was at a conference and talkedto a supplier about a new piece of
equipment for its production process that she believes willproduce ongoing cost savings. As the
Operations Manager, your CEO has asked for your perspective onwhether or not to purchase the
machinery.
After talking to the supplier and meeting with your Engineersand Financial Analysts, youâve gathered the
following pieces of data:
⢠Cost of Machine: $150,000
⢠Estimated Annual After Tax Savings: $65,000
⢠Estimated machinery life: 3 years (after which there will bezero value for the equipment and no
further cost savings)
⢠You seem to recall that Dynamicâs Finance organizationrecommends either a 10% or a 15%
discount rate for all Cost Savings Projects. You are fairly sureit is 10%.
You understand that you need to understand the projectfinancials to ensure that
this investment will be economically attractive to DynamicManufacturingâs shareholders.
Calculate the Nominal Payback, the Discounted Payback, the NetPresent Value and the IRR
assuming:
⢠Part A, BASE CASE: 3 year project life, flat annual savings,10% discount rate
⢠Part B. Saving Growth Scenario: BASE CASE but with 10%compounded annual savings growth
in years 2 & 3.
⢠Part C, Higher Discount Rate Scenario: 3 year project life,flat annual savings, 15% discount rate
⢠Part D, 5 Year Equipment Life:5 year project and savings life,flat annual savings, 10% discount
rate
Discussion â in a Word Document in paragraph form, respond tothe following:
1) From a Financial perspective, would you recommend thispurchase to Management? Which
scenario would you present and why?
2) In your opinion, which scenario is the most aggressive (i.eis based on the most aggressive
assumptions)? If you were to select this scenario as the basisfor your proposal, how would you
justify the more aggressive assumptions?
3) In SIMPLE English (as in talking to a non-Finance and non-MBAperson), explain why there was
a difference in outcome between Part A and Part B.
4) Beyond Financial measures, what other considerations wouldyou want to consider, before
making a recommendation to Management?
5) If you were the CEO, would you approve this proposal? Why orwhy not?
Case Analysis 3:
You are the General Manager at the Bicker, Slaughter and LynchLaw Firm. There is an opportunity to
buy out a small law firm that was just started by a young MBA/JDand you believe the firm can be grown
and become a lucrative part of your Firm.
With help from your Finance leader, you have estimated thefollowing benefit streams for this new
division:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | |
Before Tax Cash Flow FromOperations | $(149,000) | $- | $51,380 | $88,760 | $114,100 | $129,780 | $143,640 | $167,300 |
After Tax Net Income FromOperations | $(103,500) | $(50,500) | $36,700 | $63,400 | $81,500 | $92,700 | $102,600 | $119,500 |
After Tax Cash Flow FromOperations | $(85,600) | $15,000 | $48,600 | $72,200 | $95,550 | $101,300 | $125,200 | $140,200 |
You estimate that the purchase price for this firm would be$200,000 and that additional net working
capital would be needed in the amount of $60,000 in year 0, anadditional $20,000 in year 2 and then
$20,000 in year 5.
In addition to the purchase price, you would ask that yourAdvertising budget of $275,000 be increased by
an incremental one time amount of $50,000 in advertising in year0 to publicize the firmâs expansion.
Your Finance leader has indicated that the firm has access to acredit line and could borrow the funds at
a rate of 6%. He also mentions that when he runs Projecteconomics for Capital budgeting (such as a
new copier or a company car), he recommends a standard 10% ratediscount but the one other time they
looked at an acquisition of a smaller firm he used a 12% ratediscount.
At the end of 8 years, the plan will be to sell this division.The estimated terminal value (the sale and the
return of working capital) is conservatively estimated to be$300,000 of after tax cash flow help.
Calculate the N Nominal Payback, the Discounted Payback, the NetPresent Value and the IRR for this
potential acquisition.
Discussion â in a Word Document in paragraph form, respond tothe following:
1) From a Financial perspective, would you recommend thispurchase to Management? Why?
2) What are some of the non-financial elements that need to beconsidered for this proposal?
3) Assumptions in Project Economics can have a huge impact onthe result. Identify 3 financial
elements/assumptions in your analysis that would make thisproject not be financially attractive?
(E.g. Answer the question, what would have to be true for thisto be a bad investment?)
4) If you were the CEO would you approve this proposal? Why orwhy not?
Demonstrate your understanding of capital investment techniquesby evaluating the following three case studies. Pleaseinclude the excel spreadsheet or the math behind each solution.Thanks
Case Analysis 1:
You work for a small, local telecommunications company. In fiveyears, the company plans to undertake a major upgrade to itsservers and other IT infrastructure. Management estimates that itwill need up to
$450,000 to cover all related costs; however, as a fairly youngcompany, the goal is to pay for the
upgrade with cash and not to take out loans.
Right now, you have $300,000 in a bank account established forCapital Investments. This account pays
6% interest, compounded annually.
A member of the finance department has approached you with aninvestment opportunity for the
$300,000 that covers a five-year period and has the followingprojected after-tax cash flows:
Year Projected Cash Flow
1 $94,000
2 $114,000
3 $134,000
4 $114,000
5 $94,000
Based on this information, answer the following questions:
1) How much money will be in the bank account if you leave the$300,000 alone until you need it in five
years?
2) If you undertake the investment opportunity, what is theNominal Payback Period?
3) Using the factors for 6%, what is the Discounted PaybackPeriod?
4) What is the Present Value of the benefits from this 5-yearinvestment opportunity?
5) What is the Net Present Value of this investmentopportunity?
6) If you leave the money in the bank and earn 6% compoundedannually, will you have at least
$450,000 in 5 years to fund the server and IT upgrades? By howmuch will you be âoverâ or âshortâ of
what you need?
7) If you undertake the investment, will you have at least$450,000 in your checking account in 5 years?
By how much will you be âoverâ or âshortâ?
Case Analysis 2 :
The CEO of Dynamic Manufacturing was at a conference and talkedto a supplier about a new piece of
equipment for its production process that she believes willproduce ongoing cost savings. As the
Operations Manager, your CEO has asked for your perspective onwhether or not to purchase the
machinery.
After talking to the supplier and meeting with your Engineersand Financial Analysts, youâve gathered the
following pieces of data:
⢠Cost of Machine: $150,000
⢠Estimated Annual After Tax Savings: $65,000
⢠Estimated machinery life: 3 years (after which there will bezero value for the equipment and no
further cost savings)
⢠You seem to recall that Dynamicâs Finance organizationrecommends either a 10% or a 15%
discount rate for all Cost Savings Projects. You are fairly sureit is 10%.
You understand that you need to understand the projectfinancials to ensure that
this investment will be economically attractive to DynamicManufacturingâs shareholders.
Calculate the Nominal Payback, the Discounted Payback, the NetPresent Value and the IRR
assuming:
⢠Part A, BASE CASE: 3 year project life, flat annual savings,10% discount rate
⢠Part B. Saving Growth Scenario: BASE CASE but with 10%compounded annual savings growth
in years 2 & 3.
⢠Part C, Higher Discount Rate Scenario: 3 year project life,flat annual savings, 15% discount rate
⢠Part D, 5 Year Equipment Life:5 year project and savings life,flat annual savings, 10% discount
rate
Discussion â in a Word Document in paragraph form, respond tothe following:
1) From a Financial perspective, would you recommend thispurchase to Management? Which
scenario would you present and why?
2) In your opinion, which scenario is the most aggressive (i.eis based on the most aggressive
assumptions)? If you were to select this scenario as the basisfor your proposal, how would you
justify the more aggressive assumptions?
3) In SIMPLE English (as in talking to a non-Finance and non-MBAperson), explain why there was
a difference in outcome between Part A and Part B.
4) Beyond Financial measures, what other considerations wouldyou want to consider, before
making a recommendation to Management?
5) If you were the CEO, would you approve this proposal? Why orwhy not?
Case Analysis 3:
You are the General Manager at the Bicker, Slaughter and LynchLaw Firm. There is an opportunity to
buy out a small law firm that was just started by a young MBA/JDand you believe the firm can be grown
and become a lucrative part of your Firm.
With help from your Finance leader, you have estimated thefollowing benefit streams for this new
division:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | |
Before Tax Cash Flow FromOperations | $(149,000) | $- | $51,380 | $88,760 | $114,100 | $129,780 | $143,640 | $167,300 |
After Tax Net Income FromOperations | $(103,500) | $(50,500) | $36,700 | $63,400 | $81,500 | $92,700 | $102,600 | $119,500 |
After Tax Cash Flow FromOperations | $(85,600) | $15,000 | $48,600 | $72,200 | $95,550 | $101,300 | $125,200 | $140,200 |
You estimate that the purchase price for this firm would be$200,000 and that additional net working
capital would be needed in the amount of $60,000 in year 0, anadditional $20,000 in year 2 and then
$20,000 in year 5.
In addition to the purchase price, you would ask that yourAdvertising budget of $275,000 be increased by
an incremental one time amount of $50,000 in advertising in year0 to publicize the firmâs expansion.
Your Finance leader has indicated that the firm has access to acredit line and could borrow the funds at
a rate of 6%. He also mentions that when he runs Projecteconomics for Capital budgeting (such as a
new copier or a company car), he recommends a standard 10% ratediscount but the one other time they
looked at an acquisition of a smaller firm he used a 12% ratediscount.
At the end of 8 years, the plan will be to sell this division.The estimated terminal value (the sale and the
return of working capital) is conservatively estimated to be$300,000 of after tax cash flow help.
Calculate the N Nominal Payback, the Discounted Payback, the NetPresent Value and the IRR for this
potential acquisition.
Discussion â in a Word Document in paragraph form, respond tothe following:
1) From a Financial perspective, would you recommend thispurchase to Management? Why?
2) What are some of the non-financial elements that need to beconsidered for this proposal?
3) Assumptions in Project Economics can have a huge impact onthe result. Identify 3 financial
elements/assumptions in your analysis that would make thisproject not be financially attractive?
(E.g. Answer the question, what would have to be true for thisto be a bad investment?)
4) If you were the CEO would you approve this proposal? Why orwhy not?
