EXCEL ASSIGNMENT:
THREE SPREADSHEETS NEEDS COMPLETION, PLEASE SEE ATTACHED
Instructions Misy 5325 Project 3 Name: BennettAlycia IIII III I I I III 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: BennettAlycia IIII III I I I III 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: BennettAlycia IIII III I I I III 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: 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: BennettAlycia IIII III I I I III 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.