When we were evaluating functions of a single variable we had to replace the variable with a cell reference. We do the same for functions of several variables. We simply have to use several cell references.
Find the amount of money I will have in the bank in 10 years if I deposit $1000 and the bank pays 5% interest, compounded quarterly. Set the problem up in Excel so that I can use the worksheet for similar problems with different numbers.
At the end of 10 years, we have $1,643.62 in the bank. By simply changing values in the worksheet, I find that compounding the interest annually reduces the final amount on 10 years to $1.628.89, while compounding weekly increases the final amount to $1,648.33.
In this example we have four variables. We could vary the initial deposit, the rate, the number of periods per year and the interest rate. In previous chapters we have a variable (say q) and the function (such as profit) that depends on \(q\text{.}\) Such a function might be written as something like \(\profit(q)= -3 q^2+500q-1000\text{.}\)
where \(\operatorname{FA}\) is the future amount, and this is a function of four variables \(d\) (deposit), \(r\) (interest rate), \(p\) (number of payments), and \(y\) (number of years).
Find the revenue for 500 widgets if I know that the demand price for 100 widgets is $20, the demand price for 200 widgets is $18.75, and that the demand price is a linear function. Set the problem up in Excel so that I can use the worksheet for similar problems with different numbers.
To make our worksheet easier to read, we use named cells. We first have to find an equation for the demand price formula. We compute a slope and intercept for this line from the points (100, 20) and (200, 18.75). Once we have this function, we find that the demand price is $15 when quantity is 500. We then compute revenue as price times quantity.
The next task to consider is making a table of values for a function of several variables. Since our screens have 2 dimensions, we first look at the case when we allow two values to change. When we made a table for a single variable, we had to use both absolute and relative cell reference to distinguish between constant values used for all entries and variables that changed in each case. With functions of two variables our table will have rows where one variable is held constant and columns where the other is held constant. We note that quick filling a formula with the reference $A5 will keep the column, A, constant but allow the row to change. Similarly with the reference A$5 the column can change, but the row is constant.
I want to produce a table that shows how much I need to put in the bank to have $100,000 at some point in the future. I will assume that interest is compounded annually. I want interest rate and number of years to be treated as variables with interest rate ranging from 5% to 6% and the length of time to vary from 5 to 40 years.
As we build the table, the future amount will be a constant, so it needs to be given as an absolute reference. The number of years will be down the left side of the table and will be constant across a row, so its reference should have a dollar sign before the letter. The interest rate will be listed across the top of the table, so its reference should have a dollar sign before the number.
This lets us build the table we desire. From the complete table, we see that we can have $100,000 in the bank in 40 years by making a deposit of $9,722.22 at 6% interest. In contrast if we only earn 5% interest and can only keep the money in the bank for 15 years, we need to start with $48,101.71.
Similarly, we might want to produce a table that shows the monthly payment on a mortgage where both the annual interest rate and the number of years are treated as variables. Such a chart would be useful in deciding how big a mortgage a person can afford with different kinds of mortgages.
We can see that the monthly payment is $421.60 for a 30-year fixed rate mortgage at 3% compounded monthly. For a 10-year mortgage at 6% the payment increases to $1,110.21.
After we build a table for a function we would also like to see a graph of the function. Excel’s abilities to graph surfaces in not one of the program’s strong points. Nevertheless, it is useful to be “able to see the big picture” by looking at a graph. We will also note how to draw a graph of a surface with Wolfram Alpha.
When we looked at models of price, quantity, cost, revenue, and profit, we made the simplifying assumption that a company only produces one product. We want to consider what happens with two products.
We need to start by producing a formula for revenue. To shorten the equations we will abbreviate the terms or use initials. We need formulas for revenue for each of our products:
Finally, we would like to see a graph of the function. We notice that the 3D plots in Excel have a number of drawbacks. The plots do not label the input variables. These first plots also don’t tell us what values of the variables correspond to particular points on the graph. Some of these drawbacks can be overcome, but only with more work than we wish to expend in this course. We will only add one non-intuitive option to make the graphs work better.
We will move the names of the variables out of the upper left corner of the chart and into the row above and to the side of the data. We leave the corner cell blank. This will let us see the values of the variables in the graphs. In the table, we select the data we would like to graph. In this example we select from cells B4 through M12. Finally, we select a chart to insert. The charts we are interested in are surface charts. The types of interest are 3-D Surface, Wireframe 3-D, and contour. Each of these chart types highlights some useful information.
The Wireframe 3-D chart emphasizes that we can build a reasonable picture from the curves obtained by treating either \(x\) or \(y\) as a constant. It lets us understand a function of 2 variables by putting together a collection of several functions of one variable. This point of view will be useful when we try to take derivatives.
Another alternative for seeing a graph is to use Wolfram Alpha. Unfortunately variable names in Alpha seem to be limited to a single letter, or a letter followed by a digit. Thus we change the formula to one using the names g and w.
The techniques of this section let us look at functions of two variables. In the next section we explore techniques for understanding functions of several variables by treating some of the variables as constants.
1.Reading check, Evaluating and Graphing Functions of Several Variables.
This question checks your reading comprehension of the material is section 6.1, Evaluating and Graphing Functions of Several Variables, of Business Calculus with Excel. Based on your reading, select all statements that are correct. There may be more than one correct answer. The statements may appear in what seems to be a random order.
In the text, the demand price of an object was discussed as a function of 5 variables, 2 given demands with associated prices and the demand quantity in question.
ExercisesExercises: Evaluating and Graphing Functions of Several Variables Problems
Exercise Group.
For the following exercises, set up an Excel notebook, and evaluate the given function of several variables at the indicated values. T he workbook should be set up so that the input values can be changed and the formula will re-compute with the new values.
Setting this up so that the values of \(x\) and \(y\) can be easily changed we define \(x\) and \(y\) in separate cells and then enter the function that computes \(f(x,y)\text{.}\)
Express cost as a function of quantity, initial cost, and per-unit cost, when the initial cost is $2,000, the per-unit cost is $25, and the quantity is 75.
Express revenue as a function of two quantity demand-price pairs and quantity, assuming that demand price is a linear function, where the quantity demand-price pairs are \((0, \$20)\) and \((100, \$18)\) and the quantity is 300. (You may find it useful to have intermediate computations that find the coefficients of the demand price function, and the demand price.)
Express revenue as a function of two quantity demand-price pairs and quantity, assuming that demand price is an exponential function, here the quantity price pairs are \((0, \$20)\) and \((100, \$18)\) and the quantity is 300. (You may find it useful to have intermediate computations that find the coefficients of the demand price function, and the demand price.)
Alternatively we can use Excel. Remember that if we have only two points we need to add one of the pairs twice so that Excel can graph the points correctly in a scatter plot.
The simplest answer would be to treat Price as a function of quantity. But this is a section about multivariable functions. So what if we wanted a more general setting where we were given the following:
Express profit as a function of two quantity demand-price pairs, quantity, initial cost, and per-unit cost, assuming that demand price is a linear function, where the quantity demand-price pairs are \((100, \$30)\) and \((200, \$28)\text{,}\) the quantity is 300, the initial cost is $3000, and the per-unit cost is $8. (You may find it useful to have intermediate computations that find the cost and revenue.)
Express the future value of a deposit as a function of the initial deposit, the annual interest rate, the number of years the deposit is held, and the number of times per year that the interest is compounded, where the deposit of $10,000 is held for 20 years at 3% interest, compounded monthly.
For the rate, you should either make sure the cell is formatted as a percentage. Or you can just enter the decimal 0.03. The display formula allows you to check that the number is formatted correctly. We do want Excel to use the value of 0.03 in the formula, not 3.
Express the future value of a regular series of deposits as a function of the periodic deposit amount, the annual interest rate, the number of years the deposits accumulate, and the number of times per year that the deposits are made, where the deposit of $200 is deposited weekly for 20 years at 3% interest, compounded weekly.
Express the current value of a bond as a function of the final value, the annual interest rate, and the number of years the bond is held, where the final value of the bond is $10,000, held for 15 years at 3.5% interest, compounded monthly.
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.
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{.}\)
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{.}\)
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.
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.
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.
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%.
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.
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%.
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%.
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:
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:
