VIEWS: 4 PAGES: 4 POSTED ON: 3/30/2013 Public Domain
Chapter 6 - ski The Equipment division at the Zone is looking into a new piece of EQUIPMENT that was developed in Europe to mold skies more precisely and less expensively than the current technology being used at Zzone . The cost of the machine plus installation is estimated to be$ 1,635,000. The project cost saving are expected to be $8.50 per pair of skies. You have been asked to estimate a projected cash flow savings( if any) that will be generated by this proposed project over the next four years. 1) Open the worksheet named Ski.xlsr in the chapter for folder, and then save it as ski molder Cash Estimate. The structure for the projected cash flow estimate is given. 2) Rename the sheet1 worksheet as cashflow. Insert the following title at the top of the worksheet, merged and centered : “ Ski Molding Project – Projected 4 – Year Cash Flow estimate.” 3) Enter the sales volume for each year, assuming sales in year 1 of 160,000 pairs. The sales volume for each successive year is assumed to be 5% more than the previous year. 4) Enter the cost saving as $8.5 per pair. This will not change in subsequent years. 5) Calculate the cost savings as the number of pairs of skies sold times the cost saving per ski. 6) On a separate worksheet named loan create an amortization table listing the principle in each monthly period, assuming The Zone will borrow the money under the following terms Funding will be arranged for the entire cost of this investment at 4.5% interest compounded monthly, paid out in full in equal monthly installments over this same four – year period. 7) On the cashflow worksheet, calculate the cumulative interest expense for year 1 (the interest portion of the loan payments for the corresponding year) You can reference cells on the loan worksheet as needed. Assume the loan will start at the beginning of year 1, Write your formula so that it can be copied across the row to automatically calculate these values for the years 2 through 4. 8) Calculate the depreciation for this equipment using the straight depreciation method. The equipment is assumed to have a 10- year life with a salvage value of $75,000 at the end of that period. Set up a separate worksheet names depreciation to store these values, and named ranges in your formula. 9) Calculate the net savings – the cost savings less the interest expense and depreciation. 10) Calculate the additional tax that would be owed assuming the The Zone is taxed at a 35% rate.. Use a names range to store this value. 11) Calculate the savings after taxes. 12) Complete the worksheet, adding back in the depreciation that was deducted and adding in the cumulative principle payments for the corresponding year, to arrive at a final projected cash flow estimate for each of the four years. Use the correct absolute and relative cell referencing so that formula will work for each of the cash flow years. 13) Format your worksheet as appropriate, to make it easy to read and understand. Chapter 7 – loan Down Payment – the amount Diane will pay at the time she purchases the building. The difference between the sales price and the down payment is the loan value – the face value of the loan. Points - The additional charges banks sometimes require when lending a mortgage. Banks usually offer mortgage loans in a variety of interest rates and point combinations. Frequently, laons with higher points have lower interest rates. One point equals 1% of the loan value, so 1 point on a $7500 loan is $75 Fees – The additional amounts bank sometimes charge when lending a mortgage. These amounts vary by bank and loan type. Typical charges include application fees, appraisal fees, and so on. Your task is to complete the loan worksheet for Diane, using cell reference whenever possible. The formula in cells G8 through K8 should be written so that they can be copied down the column to calculate the values for each of the options listed. These formula’s should automatically update of the mortgage value is updated. Loan options 1-7 are all compounded monthly. 1) Open the worksheet named loan.xlsr in the chapter 7 folder and save it as LoanAnalysis.xlsr 2) In the loan Value column, calculate the face value of this mortgage. 3) In the monthly payment column, calculate the monthly mortgage payment for this loan amount based on the loan value you just calculated. Use the corresponding loan duration and the nominal interest rate calculated. 4) In the Actual Amount Borrowed column, calculate the actual amount Diane will borrow, subtracting the points and fees from the loan value. 5) To take this fees into account, the lender is required by the law to disclose the APR of the loan – the annual percentage rate of interest being charged. However, different banks calculate the APR in different ways, including of excluding different fees. So, you will calculate the APR based on the actual amount borrowed, which you just calculated in the previous step., and the corresponding loan duration to calculate an actual annual interest rate being charged on this APR. 6) In the payment with balloon column, use the nominal interest rate and loan value(column G) to determine the monthly loan payment if you altered the loan to include a $10,000 ballon payment at the end of the loan. 7) The building seller has also offered Daine a private loan for 80% of the face of the building. In return, Diane must pay $8000 per quarter for the next 10 years. Determine the annual interest rate being charged ( cell E17. Inputs don’t have to be explicitly listed elsewhere . 8) Diane is negotiating with the seller and is willing to pay $5000/ quarter at7.5% interest per year compounded quarterly. She will borrow everything but a 5% down payment. Determine how many years will it take to pay off the loan( cell E18). . Inputs don’t have to be explicitly listed elsewhere 9) Eight years ago, Diane invested in a bank Cd worth $10,000. The cd has earned 4.25% annual interest compounded yearly. Determine (true/false) if Diane has sufficient finds from this CD for options #1 down payment (E19) 10) Diane has decided that she prefers a banl loan and, given cash flows issuses, wants the laon with the smallest payment. Highlight in light turquoise the cell containing the payment. Highlight in light turquoise the cell containing the payment of the loan Diane should select. 11) Save and close the Loan Analysis.xlsr workbook Chapter 8 – Quotes Executive transport Inc is a company that rents cars, vans , and limos to a wide range of customers. Customers frequently call Executive Transport’s sales staff to receive quotes of rental expenses. As gas prices have been climbing over the last year, customers have become more conscious of fuel efficiency and its impact on their total cost of transportation. As a service to its customers, Executive Transportation has modified its Quotes workbook to include information on its cars gas mileage of the gas expense related to customers planned trips. All that remain to be added to the work book is information specific to each of the car types, and a more detailed analysis of the interaction between gas prices and total cost of production, and the gas mileage of cars and its impact on the total cost of transportation. Complete the following 1) Open the workbook named Quotes located in chapter 8 and save it as TransportQuotes.xlsx in the same location. 2) Examine the Analysis worksheet and apply appropriate names to cell D17:D20 3) Set up the structure of a one variable data table on the analysis worksheet that shows the car charge, mileage charge, gas expense, and the total transportation expense given changes in the average price of gas from $1.2-$2 by increments of $0.2 4) INSTRUCT Excel to complete the one – variable data. 5) Add headings and basic formatting to the data table so it has a professional look 6) A few rows below the one – variable data table create the structure for a two variable data structure table that shows the total projected transportation expense given the interaction between average gas prices from $1.2-$2, by increments of $0.2 and miles per gallon from the 16-26 by increments of 2 miles per gallon 7) Instruct Excel to complete the two variable data table 8) Add headings and some basic formatting to the data table so it has a more professional appearance. 9) Save and close the Transport Quotes.xlsr wookbook