Question 1 - TVOM Calculations
Ling is considering to open an annuity to save for a down payment on a home. The annuity would be created with an initial deposit of $1,000 (end of year 0). At the end of each of the following ten years, a payment of $4,000 would be made into the annuity. The interest rate is 2.5% compounded annually. Submit the following in a spreadsheet.
a) Compute the balance at the end of 10 years by tabulating the deposits, interest and balance for each year in a spreadsheet. That is, create a spreadsheet with three columns containing 1) the annual deposit, 2) interest earned for the preceding year, and 3) the balance for each year. Use a year 0 for the initial deposit which would be in the balance column. Use equations which refer to cell labels. The balance in the last row should be the future value of this annuity at the end of ten years.
b) Use the Excel FV function to calculate the future value in ten years for this situation. You should get the same answer as the tabulation of part a.
c) Use the Excel PMT function to determine the payment required each year (instead of $4000) to achieve $50,000 in ten years (same initial deposit, interest rate and years).
d) Use the Excel NPER function to determine how many years it will take Ling to achieve $50,000 but using a rate of 2.0% annually, using the initial deposit and payment as initially stated. Show two decimal places.
e) Use the Excel RATE function to determine the interest rate Ling would need to achieve $50,000 using the same number of periods, initial deposit and payment as initially stated.
f) Use the Excel PV function to determine the upfront deposit that Ling would need to achieve $50,000 using the same number of periods, interest rate and payment as initially stated.
Question 2 - Rate Calculations
This assignment has three parts. Submit them in a spreadsheet.
Part 1: Create a spreadsheet table that shows for a particular Compounding Frequency and Annual Percentage Rates (APRs) for 12% to 1%, the corresponding Periodic Interest Rate (PIR) and Effective Annual Rate (EAR). Use the format as shown below. A manual change in the Compounding Frequency in the box above the table should cause the PIR and EAR columns in the table to automatically change for all of the APRs of 12% to 1%.
Part 2: Create a second spreadsheet table that shows for a particular APR and the Compounding Frequencies shown in the table below, the PIR and EAR. Use the format as shown below. A change in the APR in the box above the table should automatically change the PIR and EAR values in the table below it.
Part 3: Create a third spreadsheet table that shows for a particular EAR and the Compounding Frequencies in the table, the corresponding PIRs and APRs. Use the format as shown below. A change in the EAR in the box above the table should automatically change the PIR and APR values in the table below it.
Question 3 - Bonds and Stocks
An investment fund is considering two long term investments. Which investment offers the best rate of return assuming equal risks and a 10 year investment?
Bond: Coupon bond with a face value of $100,000 that can be purchased today for $70,000 that matures in 10 years. Its annual coupon rate is 8% paid semi-annually.
Stock: A stock whose shares can be purchased for $84 per share today and its price is forecast to grow 12% annually for the next 10 years. It will pay dividends of $1.50 semi-annually. Note that the future price of the stock can be calculated using the annual growth rate and the FV function.
Briefly discuss your recommendation. Submit using a spreadsheet.
Question 4 - Income Statement
The CEO of Smartphone Apps, LLC is preparing a loan application. Using the data below (only), prepare an Income Statement. Within this Income statement, include totals for Cost of Goods Sold, Gross Margin, Sales General, and Administrative, Earnings before Interest and Taxes, Pretax Income, and Net Income. Also compute the Profit Margin on Sales. Submit using a spreadsheet.
|
Net Revenue
|
$1,458,000
|
|
|
Production Labor Costs
|
$385,444
|
|
|
Material Costs
|
$247,500
|
|
|
Marketing
|
$127,000
|
|
|
Depreciation
|
$58,122
|
|
|
Research and Development
|
$98,000
|
|
|
Administrative
|
$258,300
|
|
|
Interest Expense
|
$21,608
|
|
|
Taxes
|
25%
|
of pretax Income
|
Question 5 - Evaluation Criteria using IRR
Slot Precision Services, Inc. has five alternative broaching machines/processes that are being studied. Only one will be chosen and the data is shown below. Recommend the choice for management that uses the internal rate of return criterion. Compare this with the decision with that of a Present Worth criterion. Submit your solution in a spreadsheet.
|
MARR
|
15%
|
EAR
|
|
Time Horizon
|
5
|
years
|
|
Alternatives
|
Investment in year 0
|
Annual Cash Flow
|
Salvage value in last year
|
|
1
|
$50,000
|
$18,000
|
$0
|
|
2
|
$250,000
|
$85,000
|
$75,000
|
|
3
|
$350,000
|
$105,000
|
$125,000
|
|
4
|
$600,000
|
$150,000
|
$400,000
|
|
5
|
$800,000
|
$165,000
|
$600,000
|
Question 6 - Income Statement with Depreciation
General Phone Apps (GPA) is evaluating a proposal to internally develop a software capability that is intended to enhance their application (app) development process by automating testing and simplifying product conversion among different operating systems. Since it will be cloud based, it also will facilitate group development projects and enable employees to more easily work from different locations. This is not a product to be sold, but rather it will assist internal development of their app software, so it is depreciable.
The development and conversion process is estimated to take one year in year 0) and cost $1,000,000. This investment includes all programming training, loading of existing products and testing the resulting conversion. An internal project over the past year has been completed that evaluated the feasibility and created a macro design of the proposed system (sunk costs).
The new software is expected to both increase sales and decrease development costs. The sales for the present year (year 0) are $2.,000,000 and without this new software capability would likely grow 10% annually. The new software is forecast to enable a sales growth of 33.33% per year, instead of only 10%. The annual cost of fulfilling orders and customer support (COGS) is forecast at 50% of revenue and expected to continue at this level.
The change in Marketing and Sales expense related to this project would be an increase of $75,000 annually and unchanged over the projects time horizon. The annual cost of the cloud service will be $150,000 in year 1 and increase 10% annually after that.
A three year time horizon is to be used for the evaluation, although the software is expected to be used much longer. The GPA tax rate is 25%.. Three-year MACRS depreciation has been chosen for the projects $1 million development and implementation cost.
Submit a spreadsheet containing an Income Statement for this proposal. Use the standard Income statement format that includes totals for COGS, SG&A, EBIT and Net Earnings.
No recommended decision is expected in this assignment as this requires a proposal cash flow statement that is next week's topic.
Question 7 - Cash Flow Statement
The manufacturing manager for Modern Manufacturing Company is working on a justification for implementing a "Lean/Just-in-time" manufacturing system. No upfront investment will be needed. No revenue changes are forecasted.
A team of employees will spend their time training employees and making process changes. The salaries and benefits of the "Just-in-time" staff are shown below for the three years of the project. There will not be any change in other S.G.&A. expenses.
Financial gains are expected to be a reduction in the following areas: cost of good sold, inventory, and accounts payable. The data is shown below where each year changes from the previous year by the percentages shown.
Determine the present worth of the project to see if the savings justify the costs.
|
Data Block
|
|
|
|
|
|
a
|
Time Span
|
3
|
Years
|
|
|
| |
Year
|
0
|
1
|
2
|
3
|
| |
JIT Team costs
|
|
$300,000
|
$200,000
|
$100,000
|
| |
COGS-Reduction per year
|
7.5%
|
annually
|
|
|
| |
COGS at end of year 0
|
$3,000,000
|
|
|
|
| |
Inventory Reduction per year
|
10%
|
annually
|
|
|
| |
Inventory at end of year 0
|
$200,000
|
|
|
|
| |
Accounts Receivable reduction
|
0%
|
annually
|
|
|
| |
Accounts Receivable at end of year 0
|
$150,000
|
|
|
|
| |
Accounts Payable & reduction
|
10%
|
annually
|
|
|
| |
Accounts payable at end of year 0
|
$100,000
|
|
|
|
| |
Tax Rate
|
25%
|
annually
|
|
|
| |
Interest Expense annually
|
$250,000
|
constant every year
|
|
| |
MARR
|
15%
|
|
|
|
Question 8 - Financial Modeling
This is an extension of the case used in assignment 8, but it differs in the details. Use this version only for Assignment 10. The numbers are in the attached spreadsheet.
General Phone Apps (GPA) is evaluating a proposal to internally develop a software capability that is intended to enhance their application (app) development process by automating testing and simplifying product conversion among different operating systems. Since it will be cloud based, it also will facilitate group development projects and enable employees to more easily work from different locations. This is not a product to be sold, but rather it will assist internal development of their app software, so it is depreciable.
The development and conversion process is estimated to take one year in year 0. This investment includes all programming training, loading of existing products and testing the resulting conversion. An internal project over the past year has been completed that evaluated the feasibility and created a macro design of the proposed system (sunk costs).
The new software is expected to both increase sales and decrease development costs. The sales for the present year (year 0) are obviously the same for both with and without this new software capability. The expected annual growth percentages for with and without are shown in the attached spreadsheet.
The annual cost of fulfilling orders (COGS) is the same for all years and is the same both with and without the proposal.
Marketing expense can be stated separately for each year. The annual cost of the cloud service has a starting value and an annual percentage growth rate.
Three-year MACRS depreciation has been chosen.
A buyer has been found for the existing servers that will no longer be needed and it will be received in year 1. As of year 1, the servers will have been fully depreciated and have a book value of zero.
The software resulting from the project will be proprietary and not sold, but it is estimated that it would have value (opportunity costs) at the end of the time horizon.
Accounts Receivable is forecasted to decrease annually. Accounts Payable is is expected to increase by the end of year 1 and hold constant at that level. No change in inventory working capital is expected.
Used the attached workbook prepared for the above case to do the following:
1. Perform a sensitivity analysis on the "Sales annual % Increase With" for 20% through 40% in increments of 5%.
2. Determine the MARR break even point (where the PW = 0) for the "Sales annual % Increase With". Do this using the Excel Goal Seek capability.
3. Evaluate the following Pessimistic and Optimistic scenarios that were generated by outside consultants. Note that the "Sales annual % Increase With" should be set back to 33.33% so that the "Current Values" scenario is accurate. The "TV Marketing" scenario is an internal proposal of using major television ads during sporting events and its Marketing Expense is for year 1 only.
|
Scenario
|
Sales annual % Increase With
|
COGS %
|
Marketing Expense
|
|
Pessimistic
|
20%
|
65%
|
$75,000
|
|
Optimistic
|
40%
|
45%
|
$75,000
|
|
TV Marketing
|
50%
|
50%
|
$1,000,000
|
4. Consider the original estimates as the "Current Values" scenario. The probability of the Current Values, Pessimistic, and Optimistic scenarios has been estimated at .50, .25, and .25 respectively. What is the expected result of these three scenarios?
5. Discuss the implications of the above results in a paragraph.
Question 9 - Benefits and Costs
A state legislature has appropriated $15 million to purchase and prepare the property for several new state parks. Alternatives have been suggested and forecasts for acquisition, preparation and annual operating costs have been estimated and listed below for each alternative. Also listed are the forecasted annual benefits to the community in dollars.
Annual Benefits are to be computed as Net Benefits or Annual Benefits minus Annual Operating Costs. The investment is the sum of Acquisition costs and Preparation Costs.
The planning time horizon should consider the acquisition and preparation as being done in the current year and then for 20 years thereafter. The value of the park at the end of the 20 year time horizon (salvage value) can be ignored. (all numbers are in millions of dollars) Use an annual discount rate of 7% in the evaluation. Any funds left in the budget after the acquisition and preparation phase can be used for additional enhancements to the parks.
a. Using a B-C method for evaluations, determine which ones should be chosen.
b. Using a B/C method for evaluations, determine which ones should be chosen.
c. What non-financial considerations might be important?
|
Site
|
Acquisition Cost
|
Preparation Cost
|
Annual Operating Cost
|
Annual Benefits
|
|
1
|
$1.0
|
$3.2
|
$1.0
|
$3.0
|
|
2
|
$2.2
|
$2.3
|
$1.2
|
$2.5
|
|
3
|
$1.4
|
$0.8
|
$0.9
|
$2.0
|
|
4
|
$0.9
|
$0.7
|
$1.4
|
$3.2
|
|
5
|
$3.4
|
$3.1
|
$1.0
|
$1.0
|
|
6
|
$2.6
|
$1.4
|
$0.9
|
$1.4
|
|
7
|
$1.4
|
$1.6
|
$1.3
|
$1.5
|