Did ted bundy pass a polygraph test

Post-Lab Questions
1. What surprised you about the anatomy of the sheep’s heart?

2. Research diseases of the heart valves. How might a valvular insufficiency affect heart function? How would valvular stenosis (stiffening) affect heart function?

1

WEEK # 3 - EXERCISES CHAPTER # 6

Exercise Ch6-1: Level 1 – Advertising To promote the new TZEdge line of athletic shoes, the marketing group has decided to purchase advertising in selected print media, including leading health and fitness magazines and brochures to upscale sport outlets. Although the cost of this advertising has been worked into the selling expense, this money will actually be needed now, in year 0, rather than in years 1 and 2, so that the advertising agencies can begin designing a promotion and arranging for publication. The finance group at TheZone will discuss financing options directly with the advertising agencies, which are willing to accept a variety of different payment terms. Your task in these steps is to set up a worksheet to analyze each of the advertising agency options. Complete the following:

1. Open a new workbook and save it as 6-1-Advertising -YourName.xlsx in the Chapter 6 folder. 2. Create a worksheet with the following column headings:

• Option# • Company Name • Number of Compounding Periods per Year • Annual Interest Rate • Loan Duration in Years • Payment per Period • Present Value • Future Value

3. Include the title TZEdge Advertising Options on your worksheet, merged and centered over the data. 4. Use the information provided for the options, enter the appropriate data and calculations for each (across the row) so that all information is listed. For all options, assume that the payment period duration will be used as the compounding period and that payments are made at the end of each period.

• Option 1—TNC Executives Inc. has proposed a campaign costing $65,000. This agency will accept full payment over the next two years in equal quarterly installments of $8,500. For this option, you need to calculate the annual interest rate. • Option 2—Hicks & Bradshaw has designed a campaign for $55,000 and indicated that it will charge a 6.5% annual rate of interest on this amount, with fixed monthly payments paid out over the next 18 months. For this option, you need to calculate periodic payments. • Option 3—AdWest Inc. has proposed an ad campaign costing $44,000. This agency is willing to accept semi-annual payments of $9,200 until the campaign is paid off. AdWest Inc. will charge a 6.25% annual interest rate. For this option, you need to calculate the duration in years that will be required to pay off this debt. • Option 4—Johnson, Bellview & Associates has shown the marketing group an excellent campaign that will cost $1,500 a month for the next three years. This agency’s payment terms are based on an annual interest rate of 5%. For this option, you need to calculate the initial value of this advertising campaign.

2

WEEK # 3 - EXERCISES CHAPTER # 6

5. In an adjacent column, calculate the total yearly payments required for each option. 6. Format your worksheet so that it is easy to read. Be certain that dollars and percentages are included where appropriate and that columns display consistent numbers of decimal places (line up decimal points). Wrap text, as necessary, to format the column headings within reasonable column widths. Highlight cells with the data outputs. 7. In a row below your data, select an option to recommend if you were trying to minimize the yearly outlay for this campaign. Label and highlight your recommendation in pink. 8. Add your name and Current Date at the end of the workbook.

9. Save and close the 6-1-Advertising -YourName.xlsx workbook.

3

WEEK # 3 - EXERCISES CHAPTER # 6

Exercise Ch6-2: Level 1 – Evaluating Loan Options for Flowers By Diana

Diana Bullard currently rents space for her small florist business. As her business continues to grow, Diana has decided to purchase her own building. She has selected a site and now requires financing. After meeting with several banks to discuss financing options for a mortgage, Diana has the data she needs to analyze her options. She lists the purchase price of the building and the different values for each of the loan variables together with the other data inputs in an Excel workbook named Loan.xlsx. Her analysis must also take into account the following: • Down payment—The amount of money Diana will pay at the time she purchases the building. Provided is the percent of the building purchase price that will be required for a down payment on each corresponding loan. The difference between the sale 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 rate and point combinations. Frequently, loans with higher points have lower interest rates. One point equals 1% of the loan value, so one point on a $7,500 loan is $75. • Fees—The additional amounts banks sometimes charge when lending a mortgage. These amounts vary by bank and loan type. Typical charges include application fees, appraisal fees, credit report fees, and so on. Your task is to complete the Loan worksheet for Diana, using cell references whenever possible. Write the formulas in cells G8 through K8 so that they can be copied down the column to calculate values for each option. Write the formulas so they will automatically update if the mortgage value changes. Loan options 1–7 are all compounded monthly. Complete the following:

1. Open the workbook named Loan.xlsx in the Chapter 6 folder, and then save the file as 6-2- Loan-Analysis-YourName.xlsx. 2. In the Loan Value column, calculate the face value of this mortgage. The purchase price of the building is in cell E3. 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 nominal interest rate indicated. Assume that the loan is completely paid off at the end of this duration. The number of compounding periods per year is in cell E4. 4. In the Actual Amount Borrowed column, calculate the actual amount Diana will borrow by subtracting the points and fees from the loan value. 5. To take these fees into account, the lender is required by law to disclose the APR (the annual percentage rate of interest) of the loan being charged. However, banks can calculate APR in different ways, including or excluding different fees.

4

WEEK # 3 - EXERCISES CHAPTER # 6

To calculate an actual annual interest rate being charged on this loan (APR), use the actual amount borrowed (Step 4) as the present value of the loan, the monthly payment (Step 3), and the corresponding loan duration. 6. In the Payment with Balloon column, use the nominal interest rate and loan value (from column G) to determine the monthly loan payment if you altered the loan to include a $20,000 balloon payment at the end of the loan. 7. The building seller has also offered Diana a private loan for 85% of the value of the building. In return, Diana must pay $4,500 per month for the next 10 years. Determine the annual interest rate being charged (cell E17). Inputs do not have to be explicitly listed elsewhere. 8. Diana is negotiating with the seller and is willing to pay $10,000 per quarter at 6½% interest per year compounded quarterly. She will borrow everything but a 10% down payment. Determine how many years it will take to pay off the loan (cell E18). Inputs do not have to be explicitly listed elsewhere. 9. Ten years ago, Diana invested $75,000 in a bank CD. The CD has earned 3.25% annual interest compounded yearly. Determine (TRUE/FALSE) if Diana has sufficient funds from this CD for the down payment for Option #1 (cell E19). 10. Diana has decided that she prefers a bank loan and, given cash flow issues, wants the loan with the smallest payment. Highlight in light blue the cell in column H containing the payment of the loan Diana should select. Optional Challenge: Using Conditional Formatting, highlight the cell containing the minimum payment value, so that if any of the values on any of the bank loans are later modified, the correct value will be automatically highlighted. 11. Add your name and date at the end of the exercise

12. Save and close the 6-2-Loan-Analysis-YourName.xlsx workbook

5

WEEK # 3 - EXERCISES CHAPTER # 6

Exercise Ch6-3: Level 2 – Ski Molder Cash Flow The Equipment division at TheZone is looking into a new piece of equipment that was developed in Europe to mold skis more precisely and less expensively than the current technology being used at TheZone. The cost of the machine plus installation is estimated to be $1,500,000. The projected cost savings are expected to be $10.00 per pair of skis. 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. Complete the following:

1. Open the workbook named Ski.xlsx in the Chapter 6 folder, and then save the file as 6-3-Ski- Molder-Cash-Flow-Estimate-YourName.xlsx. The structure for the projected cash flow estimate is provided on Sheet1, as shown in Figure 6.26.

2. Rename the Sheet1 worksheet as Cash Flow. 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 140,000 pairs. The sales volume for each successive year is assumed to be 5% more than the previous year. Round your calculated sales volumes to the nearest whole number. 4. Enter the cost savings as $10.00 per pair, which will be the same amount in the subsequent years. 5. Calculate the cost savings as the number of pairs of skis sold multiplied by the cost savings per ski. 6. On a separate worksheet named loan (similar to the Loan worksheet shown in Figure 6.15), create an amortization table listing the principal and interest payments and remaining principal in each monthly period, assuming TheZone will borrow the money under the following terms:

6

WEEK # 3 - EXERCISES CHAPTER # 6

Funding will be arranged for the entire cost of this investment (the cost of the machine plus installation) at 5.25% interest compounded monthly, paid out in full in equal monthly installments over four years. 7. On the Cash Flow worksheet, calculate the cumulative interest expense for year 1 (the interest portion of the loan payments for the corresponding year). Assume the loan will start at the beginning of year 1 (January), and all payments will be made at the end of each period. Write your formula so that it can be copied across the row to automatically calculate these values for years 2 through 4. 8. Calculate the depreciation for this equipment using the straight line depreciation method. The equipment is assumed to have an 8-year life with a salvage value of $50,000 at the end of that period. For cost, use the cost of the machine plus installation. Set up a separate worksheet named depreciation to store these values (similar to the depreciation worksheet shown in Figure 6.19), and use named ranges in your formula. 9. Calculate the net cost savings—the cost savings less the interest expense and depreciation. 10. Calculate the additional tax that would be owed (based on the net cost savings) assuming that TheZone is taxed at a 35% rate. Use a global named range to store this value.

7

WEEK # 3 - EXERCISES CHAPTER # 6

11. Calculate the savings after taxes 12. Complete the worksheet, adding back in the depreciation that was deducted and adding in the cumulative principal 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 your formulas will work for each of the cash flow years. 13. Skipping several rows under the data, include a sentence summarizing whether or not the cost of this machine will be recovered based on the Projected Cash Flow Estimate over the four years. Highlight your analysis in a light blue color. 14. Add titles on each worksheet, and format them to make them easy to read and understand. 15. Add your name and date at the end of the exercise

16. Save and close the 6-3-Ski-Molder-Cash-Flow-Estimate-YourName.xlsx workbook.

8

WEEK # 3 - EXERCISES CHAPTER # 6

Exercise Ch6-4: Level 2- Creating a Mortgage Calculator for Tri-State Savings & Loan.

You have been working as a loan officer at Tri-State Savings & Loan for over six months. Most of the work you do involves dealing with mortgages for home buyers and small business owners. Frequently, prospective buyers come in seeking information about payments for a particular size mortgage and/or the maximum size mortgage they can obtain for a particular payment. They also frequently require information on the tax implications of their selected mortgages, including cumulative yearly interest and depreciation. The answers to these questions vary based on the interest rates currently being offered and the terms the potential buyer is seeking, such as loan duration, balloon payments, and so on. Although you have found some excellent Web sites that perform the necessary calculations, relying on the Web is sometimes problematic. You can just as easily construct this type of mortgage calculator in Excel, which is what you will do in these steps. Complete the following:

1. Create a new workbook and save it as 6-4-Mortgage-Calculator-YourName.xlsx in the Chapter 6 folder. Rename Sheet1 as Calculator and include the following elements:

• First, construct a small mortgage calculator in which you can fill in the data inputs for the value of the mortgage, the loan duration in years, the number of payment periods per year, and the annual interest rate. Assume that at the end of the loan duration, no balance will be owed. Then, using this data, calculate the payment for the mortgage. Assume the payment is rounded to the nearest cent. Format the worksheet so the calculator is easy to read and use with data inputs and outputs clearly defined (labeled).

• Below the mortgage calculator on the same worksheet, create an amortization table for the loan, organized as follows:

Period Number Remaining Principal Interest Payment Principal Payment

Make sure the table can accommodate a maximum mortgage duration of 30 years, assuming monthly payments. The remaining principal should start out by referencing the calculator’s principal value, and thereafter reflect the previous remaining principal value and principal payment. Write the interest and principal payment formulas so that if any of the calculator elements change, these amounts will be automatically updated. Write them so they can be copied down the column for each corresponding period.

Optional Challenge: To avoid #NUM! Errors in periods past the end of the loan, nest your principal and interest payment formulas inside an IF statement to return a 0 if no further interest or principal payments are required.

2. To test the calculator, use the following customer inputs: determine the monthly payment for Zach Jones, who wants a 25-year $300,000 mortgage. The current annual interest rate is 4.25% compounded monthly. The loan is completely paid off at the end of 25 years. Assume no additional points or fees.

9

WEEK # 3 - EXERCISES CHAPTER # 6

3. On a separate worksheet named Tax, create a table listing years 1–30 and calculate the following:

• Cumulative interest payments for each year. Write a formula that automatically calculates this value for the corresponding periods so that it can be copied down for each year. Assume that the loans all begin in January so that no “partial” years need to be calculated. Note that to accommodate variable periods (months, quarters, and so on), the beginning and ending periods must be formulas that reference the number of periods per year on your mortgage calculator. (Hint: To automatically determine the starting period, multiply the year number by the number of periods per year, and then subtract one less than the number of periods per year.) • In three adjacent columns, calculate the value of the expected tax deduction for tax rates of 15%, 28%, and 35% for the corresponding year (interest payments * tax rate). Your formula should copy both down the column and across the row. Enter the tax rate in a row above the corresponding column. • For sample data, use the values from the loan for Zach Jones.

Optional Challenge: Automatically substitute zeros instead of #NUM! Errors in periods past the end of the loan.

4. In some cases, small business owners who want to buy the properties for their business endeavors are applying for mortgages. For these customers, it would also be helpful to provide them with depreciation estimates. Create a separate worksheet named Depreciation to calculate the depreciation. Include the following:

• At the top of the table, list the inputs that will be required: asset value (which will differ from mortgage to mortgage, so it needs to be entered directly), salvage value, and asset life (which will differ from the loan duration).

• Just below the input area, calculate the yearly depreciable value using straight line depreciation.

• Next, create a table below the straight line depreciation to calculate the depreciation for each year (1–20) based on the double-declining balance (DDB) method. For more details on how to use the DDB function, refer to Excel Help. Assume the default factor will be used and, therefore, can be omitted. Your table should include the year and the depreciable amount, using Year and DDB as headings to identify the values.

• Enter the following test data: asset value of $200,000 with a 10-year life and a salvage value of $10,000.

5. Format the workbook so it is easy to read. Save the changes to your Mortgage Calculator.xlsx workbook. 6. Add your name and date at the end of the exercise

10

WEEK # 3 - EXERCISES CHAPTER # 6

7. Use the Save As option to create a copy of the workbook named

6-4-Mortgage-Calculator2-YourName.xlsx. Then complete the following:

• Modify your inputs and formulas on the Calculator worksheet so that you can enter a known monthly payment, duration, and interest rate to calculate the associated mortgage value as output. Double check that all of your other formulas work:

• Use the following for your test data: Kelly Hamilton wants to buy a building she plans to use

as rental property. If she can make monthly payments of $1,250 per month for the next 30 years, how large a mortgage can she take, assuming that the current interest rate on a 30- year mortgage is 5% per year compounded monthly?

For depreciation, assume an asset value of 110% of the loan value, a salvage value of $15,000, and a depreciable life of 20 years.

8. Save and close both the 6-4-Mortgage-Calculator-YourName.xlsx workbook and the 6-4-Mortgage-Calculator2-YourName.xlsx workbook.