Assuming 3,000 trips will be made per year, which location should be chosen based on the overall cost?

FIN 5203, Trine University, Fall 2019, Dr. Kolar

Page 1

Excel Assignment 1: due Sunday, September 1st at 11:55pm

Note: Please upload your Excel file (in the .xlsx format – do not use the GetFormula function I used in my examples – include your name in the file name) on Moodle using the corresponding link under Week 2. Start with a new Excel file and answer each question in a separate sheet within your file. Please make sure you use cell references wherever appropriate, show your inputs at the top like I did in my examples, show and do all of your work directly in Excel, and answer all questions (you may have to type written answers to some questions if appropriate). After you upload the file, double-check the file you uploaded and make sure it contains all of your answers; let me know immediately in case there is an issue with the file you uploaded.

Question 1 (based on Chapter 2) A local government board hired you to recommend which of the two possible sites should be chosen for a new recycling facility. Location A is much closer to the city center, and thus the average hauling distance would only be 2 miles, while Location B being outside of the city would result in an average hauling distance of 5 miles. Public funds will have to be used to pay for the leasing cost of the site, which would be $50,000 a year for Location A and $10,000 a year for Location B, as well as for the hauling cost which is $200 per mile for each trip. Assuming 3,000 trips will be made per year, which location should be chosen based on the overall cost?

Question 2 (based on Chapter 2) Metal ABC produces sheets of metal. Its fixed cost in year 2019 is $1,000,000. The variable cost is $400 per sheet.

a. Find the break-even quantity assuming the selling price is $500 per sheet. b. Find the price and quantity that will result in maximum profit, assuming the demand function is 𝑃 =

$10,000 − 6𝑄. How much profit will be made using the profit-maximizing price and quantity values?

Question 3 (based on Chapter 3) A package delivery company is contemplating building a new shipping center. The most recent shipping center, 10,000 square feet large, was built in 2005, at a cost of $500,000. The new shipping center will be 15,000 square feet large. Using the power-sizing cost-estimating model, estimate the cost of building a new shipping center now, using the assumptions below to construct a weighted cost index value for 2005 and for today. Use 0.90 for the cost-capacity factor.

The dollar cost of building a shipping center can be broken down into the cost of labor (20%), materials (35%), and equipment (45%). The cost index for labor is 120 in 2005, and 150 today. The cost index for materials is 180 in 2005 and 170 today. The cost index for equipment is 140 in 2005 and 200 today.

Question 4 (based on Chapter 4) Milana just deposited $10,000 in her account. Assuming the annual interest rate is 8%, how many years will it take before her account balance reaches $23,000? (Please make sure you use the time value of money Excel functions)

FIN 5203, Trine University, Fall 2019, Dr. Kolar

Page 2

Question 5 (based on Chapter 4) Ali wants to retire in 15 years. Earlier today, he opened an account and deposited $25,000. In addition, he will deposit $5,000 at the end of each year until he is ready to retire. Once he retires, he wants to be able to withdraw the same amount at the end of each year, for 25 years. Assuming a 7% annual interest rate in his account, what is the maximum amount he will be able to withdraw each year after he retires?

(Hint: this problem needs to be done in 2 steps, using the time value of money Excel functions.)

Question 6 (based on Chapter 4) Chandra is considering borrowing $150,000 to buy a house, using a 15-year fixed-rate mortgage. Assuming quoted annual interest rate (APR) of 3.9%, put together an amortization table showing all of the monthly payments broken down into the amount going toward paying interest and the amount going toward paying down the mortgage loan balance. You should also have the beginning and ending balance for each month in your table. Please make sure you use cell references and copying and pasting to fill in the rest of the table once you enter the necessary formulas for the first and the beginning of the second month.