SUNY Calculating New Product Line Upfront Costs Using Excel Accounting Problems

User Generated

qnivf958

Business Finance

SUNY Morrisville

Description

Greetings, I have 3 questions in excel to be completed. The questions will be in the attachment below, thanks.

Unformatted Attachment Preview

1 Sprint LTE 9:43 AM 1 @ 32% blackboard.morrisville.edu J. Virat al IIT vartUUS TIITSIINI TISK . These problems are to be completed in EXCEL ONLY with formulas to support your calculations. For each problem, provide a 1-2 sentence explanation of each answer, which will be some points for each question. YOU WILL USE ONLY EXCEL FOR THIS PART AND NO OTHER DOCUMENT FORMAT SINCE ONLY EXCEL WILL BE REVIEWED FOR GRADING THIS PART. INDIVIDUAL WORK IS ONLY ACCCEPTABLE. 1. You have just made your first $5,000 contribution to your individual retirement account. Assuming you can earn a 7.55% rate of return and make no additional contributions, what will your account be worth when you retire in 30 years? How would that investment compare to an account you invested $1000 over the next 30 years and earn a rate of 8.50%? (10 points) 2. Indicate Inc. has an unfunded pension liability of $645M that must be paid in 15 years. In order to get a proper stock value, analysts want to discount this liability back to the present. If the relevant discount rate is 5.5%, what is the present value of this liability? (10 points) 3. Executives determined they want to invest in a new product line with the following upfront costs: Machinery and equipment $27.2M, Installation = $11.3 M and Testing = $5.5M. This new product line will generate the following annual cash flows (10 points): = 1. $9,750,000 2. $9,925,000 3. $12,540,000 4. $18,350,000 5. $34,450,000 a. At the end of the fifth year, the company will evaluate the project for future viability. Will the project be successful given a 16.5% return? b. What if an additional investment of $11.5M was added and could generate an additional cash flow of $8.25M each year? However, the additional money requires added risk and therefore the return would increase to 24%. Should Smucker consider the additional investment? If so, why? c. In addition, Executives want to finance the cost for these two projects over five years. The current average loan rate based on prior debt is approximately 3.85%. What is the annual payment and annual repayment schedule for the loan of equipment in 3a and 3b? Homework 4 - Grading Rubrics
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hey there, please see the attached Excel. The explanations for each question are included in the Excel file as well. Let me know if you have any questions. The formulas used for these types of questions can sometimes be a bit different so let me know if you have questions about the way I did things :)

1)

Contribution (PV):

$

interest rate (r):

7,55%

Years (n):

30

FV = PV (1+r)^n

$

Yearly contribution (C):

$

interest rate (r):

44.389,72 = value of your account after 30

1.000
8,50%

Years (n):
FV of annuity = C ((1+r)^n-1)/r)

5.000

30
$ 124.214,73 = value of your account after 30

Explanation: Under the first scenario, the future value must be calculated on the presen
interest on it would be $44,390 after the 30 years. Under the second scenario, the futu
be $124,214.73. The second scenario yield a much higher account value which makes se
result in a higher account balance.

2)

unfunded pension liability (FV):

$645 mil

Years...


Anonymous
Great! 10/10 would recommend using Studypool to help you study.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags