John would like to establish a retirement plan that returns an amount of 100,000 in 20 years. Build a spreadsheet model to calculate the amount John must contribute at the end of each year towards his retirement fund, assuming an annual interest rate of 6.
Use the Excel function
=PMT(rate, nper, pv, fv, type)
The arguments of this function are
rate = the interest rate for the loan
nper = the total number of payments
pv = present value (the amount borrowed which is 0 in this case)
fv = future value (in the formula, indicate this value as negative as the future value command assumes a stream of payments not deposits)
type = payment type (0 = end of period, 1 = beginning of the period)
Also, construct a one-way table with interest rate as the column variable and the amount contributed at the end of each year as the output. Vary the interest rate from 4 to 7 in increments of 0.5.
Q. 2An electronics store sells two models of television. The sales of these two models, X and Y, are dependent, that is, if the price of one increases, the demand for the other increases. A study is made to find the relationship between the demand (
D) and the price (
P) in order to maximize the revenue from these products. The result of the study is shown below.
DX = 476 0.54 PX + 0.22 PY
DY = 601 + 0.12 PX 0.54 PY
a. Construct a model for the total revenue and implement it on a spreadsheet.
b. Develop a two-way data table to estimate the optimal prices of each of the two products in order to maximize the total revenue. Vary price of each product from 600 to 900 in increments of 50.
Q. 31L6zGgj6B54k6bpp5jrlc7pztFEMv
aI2cE4oPB7A6VjKH8MkvmKfM6GHTw
byBruaXQWjANH6RTz3t/3c4z/u+IhkvE/BlAD7sKJ4dGVFJxv7nEGIyQJv+5Fbcq0P8bFE33jB7TD0YtqS85F0eJLOj2toiBVyRKc65/MRV1E3RWQuW1bRl6gUoo2G0OGRKSEdNAaIzp7yW3sc2Ims3dtgksHXzQl21ATQ8eLMgzpioAxkIiC5czhzCnElDiLF4WbWof798PdXSjgy7zqKYRGXFHbQxD4XhTXQ5ORipoPAK2zhMUCKNTW7EX1TZnn8EXJHIZZXJRNmXD/GL5sKkTIlyBSR1AsuwsVNeXwciTZ5psgFeUdIkbRd/2AZcfwG4SJQhx5goaD+jLPcFZZWXclVNS/o+7i2puM7wOKAYYmU0zoUEkThamaA2SdAz/n7wL9QAFUpNLylQbEPNmQBjP5o5r/BOjDNvcLEB1OeASlCi9K6FBr517U0MP1F3lNpFr2OveiVgN96PAsjLu+ESCDjtCB/1KlaAAdL8pG3aV4UTak4THqblPA96HbdBp11/SfOepuT7FWbXcYV7FDV4C56T2CR6Tg7y4W/2MAcyF2dRycFdDHLryosX5GL4rMP8MT7VF9wFYVSs9FTdPQlpJiYeBLeuaK5B+8qEEPa9j6p3NR8KLY0YuqITtlObTZ4eBLmu+m0YWyIeZkKrDRqSeoKBvY26goaKuph5xiMsY+EOBaRU0tvCETnGJq89BE3c04THfz2+P5XJTBlMngzOe4D7TBcUGtscsgtygii0acNX2ThEdtQUH5WNI1MKKP4rWK4W8dnY+1F9XVNDMRi+gQsFhPR1SZwtezXKcgeDK9HEnb8KLqBA6NCXGyDRrTCymuB0DxpQ7B2axEnURoVPgDcL2orYsKNk208qJQAU70xU3RGBseBGkzdKXYw1uWEn4lhVkMGHwPW2cFChsfzrHM+yI6eFk3kKnIUnv8oQr94Cvzjzch3hmPoCWhotAweqAPncHMRZVmLqqrMvRuKK8V7/4xF6WjJFPgwfh0ou0iXpRVUTak4VAEXljo+1comhSkzDrqrtDTnGOFtp9VVAOf9tyLojNSyw/dGQ+RjP8ZTF0FZiVnXAX6inF2PtDXkhd1bah8W6b9Mc6f6MPvQy1BRbXQE6DW2MY0K0BKBworU9BQFDoOncbudToXRdGJmLIdaqhxa7SNVoQ+DUqsoaPuatE5dkqES0hDE3UXuseLjG3YsmCJunsMDG+i7g6likJr2XW58EV8+TTs8Yfuiyu8mto5dguB1mQboWdNrDmglCxSFfS+8Odg+R+9CoP1fFKZRGdXRCPIOqAqAFc99OcAdEMJ6xhyn8pPlvqFlR3qcKsWPVng4rIbr0F+6jwXpds0IDN91WVzGZg5syOmmtHTaGajh4Q1A3fkSuj9jnvrA8LQg/fRti3dnCpnHvzLfDN0DVkz4RL3vYN7kTQ06bhE1KTBz0817oKbSMA1jwAom6aupIjivKa3NaaPtsoEg+fUdm0d0xNIetBvxaPLJ/riAgVdhR7sRRS30wxcR6ohZVbnevC6L4UnYnODoKICrYRwTQnYAAVO07F+Wveo7h/2vom6i5tkTZxBcvPUDHVCJnOcKjQZ7IJLFQVdCH7d4E59E1oqbnUAh68AEtXbhypHH0RfgjFkiqkHVakf+GkF00gXgnh93+TSC6OioehmuuIJbsKU72I1F4W0a8uyyhIZMkY2cE0vb9R5wrnMyjJPoaDikualdH39gag5mYvK6UHhJMuLIuewn5UZNRoyEUVc0eBPkmR6kCoVnn0sbew486xt2BVBRLY5PNMwiFLUl9H7ziMVRVF3bXDeqc38iJytD4qZ7TGVUcXQ20XxpYzN5ELIu4KE8NeoRSF0A19lBV0EPTlB10jKNxK4Y6mM4CAZ13toa5TwcH+IZFFU3dyNYChBFM4bPdyhSCSoqTg9nkC7dnmw25u719ZlkUON7QLUKagI3r33/o7NAs2W52V9MoK3YKjUYX+g88zpEXmun9NrMyUV/Ra8hJDxs6FtnI25EBwZ31MsXTp0xw/vPOumqU8Fj7FvkSsG0/5sjLGX9KTe+REtYKZ4OxZnNHqM3iVo1MtMmYSCOo5gKPtSu9+EeMe5KD3EV2QJPGXzFlQc5zps9VQXqS6QcV5B7655hH1P5qKGNqX3omiAjol4Nac2lonQ5dI8Ajo0WZzbh/WbMknsuy9TmSkzt9eVia4fJ2mc6uZpyjipTLWxwN5mZGJoEnrvgGdFhdY0jyitMTQ5mn6zY/w1btJA/58YasjhiHNB79fhT6K7ytgminqRgTLvmfS1FKhDJaiZnD6QZfB1mXZDrLwo2HRdm9FzpxmANC8b4zA1Za7H6PIK+ukscujpXNQENUzvrxBO37Icy5QKk8zOqNPDafatqbGpy8ZYymNfVZVxM5syxXGyrKyaWsd0HauqsIOkY1dWldkDv4gDJxkZnHV7NtpK0D/0gC7+LV71TaHvjn0LSGOCnUyDeGm2nD6ZzqiFtqB7TG8C2C/OMPVFStWSfH5+ASWZ0X30bLH+KToGRBpu7djV2JjbLU6vvE4EKVZkuhWx+6IRhzaj4+jz2b5iiGY6cpLa5zDwg3WRmUaBb6H3haq5nDaa6hLqcvug9IQn9Upz5NVz9fqBWpRnVgr/G+jDNvcLLJpmYROYYj8aKAfD6UODE1R4Ut+t0XcFEIm4h3bD4aFAp7W5O+J8Lor4QyFPqVvgY/mDnCnR78mv6+Mrt0bfJuieOWq9Am5CvPM53e/mz+aiHFZ4iGR0uMRDZNrFE33fTU/nohwWOF69Cm7SUjQIgQP9NMUHSs55UZtwpt6TAD3V5u6IddTdn6SglvOiNoE746j1ErgJ8cACfL4Xa22d77pabxCjzNigTl0egHj54b4uf9O8Efu2XGds7/9LvNlwn+AJpT/J1w29YosTNuftYEDjPg6vgM2Wsvz4MqBjwAI9WPfDFEeAusKhDPNdalLTHJ/Xcel9UqOf1iV3wFvXDz0tZP7jdISicl7UJQyz7IbDswJMA/Hsxi8AOuA42sxcP0/01Ty6inks2B7OYQXcHZtzeCi0erq3TDt/L+q7eVBr/USfwwLHq1fBTVpqZsRP09E90beNh4hFh008RKadr9H33TxUVOPmorZgfGK74fDEuAnxjBe15ohhh8mvyslpsyWn9bv1e1EOKzxEMjpc4iEybeOJPv3cuSbSulyf3FlNXX7yXpTDAserVwH6sM39AkSHE17Q1GwLnbMqJ1oNvS1cas6p86I2AZGI+2Y3HB6Kh8i0y/eipq6pi7Lp+oU/mmxNVVTN0eib6+NbUM6pqEtone6o9QK4CfHWPBoouFqXJ7TYLq0Tb227qe+aVK9+YZZoOeWRey/qKh4iGR0u8RCZduJFAU2Z8SjwaB0w2gSvwLy6SKLAD0QG628Zu7Cpey/qCnCnbM7huXGTljoygjjT5wkFP2NCUNBAGtyjpQdTHeuMCVq7aNZbRx45L2oTWio6U+8p8BCZtl6jb+rbSikVJ4ld6Vxro67OJZXFXKUdzMOlvv6gjpuL2gQ5pI5ar4CbEO84F0Vah5QNQCud66i7mi96iK9tlBQUlMOopZlHyLu5qGt4iGR0uMRDZNrpXBRlpr5Zou5avtE6zWUq4rQ1rtVSX1dwc1GbcLx6FaAP29wvMNPHkghkHinqrqB4UZomKB+nj6Grldryotxc1BVAJEIw2g2Hh+IhMm1jjb5jvKiFPyOpqKMXtaoPNrq5qE24uahXwU2Id8Yj8AJeU2Kj7s7aCIVtLRcv6qS+m4u6iodIRodLPH4uitLxo68LLtdeFOmtlRd1av25uagrcLx6FdykpY6MsLygJyZixSmkIfK6nLyo9ooXBUXlvKgtaKnoTL2nwENk2vl7UbMXZQPD23LrRSXkRRk6LfXdXNQV4M44ar0EbkK841zUQCl4NA49zUXpkMHaxaIwHFPfwIsqW4omsOYR8m4u6hoeIhkdLvEQmbblRTXGi6JnzU259qISHtNAHzZO67u5qG04Xr0K0Idt7hcgOlhGTFBOZU5BuVgUchmna
Q. 4dmCOH1boFDJKIqEjDOKakgW3sIm50
VtAiLRzUU9CR4i09ZzUfREXxIrCtC
ow3qmed1P9PSEguEneMSYEBTvbpjH
KMgGdHNRV+Dmol4FNyHemkfD0Dd1XZRVVTclUFGcbeituqqwgVL8LysaNV/xyM1FXYWz9p4ETzAXNdBClrT0svmAdjSkTnm4SgC0UW9e6V32cnNRV+B49Sq4SUsdGUEpDTaQ+T+n+AmU0+jfNEfddXNRX4OWis7Uewo8RKZdzEXZkYfVnJPOHMt1ybq+m4vaBO6Mo9ZL4CbEs0z5eTq5uahruEkDOfweD5Fp8KJI65AGAlW+n0fGzUVtAnfG5hyeG+jNNvcLQEWZsb4fp6CRU1GXgEx0VHoSkNy/e1O86QE9M6z3k7Tvh6ZpkRrt6tIlRXMa2fdJHZc+Qwpj67L8u6gbHepTx/n8SR5pjS29eB8ZfzqF7vq/z+MD/X1Z7vL3zyOzLgds7/9LvLXtp/Gq/5XHBwSDrgKx6ZR16vL4Y9rS5HWyUcflH55HSi11UW758WXYoW+i8Y9SNxd1BWgUwG44PBQ/4MXv8aYZQmN3M1u+lwefoaVAcns8hxkk8By1XgE3IZ6Zi1rmaH+Q79xc1BU8RDI6XEJbcfeWaRfvRX03de9FXYHj1asAfdjmfgGiwzWOfC11XtQmIBIhGO3GJbTfa/MOf4yH3OrzNfo28+v0vI57L+oKnBf1KrgJ8TZ4RD1gVl16Ex+U0YfGIU7ru/eiruOTBhqbVKnE6aj74CEy7XJ1CcOfdQllYOKtN9ffwvTDd/Z4DjO+K/iutf15+dhmRX126Pv3m/8SbqKiTnlBJOrbKo7TykQvJP3UlznkKZDQyhKfvxc19VksDGRS9I9u3rZIVFbZjalLY7Ou0z1AQvFK9+7KhIW+5/k8q23Rfw19nsRx0dotYIBO1t0iTru527ZlpotEnJWbd6opzD5CFY0pGZpcF3CZ1csuXZVyXarWv7jCTZjyXZy+FwVqDV2eSCYzHdKQWNQ2ZSwkwLksalRc1Ufu9L2ovkqiIAijiJZ+iYsHXNAJ+pgHsrC074o4EllvNv4euDPXqHWGJuN+EDCGexYsnaNOBd1H3EnG0PX0/R2KIk0TxVSSJWle4rqmXAV+EKJOGEZ5Y+93X6ecdo1CJsru0eLt6XET4mkTzvpDyFFoUBZ6S9RdWHJVjkarm7ZIFYtEQaprXgNT73UyF9WVobeH+GjbJuFBJPPHUqkrlXcIct2XqpgdAnXPbrXZQF2hIsYremKrSeOkGf5r/Xzqa8X93fubp0pbNDQq9CFXmwa9wg90r5igqOO4wl2ocx74l9qlTETEVUmPi7ZtT3dybLIoCPVhKhGGLCEF35cxhFBet02TsyCU+YaW+rpMuyFWXhQUUt+mipOoFLGJuoviqsjKBgTs9aXG6Aq2vt3rZC6qK4QXClokc2xV5PP0wdZNmwsv4A3u6tSgTSEY7Bd/j01ebaKQ1NvQ9mOden6gVSp0D4sSsltP+kRXBPs3YB8orWsHFR141qBOm4lDKGjXvmS+n1T4fipjtEH8cBv8yYE+bHO/wKJptJ3X51mSFVWsRHaMF4Xv9UqyQwuSJWWnXat5r7O5qL6CvWKNqyb1g8g6ziMt9JfDPNGbbVMtpvRIT9oaGhJn87xozHcTjtz1XVPmKJv972lo2tmiGbu6nX93aIvsePw1ChkEPBv6mvthOu96B6D7QzDajRUKmHCL7P4vAj6NTIsc1mZSmBJokTCQtqn6MiRZcXJnKhUyeeJITW3OuCQBeMRUyIjNt26qE3ih7TRmDGrJHruD2IzU3CeO+LpMuyHO1uhrirKuq0KqGH2fqAX2aCKRxQctzWXZ2nIUapvxZC4KKipgymxUceCLzNycJlfww4TUTsxQp1k+c2CosqzWN5qahBYDTGotU/F7tJ5ZTuMdMqtM9aEpssr6qm2V5Y11iur18U8wxKHHkqrORBDddcz66+O2hVrI1skoiLUYyWEjpvZKZ4xVkQgeBeh08MVrXOsQM1+YjtUV8Ljq4WMoVRjFtj2GCv04h8ngcB3ozzb3C6x4RLygVY767jxeFKUfQ1cpGl+B6rLlxvc6mYuCnREGGWmCqUyiQCTkJAx1rHiSZqnkTCb9OCTMD2Mz/jakLBQZLLA+Szjc7DSBHBJEpamT4T7gMk1gYwaRKjTZ8oBFpWZLX0qfS8rCbNfHTyTD8c+11FALeFLQU7E+wh2x2UB9qfYHP16NU/0nUadROKsoOI5haGxT3BSyFZLVeCsEpgiDmAZXjmhzCTcpVlqKyrSlGwm7NlTz2NI0lDxkVdPIEPLG3uexSSFlThUb4Rnmouj355XOtSqay/FNUyRc4CIvvajVQB+6e8hrsK0pBQ8TTQLoJ5Fk8DKLmDP077GOPD8x9lxfBD5DLxuqVKgYviiUDdxS3L+xjn3vwJOirgsW+sbxhE3giYR2hGSPg1D7GdjFHD+HdarOh0Rgd7LDfu+xOw95bQu+qYOSYQvgrQ7Q5WEgUognXABEiOk7dQqvj0bwoHi0qCL0fT91tUpLCLWefPYxZlDAJfbFvYUVhDqkosLZ1Boq5oWmYzpcw01U1JERlhcb8aI0j8YqIw3VznFtFh6deFFDzfydj/aP0AkUOglQoYnRT6g2mtVPmwEyi/GUzn4oyUCB/ivjIJKk/saOBoXSFqJLMF+V+hBNCvuGLJa+jDgZNLQrbEkRo6yKWSRS0q/4lhh6FH8GTRy8vR1ID94RWipuMhfMjv2DD3eTJ/9Zd6qGdxNbFQXfmvm+gnsMWVEl/i7INLW7MiWZAl2k8rMhkyYT3j7K4HjUkCghzPQeHSMKxayi4EbBeIczwMOjczy2GQtYdd7+D/OiND2Q6g+sv614UeBbLcEGGp04rX86F9UV0jt4EW6XT74LFU2NgBZSWVmW0HHeAcp5ghQVegywhe2n0AAjbl/IY1oWOo/9vZ912C8JuDBqhe6YzCiT80ClphWKOITwhiuL4/M4p1318eexjAW98t/ePHVnXwJ35gq1zgERsTv4nHPIoyCiIVZTjt1xf2EH7f2Ixu02ADsapmSIfbErEzntOjQipBueA7gh7+HisTps4ibEO85FLZTpWgoMr72ohUdtmcIw0RNRJzxC/mQuCl5UQEpopEkpY89NmQj2HrU1o/+STC7STBxfkq7SIxZNytGXUAEiC7Uy9Jux4zy0MwtdEXJGggg+N2emUw2VCmSMvkbH133JHv/MshkqEXoB1KacJea98GkDTV2VBN7O6uD/HCryoo4KmIayImofJdBBRGaMFy0o0JtyGtjXJssM9AcY8rZkKAMvLNsWpsjiRaFQhBFUmAyjlYpKoaLmrSO+LtNuiMsn+siLmlWUKaFnKFLBJIx35M/rn8xF9YUMmMKljU3i+WGB4rEOPS+SMZAkaZbTkN0IqcyhaQa4FCnRb5ChHzCqlFClAtwZ6yQUyhgFTc7DmFwEGkhUpKuAMiH/AvUCz9O76uNfPO3WZHBkOYd7sjUB+HfAjbK5NSZ6REc/1qVwwgrO3/hRxmEU216YiyA87WSkYsmJnLvUCQbFPEl3C4AmPnA9Njj1TarvZJ7CzceuW2fiMAN92OZ+AaLDCS9oRirRc1FgtSnpmxJ2Hg3xUfc4HY3YmosybQ4n2+PU59E3zMz2CmMueZJXWSLNsESdMM2sFWCy8DDTozZTm0MFkejpS+gqQ5WxNl4UqcCL4y+YchlFSTl0JfPNCOSdAJGoB3c+QyZ8M6Dy30MDL2rTR+wL2Drn7dBmQaTbd0abCz+KrX0L55t8o4F64dzQIxR8AE9ghCqMrSTBYeBZzXutsC3T/hjn70WhL2gVBVueFCbK8V2RSBmToMO3tvJcnwzD87koM8pEY1BRCq1AXk581vOnPhY8KzKpYu3c0Kg6DUqsABUVcLmoKKOZupz7MqUi9EtoQ+1F8cBPzmZtFnRlFETwfqc68eGL3NHS0gbHBbWmoa0rCiFEoYPKqiaXCSpqmfhtM+7r4ebjvlPDfC/edKNwG5m/PLJIvVGezBPgHtKo6cVZOKxxE+KteYQcRa5pKpi6Kq+he2Db0dB3FMqsgklHA