Excel by lanyuehua


									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
   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

   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
    9) Save and close the Transport Quotes.xlsr wookbook

To top