Help! New to excel, need to solve a problem

X

xsd87x

Hi im new to excel and im trying to figure out this problem...

Essentially, I am supposed to use Excel to solve a problem given the
following information.

-Between 1961 and 1970, 90 million cars total were produced.
-The life span of a car is assumed to be 10 years, therefore cars that
were manufactured in 1961 are retired in 1970.
-Each year the number of cars produced is equal to 1.3 times the number
of cars that were retired that year. Basically, the number of
manufactured cars in 1970 equals 1.3 times the number of cars
manufactured in 1961.

Given this data I am supposed to interpolate how many cars were
produced each year between 1961 and 1970. Any suggestions how I can go
about this? I am familiar with graphing in excel but equation solving
aint my strong point. Any help would be great. Thanks.
 
N

Niek Otten

The number of retired cars is irrelevant in your problem; please re-formulate the problem if that is not the case.

In A1, enter the number 1,000,000
In A10, enter the formila:
=A1*1.3
In A2, enter the formula:
=($A$10-$A$1)*(ROW()-1)/9+$A$1
Copy down to A9.

In A12, enter:
=SUM(A1:A10)

Tools>Goal Seek
Set Cell: A12, To: 90000000, By Changing cell: A1.

This gives you the numbers produced per year, starting with 7,826,087 in 1961.

--
Kind regards,

Niek Otten

|
| Hi im new to excel and im trying to figure out this problem...
|
| Essentially, I am supposed to use Excel to solve a problem given the
| following information.
|
| -Between 1961 and 1970, 90 million cars total were produced.
| -The life span of a car is assumed to be 10 years, therefore cars that
| were manufactured in 1961 are retired in 1970.
| -Each year the number of cars produced is equal to 1.3 times the number
| of cars that were retired that year. Basically, the number of
| manufactured cars in 1970 equals 1.3 times the number of cars
| manufactured in 1961.
|
| Given this data I am supposed to interpolate how many cars were
| produced each year between 1961 and 1970. Any suggestions how I can go
| about this? I am familiar with graphing in excel but equation solving
| aint my strong point. Any help would be great. Thanks.
|
|
| --
| xsd87x
| ------------------------------------------------------------------------
| xsd87x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33608
| View this thread: http://www.excelforum.com/showthread.php?threadid=533863
|
 
D

Dana DeLouis

-Each year the number of cars produced is equal to 1.3 times the number
of cars that were retired that year.
Click to expand...

Hi. Just a different idea.
If 30% growth over 10 years, then perhaps we can assume an annual growth of
2.958 %.
Perhaps a function like: = 7865919.678 * r ^ (yr - 1961)

Here's what I was thinking using vba:

Sub Year1961()
Const r = 1.3 ^ (1 / 9) ' 1.02958065156903
Debug.Print (90000000 * (r - 1)) / (r ^ 10 - 1)
End Sub

Year 1961 production would be: 7865919.67731542

A production function based on a year might be:

Function Produced(yr)
Const r = 1.3 ^ (1 / 9) ' 1.02958065156903
Produced = 7865919.678 * r ^ (yr - 1961)
End Function

Test some data:

Sub Demo()
Dim r, y, tot

'// All ratio's are 1.3
Debug.Print Produced(1970) / Produced(1961)
Debug.Print Produced(1980) / Produced(1971)
Debug.Print Produced(1965) / Produced(1956)

'// Total is 90,000,000
For y = 1961 To 1970
tot = tot + Produced(y)
Next y
Debug.Print tot
End Sub
 
K

kcc

Is 1970 being 10 years after 1961 your assumption or the way the
problems written? If it is really 1971, it's a simple 3%
non-compounded rate per year.
Assuming that, 1961 Cars = 90,000,000/sum(1,1.03,1.06,...,1.27).
kcc
 
Top