Instructions
Misy XXXXXXXXXXProject 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 inco
ect FileName, misorder of the worksheet, inco
ect worksheet name, or inco
ect 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 inco
ect 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 inco
ect 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 Number Payment Month Beginning Balance Ending Balance Payment Amount Toward Principal Toward Interest
1 Apr-02 179,000.00
2 May-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 Statement Analysis
Revenue Units Total Revenue Total Expenses Operating Income
Units Sold 875,000.00
Price per Unit $22.50 250,000
Total Revenue 275,000
Fixed Expenses 300,000
Administrative $2,250,000.00 325,000
Leasing 800,000.00 350,000
Marketing 500,000.00 375,000
Salary and Benefits 1,100,000.00 400,000
Total Fixed Expenses 425,000
Variable Expenses 450,000
Material Cost per Unit $3.25 475,000
Total Material Cost 500,000
Manufacturing Cost per Unit $4.75 525,000
Total Manufacturing Cost 550,000
Total Variable Expenses 575,000
Summary 600,000
Total Expenses 625,000
Operating Income 650,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
eak even for a price per unit of $3.25 should be 1,428,571.
Enter the co
ect 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.