Advanced Spreadsheets for Business and Economics

Week # 3 - Chapter # 6 Assignment

This week we will see: Chapter 6 “Evaluating the Financial Impact of Loans and Investments”

 Level 1 explores some fundamental financial calculations to evaluate different financing options

 Level 2, these basic concepts are expanded upon to develop a cash flow

analysis and an amortization table, which is a schedule for paying off a loan. You also will learn about Excel tools for calculating depreciation, a technique used to allocate the costs of an asset over its useful life.

We will cover Levels 1 and 2.

Let's get started: ==========================================================================================

Week # 3 - Chapter # 6 Assignment

Chapter # 6 - Level 1: Objectives (Pages 360 – 377)  Understand how simple interest and compound interest are calculated (page 360)  Determine the value of a loan payment (PMT Function) (page 363)  Analyze positive and negative cash flows (pages 365)  Determine the future value and the present value of a financial transaction (Using the

RATE, NPER, PV, and FV Functions (pages 368)  Determine the interest rate and the number of periods of a financial transaction (page

372)

Level One: LEARNING

The material covered the Case Scenario in Level 1 (pages 360) “Calculating Values for Simple Financial Transactions”.

You should read learning material on pages 360 - 377 and the youtube.com video tutorial for this level. Use the attached presentation in Level 1 to see the video Level 1: “Calculating Values for Simple Financial Transactions” Or use the link: https://www.youtube.com/watch?v=YmKoWVKIvbs&list=PLnz4OKywJPqsAcTWt_cFbq8pArR8auh8d

&index=16

Level One: PRACTICE:

In the Week # 3 - Chapter 6 – Exercises Document, make the Exercise Ch6-1: Level 1 –Advertising Post your completed file to Blackboard for review and grading. The file Name should be 6-1-Advertising -YourName.xlsx POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (20 POINTS)

Level One: APPLYING

In the Week # 3 - Chapter 6 – Exercises Document, make the Exercise Ch6-2: Level 1 – Evaluating Loan Options for Flowers By Diana Post your completed file to Blackboard for review and grading. The file Name should be 6-2-Loan-Analysis-YourName.xlsx.

POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (20 POINTS)

==================================================================

Week # 3 - Chapter # 6 Assignment

Chapter # 6 - Level 2: Objectives (Pages 379 – 399)  Set up an amortization table to evaluate a loan  Create a sequence of numbers automatically  Calculate principal and interest payments  Calculate cumulative principal and interest payments  Set up named ranges for a list  Calculate depreciation and taxes

Level Two: LEARNING

The material covered in Level 2: Creating a Projected Cash Flow Estimate and Amortization Schedule on pages 379 to 399.

You should read the learning material covered on pages 379 to 399 and the youtube.com video tutorial for this level. Use the attached presentation in Level 2 to see the video Level 2: Creating a Projected Cash Flow Estimate and Amortization Schedule Or use the link: https://www.youtube.com/watch?v=MwijOav0a7k&list=PLnz4OKywJPqsAcTWt_cFbq8pArR8auh8d&i

ndex=17

Level Two: PRACTICE

In the Week # 3 - Chapter 6 – Exercises Document, make the Exercise Ch6-3: Level 2 – Ski Molder Cash Flow

Post your completed file to Blackboard for review and grading. The file Name should be 6-3-Ski-Molder-Cash-Flow-Estimate-YourName.xlsx POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (20 POINTS)

Level Two: APPLYING

In the Week # 3 - Chapter 6 – Exercises Document, make the Exercise Ch6-4: Level 2 – Creating a Mortgage Calculator for Tri-State Savings &

Loan

Post your completed file to Blackboard for review and grading. The files Name should be 6-4-Mortgage-Calculator-YourName.xlsx &

6-4-Mortgage-Calculator2-YourName.xlsx POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (20 POINTS)

==================================================================

Week # 3 - Chapter # 6 Assignment

Create a Word Document 4-Essay-Chapter6-YourName to answer the following questions for Chapter# 6. POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (15 POINTS)

Match the following lettered items with Questions 1–14. A. Compound Interest

E. IRR I. PMT M. ROI

B. CUMIPMT F. NPER J. PPMT N. Simple Interest

C. FV G. NPV K. PV O. SLN

D. IPMT H. Payback Period L. RATE P. Type

1. _____Function to calculate the value at the end of a financial transaction 2. _____Function to calculate the interest percentage per period of a financial transaction 3. _____Function to calculate the value at the beginning of a financial transaction 4. _____Function to calculate the number of compounding periods in a financial transaction 5. _____Function to calculate periodic payments into or out of a financial transaction 6. _____Use a 0 for this argument to indicate that interest will be paid at the end of each

compounding period 7. _____This type of interest is calculated based on original principal regardless of the previous interest earned 8. _____This type of interest is calculated based on principal and previous interest earned 9. _____Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value 10. _____Function to calculate the cumulative interest paid between two periods 11. _____Function to calculate the amount of a periodic payment that is interest in a given period 12. _____Function to calculate the amount of a specific periodic payment that is principal in a given period

Week # 3 - Chapter # 6 Assignment

Let me know if there is anything you don't understand -- the earlier the better. ALWAYS attach a copy of yours excel file clearly indicating where you have a problem.

The solution files that you need to attach are: 6-1-Advertising -YourName.xlsx (20 points) 6-2-Loan-Analysis-YourName.xlsx (20 points) 6-3-Ski-Molder-Cash-Flow-Estimate-YourName.xlsx (20 points) 6-4-Mortgage-Calculator-YourName.xlsx (20 points) 6-4-Mortgage-Calculator2-YourName.xlsx (5 points) 4-Essay-Chapter6-YourName.docx (Questions requested) (15 points)