MTH 105 PROJECT 8 Finances Version A-1
docx
keyboard_arrow_up
School
Eastern Gateway Community College *
*We aren’t endorsed by this school
Course
105
Subject
Industrial Engineering
Date
Feb 20, 2024
Type
docx
Pages
5
Uploaded by ConstableGuanacoPerson750
(A)QR Project 8 Finances
Name: ______
Must show calculations/formula for credit. Any answer given with no calculations/descriptions of how arrived at answers shown will result in no credit for that answer. If utilizing excel must state/describe your formulas used and your inputs
I strongly suggest you use excel for this project. The calculations are accomplished much simpler, quicker, and more accurate than doing the formulas by hand. The video embedded in your Canvas course provide a step-by-step process of how to go about it. 1.
The following chart is data over an 8-month period that shows how much a company spent in advertising and the sales revenue for that month
X Y A26:A33 b26: b33
MONTH
ADVERTISING $
SALES $
March
900
56000
April
2700
89200
May
3150
98500
June
1300
54000
July
3400
97000
Aug
1500
56000
Sept
2300
93000
Oct
2250
79000
a) What is the correlation coefficient? (round to 2 decimals) describe how you utilized excel to arrive at this number (recommended) or show the formula you utilized to arrive at this answer
I put the table into excel and used the Correl formula .939934211
I rounded it to .94
Correlation coefficient of 0.94
b) Is it a positive or negative correlation?
1
It is a positive correlation
c)
Would you say it is a strong correlation, weak correlation, or no correlation? What is the indicator that led you to that conclusion?
I would say it is a strong correlation. Due to it being close to 1 it means it’s a strong correlation.
d)
What is the linear equation (y = mx + b form) that best approximates the relationship between advertising dollars spent(x) and sales revenue(y) based on the above 8 months of data? (round to 2 decimals for the slope and the y intercept)
describe how you utilized excel to arrive at this equation (recommended) or show the formula you utilized to arrive at your equation
I used the slope formula =
and it said that it = 20.56022346 = 20.56
Then I used the intercept formula
and it said it = 32862.01117 = 32862.01
Y= 20.56x + 32862.01
e)
What sales revenue would the company expect for the following advertising spending? Round to
nearest cent show calculation
a)
3000 Y= 20.56(3000) + 32862.01 = 94542.01
b)
2100
Y= 20.56(2100) + 32862.01 = 76038.01
c)
1300
Y= 20.56(1300) + 32862.01 = 59590.01
2
f)
If you were in charge of the advertising department how much would you spend on each of the next 4 months on advertising and how and why did you arrive at your decision?
Nov 3000
Y= 20.56(3000) + 32862.01 = 94542.01
Jan 3200
Y= 20.56(3200) + 32862.01 = 98654.01
Feb 3400
Y= 20.56(3400) + 32862.01 = 102766.01
March 3600 Y= 20.56(3600) + 32862.01 = 106878.01
For these 4 months we would monitor the sales and see if they keep rising and if they do, we could continue to raise the amount of money spent on advertising or we could find a spot where the company makes a steady amount of income and find other ways to increase the sales.
Please give a brief explanation as to how and why you came up with your advertising spending for the above 4 months. I would try to spend more and more each time to see if we would continue to see an increase in sales. If we keep increasing the sales and we can handle the workload it will be better for the company. Although if the sales plateau or we cannot handle the workload I would lower the advertising to a manageable rate. We can see that there is a strong positive correlation between ads and sales, if the company can handle the extra sales, it would only grow the company and if we see a plateau, we could lower the ads and find more ways to further increase the sales.
Example: You want to buy a $18,500 car. The company is offering a 3% interest rate for 4 years. 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
What will your monthly payments be?
I will do this one for you and show you how I want you to describe your formula/inputs in excel if that is how you choose to go about solving problems 2 through 5 - which I strongly recommend. If you choose to perform the calculations by hand show the formula used with values. Excel:
Formula used: PMT
Rate input: .03/12
NPer input: 4*12
Pv input: 18500
Answer: $409.49 per month
2.
You want to buy a $22,500 car. The company is offering a 4% interest rate for 5 years. a.
What will your monthly payments be? Round to the nearest cent.
22500= x(1-(1+.04/12)^-12x5)/(.04/12)= 414.37
You would pay 414.37 per month for the payments.
b.
Assuming you pay that monthly amount for the entire 5 years, what is the total amount of money you will pay during those 5 years for the car?
12x 5 = 60 60x 414.37= 24,862.20
c.
How much interest will you pay during those 5 years?
22500 –24862.20 = 2362.20 You would pay 2,362.20 in interest. 3.
You have $400,000 saved for retirement. Your account earns 6% interest. How much will you be able to pull out each month, if you want to be able to take withdrawals for 25 years?
400000= x(1-(1+ .06/12)^-12x25)/(.06/12)= 2577.20
You can withdraw 2,577.20 $ per month for 25 years.
4.
You deposit $600 each month into an account earning 4% interest compounded monthly.
a) How much will you have in the account in 12 years?
(600((1+.04/12)^12x12 –1))/(.04/12) = 110661.28 You would have 110,661.28$ in 12 years.
4
b) How much total money will you put into the account?
600x12=7200 x 12 = 86400 or 144 x 600 = 86400
You would contribute 86,400$ in the account
c) How much total interest will you earn?
5.
Suppose you want to have $700,000 for retirement in 25 years. Your account earns 9% interest.
a) How much would you need to deposit in the account each month?
700000(.09/12)/ [(1+.09/12) ^12x25 –1) = 624.37
You would need to deposit 624.37 into the account every month.
b) How much interest will you earn?
12x 25 = 300 300x 624.37 = 187331 700000 -187331 = 512669
You would earn 512,669$ in interest.
6.
You deposit $2100 in a savings account paying 5.5% simple interest. The solution to this problem is not accomplished by an excel formula. Use the formula I = PRT where T is in years
a) How much interest will you earn in 18 months?
X= 2100(.055)(18/12) x =173.25
You would earn 173.25 in interest
b) How much will be in your account at the end of 18 months?
173.25 +2100= 2273.25
You would have 2273.25 in the account at the end of the 18 months.
5