Instructions Misy XXXXXXXXXXProject 3 IIII XXXXXXXXXXI IIIIII Do Not Do not insert or delete either rows or columns in any of these worksheets. Do not move any of the worksheets in this workbook...

1 answer below »
See attachment please complete excel document


Instructions Misy 5325 Project 3 IIII I IIIIII Do Not Do not insert or delete either rows or columns in any of these worksheets. Do not move any of the worksheets in this workbook relative to other worksheets. Do not modify the name and bar code in the grey bar of all worksheets. Do not change the order of the data if the instructions do not state that. Do Before submit your assignment, please rename the file by removing the work "blank" in front of the file name. For example, if the file titled "blankDoeJohnProj3" must be renamed to "DoeJohnProj3" Please note that the file name is case-sensitive (i.e., uppercase letter is NOT the same as lowercase letter) 5 % will be deducted for incorrect FileName, misorder of the worksheet, incorrect worksheet name, or incorrect information on the bar code Complete all of the worksheets. FinancialFunction Name: WilliamsMercedes IIII I IIIIII Payment Calculation Instruction: Calculate Loan Payment using PMT Function. All cells must contain formulas (i.e., typing the number directly into the cells will be graded as incorrect answers). 1.1 Susan purchases a Mazda 6 for $22,000. To pay for the car, she will make monthly payments over 4 years, annual interest rate = 5.25%. What are the monthly payments? Put the formula for the claculation in the cell A6. 1.2 What is the total amount (interest and principal) that Susan will have to pay for the car? 1.3 How much interest will Susan pay? 2.1 Ryan purchases a house for $120,000 to be paid for over 30 years at an interest rate of 6.25% per year. What are the monthly payments? 2.2 What is the total amount (interest and principal) that Ryan pay for the house? 2.3 How much interest does Ryan pay? Future Value Instruction: Calculate the amount of money using FV Function. All cells must contain formulas (i.e., typing the number directly into the cells will be graded as incorrect answers). 1.1 If John deposits $200 in the bank each month for 5 years where the annual interest rate is 7.5%, how much does he have in the bank at the end of the five years? 1.2 How much interest does John earn during the five years? 2.1 Rachel deposits $400 per month for 5 years into an account that pays 6% annual. How much is in Rachel's account at the end of the five years? 2.2 How much does Rachel actually deposit into the account? 2.3 How much interest does Rachel earn? Amortization Name: WilliamsMercedes IIII I IIIIII Amortization Schedule Instruction:Develop and amortization table for the payment of a loan on a house. Payments are monthly for 10 years. Loan on the house is $179,000. Interest is 5.3% annual. Payment:1.92493 Payment NumberPayment MonthBeginning BalanceEnding BalancePayment AmountToward PrincipalToward Interest 1Apr-02179,000.00 2May-02 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 PMT formula PV formula Break-Even Analysis Celine's Scooter Supply Ltd.Break-Even Quarterly Income StatementAnalysis RevenueUnitsTotal RevenueTotal ExpensesOperating Income Units Sold875,000.00 Price per Unit$22.50250,000 Total Revenue275,000 Fixed Expenses300,000 Administrative$2,250,000.00325,000 Leasing800,000.00350,000 Marketing500,000.00375,000 Salary and Benefits1,100,000.00400,000 Total Fixed Expenses425,000 Variable Expenses450,000 Material Cost per Unit$3.25475,000 Total Material Cost500,000 Manufacturing Cost per Unit$4.75525,000 Total Manufacturing Cost550,000 Total Variable Expenses575,000 Summary600,000 Total Expenses625,000 Operating Income650,000 Name: WilliamsMercedes IIII I IIIIII Instructions: 1. Use the formulas to calculate the following: Total Revenue = Units Sold * Price per Unit Total Fixed Expenses = sum all fixed expenses Total Material Cost = Units Sold * Material Cost per Unit Total Manufacturing Cost = Units sold * Manufacturing Cost per Unit Total Variable Expenses =Total Material Cost + Total Manufacturing Cost Total Expenses =Total Fixed Expense + Total Variable Expense Operating Income = Total Revenue - Total Expenses 2. Use the function Data Table to derive the Total Revenue, Total Expense, and Operating Income for different amount of units sold. The number of units to break even for a price per unit of $3.25 should be 1,428,571. Enter the correct formula in cells F4, G4, and H4. Then, use the function Data Table (not the formula) we learn in chapter 4 to derive the Total Revenue, Total Expense, and Operating Income for different amount of units sold. Microsoft Access 2010 Microsoft Excel Chapter 4 Financial Functions, Data Tables, and Amortization Schedules Chapter 4, Financial Functions, Data Tables, and Amortization Schedules 1 Assign a name to a cell and refer to the cell in a formula using the assigned name Determine the monthly payment of a loan using the financial function PMT Use the financial functions PV (present value) and FV (future value) Create a data table to analyze data in a worksheet Create an amortization schedule Objectives Financial Functions, Data Tables, and Amortization Schedules 2 Upon completing this chapter, we will learn how to assign a name to a cell and refer to the cell in a formula using the assigned name. We will also determine the monthly payment of a loan using the financial function PMT, use the financial functions PV (present value) and FV (future value), create a data table to analyze data in a worksheet, and create an amortization schedule. 2 3 Project – Start File Financial Functions, Data Tables, and Amortization Schedules In Blackboard, under the Learning Module, Chapter 4, the Chapter 4 - Start File is provided as shown in the screen in this slide. Please download the file and select “save as” to save the file to your flash drive. This worksheet contains the data we will need for Chapter 4. Once we complete this project, the file will look like the screen provided in the next slide. 3 4 Project – Complete File Financial Functions, Data Tables, and Amortization Schedules This slide contains the screen shot of the first worksheet, or tab, of what we will achieve once we complete all we should learn in Chapter 4. Note that this complete file is also available in Blackboard under Learning Module Chapter 4. 4 Financial Functions, Data Tables, and Amortization Schedules 5 Project – Loan Payment Calculator with Data Table and Amortization Schedule This slide contains information concerning what we need to create each section. The "Mortgage Payment Calculator" section demonstrates how to calculate a monthly payment for a loan of $294,000.00 with a 3.375% interest rate when the term of the loan is 15 years. The "Varying Interest Rate Schedule" section demonstrates how to use a Data Table function to automatically derive the monthly payment, total interest, and total cost of the house for various interest rates starting from 3% to 4.25%. The "Amortization Schedule" section calculates the status of the loan for each specific period. We’ll learn the details on how to work with each section in the following slides. 5 Financial Functions, Data Tables, and Amortization Schedules 6 Creating Names Based on Row Titles Left Column Checked Formulas Tab Create from Selection Instead of referring to a cell by using a cell reference such as A1, B2, etc., we can also name a cell or a range of cells. In this project, we will use the data contained in cells C4 to C8 to serve as names for cells D4 to D8. To do this, we need to highlight the range from C4 to D8. Under the formulas tab, click the Create from Selection button to display the Create Names from the Selection dialog box. Make sure that only the Left Column box is checked to indicate that we will use the data contained in the left column to serve as the name. Click the OK button to name the cells. 6 Financial Functions, Data Tables, and Amortization Schedules 7 Name Box Name Box After naming the cells, you can place your mouse in cell D4, then look at the name box. Note that the name box already contains the specific name, Date, instead of D4. You can also check D5, D6 and so on. Note that the name cannot contain spaces. 7 Financial Functions, Data Tables, and Amortization Schedules 8 Entering the Loan Amount Formula Using Names Price-Down_Pymt. Once the cell is named, it is better to use the mouse clicking or point mode method when entering the cell formula, rather than typing the cell reference. So, in this slide, to calculate the Loan amount in cell D8, instead of typing D6 minus D7, we will click on the cell D6, then type the minus sign and click on D7. Notice that the name of the cell will be used instead of the cell reference which makes the formula much more meaningful. In addition, when the name is used in the formula, that formula will automatically become an absolute reference. In other words, if you copy this formula and paste it into another cell, the formula will remain the same. 8 Financial Functions, Data Tables, and Amortization Schedules 9 Creating Names Based on Row Titles Left Column Checked Create from Selection Formulas Tab Next, we will use the knowledge we just learned to name cells F4 to F8 using the data in cells E4 to E8 to serve as names. 9 Financial Functions, Data Tables, and Amortization Schedules 10 Other Financial Functions There are three financial functions we will learn in this Chapter. The first function is the Future Value function which returns the future value of an investment. The second function is the Payment function. This function calculates the payment for a loan. The third function is the Present Value function. This function returns the present value of an investment. We will start by learning the Payment function in the next slide. 10 Syntax: =PMT(interest rate, numbers of periods, loan amount)
Answered Same DaySep 02, 2021

Answer To: Instructions Misy XXXXXXXXXXProject 3 IIII XXXXXXXXXXI IIIIII Do Not Do not insert or delete either...

Akshay Kumar answered on Sep 02 2021
143 Votes
Instructions
                Misy 5325 Project 3
     IIII I IIIIII
        Do Not
        Do not insert or delete either rows or columns in any of these worksheets.
        Do not move any of the worksheets in this workbook relative to other worksheets.
        Do not modify the name and bar code in the grey bar of
all worksheets.
        Do not change the order of the data if the instructions do not state that.
        Do
        Before submit your assignment, please rename the file by removing the work "blank" in front of the file name.
        For example, if the file titled "blankDoeJohnProj3" must be renamed to "DoeJohnProj3"
        Please note that the file name is case-sensitive (i.e., uppercase letter is NOT the same as lowercase letter)
        5 % will be deducted for incorrect FileName, misorder of the worksheet, incorrect worksheet name, or incorrect information on the bar code
        Complete all of the worksheets.
FinancialFunction
    Name: WilliamsMercedes IIII I IIIIII
    Payment Calculation
    Instruction: Calculate Loan Payment using PMT Function. All cells must contain formulas (i.e., typing the number directly into the cells will be graded as incorrect answers).
    $509.14    1.1 Susan purchases a Mazda 6 for $22,000. To pay for the car, she will make monthly payments over 4 years, annual interest rate = 5.25%. What are the monthly payments? Put the formula for the claculation in the cell A6.
    $24,438.70    1.2 What is the total amount (interest and principal) that Susan will have to pay for the car?
    $2,438.70    1.3 How much interest will Susan pay?
    $738.86    2.1 Ryan purchases a house for $120,000 to be paid for over 30 years at an interest rate of 6.25% per year. What are the monthly payments?
    $265,989.83    2.2 What is the total amount (interest and principal) that Ryan pay for the house?
    $145,989.83    2.3 How much interest does Ryan pay?
    Future Value
    Instruction: Calculate the amount of money using FV Function. All cells must contain formulas (i.e., typing the number directly into the cells will be graded as incorrect answers).
    $14,505.42    1.1 If John deposits $200 in the bank each month for 5 years where the annual interest rate is 7.5%, how much does he have in the bank at the end of the five years?
    $2,505.42    1.2 How much interest does John earn during the five years?
    $27,908.01    2.1 Rachel deposits $400 per month for 5 years into an account that pays 6% annual. How much is in Rachel's account at the end of the five years?
    $24,000.00    2.2 How much does Rachel actually deposit into the account?
    $3,908.01    2.3 How much interest does Rachel earn?
Amortization
    Name: WilliamsMercedes IIII I IIIIII
    Amortization Schedule
    Instruction:    Develop and amortization table for the payment of a loan on a house.
        Payments are monthly for 10 years.
        Loan on the house is $179,000.
        Interest is 5.3% annual.
        Payment:    1,924.92924
        Payment Number    Payment Month    Beginning Balance    Ending Balance    Payment Amount    Toward Principal    Toward...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here