Exercise Group 6.1.10–17.

For the following exercises, set up an Excel notebook to produce a table for the given function as a function of the two specified variables and the other parameters. The workbook should be set up so that the input values can be changed and the formula will recompute with the new values.
10.
Create a table for the function \(f(x,y)=3x^2+xy+5y^2\text{,}\) with \(x\) ranging from \(-10\) to \(10\text{,}\) and \(y\) ranging from \(-5\) to \(15\text{.}\)
11.
Create a table for the function \(f(x,y,z)=xyz+x^2 y-y^2+5z\text{,}\) with \(x\) ranging from \(-10\) to \(10\text{,}\) and \(y\) ranging from \(-5\) to \(15\text{,}\) with \(z=2\text{.}\)
Solution.
We need different types of absolute references. If the \(x\) values are entered in column A, and the \(y\) values are entered in row 4, and \(z\) is stored in Cell B2:
  • When using \(x\text{,}\) we want to fix the column reference.
  • When using \(y\text{,}\) we want to fix the row reference.
  • When using z we want to fix the cell reference.
In table form we get:
We can always check our work by double clicking a value in the table. This will highlight the calls called upon to compute the entry. Note that this will check if you are calling on the right \(x\text{,}\) \(y\) and \(z\text{.}\) To check that the formula is correct we recommend the Show Formula feature.
12.
Create a table that expresses cost as a function of quantity, and per-unit cost, with initial cost as a parameter, when the initial cost is $3,000, the per-unit cost ranges from $20 to $40 by $2, and the quantity ranges from 50 to 100 by 5.
13.
Create a table that expresses the future value of a deposit as a function of the annual interest rate and the number of years the deposit is held, with the amount of the initial deposit and the number of times per year that the interest is compounded being treated as parameters, where the interest on a deposit of $10,000 is compounded quarterly, and the deposit is held for 20 to 40 years at interest rates ranging from 3% to 5%.
Solution.
We will enter the deposit and the times the interest is compounded (ppy) as fixed parameters. The years run from 20 to 40, and the interest rate from 3% to 5%. We have enough space on one sheet to do the years in increments of 1. The interest is done in increments of 0.25%. This is something not uncommon in the banking world.
How much detail should you give? Imagine working at a bank. What would be a good spreadsheet to share with a colleague, boss, or costumer? It should be adequately labeled and it should be easy to read.
14.
Create a table that expresses the future value of a regular series of deposits as a function of the annual interest rate and the number of years the deposit accumulate, with the amount of the deposits and the number of times per year that the interest is compounded being treated as parameters, where a deposit of $2,000 is made monthly, and the deposits accumulate for 20 to 40 years at interest rates ranging from 3% to 5%.
15.
Create a table that expresses the current value of a bond as a function of the number of years the bond is held and the interest rate, where the final value of the bond is $10,000, the number of years the bond is held runs from 5 to 40 and the interest rate runs from 2% to 6%.
Solution.
The entries in the cell look like this:
We can easily read off what our initial investment needs to be if we want to earn $10,000.
16.
Create a table that expresses revenue as a function of the quantity of widgets and gizmos sold as both quantities range from 0 to 1000, where the demand price functions are:
\begin{align*} \PriceGizmo\amp =50-\frac{\QuantityGizmo}{40}-\frac{\QuantityWidget}{300}\\ \PriceWidget\amp =40-\frac{\QuantityGizmo}{400}-\frac{\QuantityWidget}{50}\text{.} \end{align*}
17.
Create a table that expresses revenue as a function of the quantity of widgets and gizmos sold as both quantities range from 0 to 1000, where the demand price functions are:
\begin{align*} \PriceGizmo\amp =60(0.9)^{\QuantityGizmo/100}-\frac{\QuantityWidget}{200}\\ \PriceWidget\amp =40(0.85)^{\QuantityWidget/100}-\frac{\QuantityGizmo}{100}\text{.} \end{align*}
Solution.
Let \(x\) be Gizmo, and \(x\) be Widget, then
\begin{align*} \text{PriceX}\amp =60(0.9)^{\frac{x}{100}}-\frac{y}{200}\\ \text{PriceY}\amp =40(0.85)^{\frac{y}{100}}-\frac{x}{100} \\ \revenue \amp =x \text{PriceX}+y \text{Pricey}\\ \revenue \amp =x 60(0.9)^{\frac{x}{100}}-\frac{y}{200}+y40(0.85)^{\frac{y}{100}}-\frac{x}{100}\text{.} \end{align*}
in-context