Get documents about "Answers for Worksheet
W
Description
Answers for Worksheet document sample
Document Sample
4558d58d-299c-412a-bab4-a7b5b6e5ab34.xls
Some basic points about using Excel to manage and understand data.
a. One way to think of Excel is as simply a large calculator, one that can remember calculations and can
repeat calculations on different data very easily.
b. Excel is exceptionally good at doing repetitive calculations; as a result, try to structure your
worksheets so that you can take advantage of this fact.
c. An Excel workbook is composed of worksheets. Worksheet tabs are laid out along the bottom of the
screen. You can label these tabs anything you wish. (For example, you may have a worksheet for each
month, and one for the year as a whole.)
d. To switch between worksheets, click on the worksheet tab. You may wish to click on the tab labeled
1. Modeling Growth now. Alternatively, you can read the material on comparative statics before going
to that worksheet.
Using Excel for comparative static analysis.
1. One of Excel's important uses is for testing how sensitive the results of an analysis are to the
underlying assumptions of that analysis. Economists call this comparative static analysis or sensitivity
2. To take maximum think of this as "What if?" for sensitivity analysis, it is useful to set up each
analysis; you can alsoadvantage of Excel's ability analysis.
worksheet with the data in the upper part of the worksheet and the calculations based on that data
underneath the data.
3. When entering data, use separate cells for the data and its label. Otherwise you cannot reference the
data within an equation. For example, if one piece of data is that the current price is $100, put 100 in a
cell, and Current price in the cell next to the 100. (Note you do not have to enter $100; Excel can
format that for you using the buttons on the Formatting Toolbar).
4. To enter an equation, start with an equal sign. You can tell what is in a cell by looking in the large
white area, just to the right of the = sign just above the worksheet area and below the toolbar(s) at the
top of your screen. This is useful to know, because often it looks like you just have numbers in cells,
when in actuality, the numbers represent the result of the equation in that cell.
5. The important rule to follow when doing calculations is to ALWAYS cell reference the data rather
than type it into an equation. That way, comparative static analysis is easy to do by changing the data
and watching how the calculations change as a result.
Notes
A B C D E F G H I J K
1 Put the mouse's cross over cell A1. Suppose sales in year 0 are 12520 units. Sales are expected to increase by 1.25% per year.
2 1. What are expected sales in 8 years? 2. How many units do you expect to sell total by the end of the 8th year?
If you were only interested in question 1, you could easily answer this using the single equation in Model A. This is done in cell A10
below. Unfortunately, the answer to question 2 using a single equation would be pretty ugly (of course it could be done). An easier way
is presented in Model B, in cells C9:D21. To understand the basic structure set up there, place the mouse over cells with comments (red
3 triangles in the upper right corner). Note: Model B presents a second method for calculating sales/year in E10:E19.
3. How would your answers to 1 and 2 change if you expected sales to increase by 1.5% per year? 1% per
4 year? How would you answer these questions using model B? (This is comparative static analysis.) Model C. Data Setup
5 To answer 3 using A or B requires replacing the 1.0125s in cells A10, C11:C20 and D10:D20 with 1.015. year 0 sales 12520
6 An easier way to do comparative static analysis using Excel is to set up the model as is done with Model C (the growth rate 1.25%
Notes page discusses this setup). You can answer 3 using Model C by changing .0125 in Cell J6 to .015 or
7 .01. J10:J18 do not need to be changed, they automatically reflect the new value for growth rate.
8 Model B. Sales in year Create your own model below Model C. Calculations
9 Model A. Simplest solution to 1. year method 1 method 2 year Sales in year
10 13,828 Sales in 10th year 0 12,520 12,520 0 12,520
11 1 12,677 12,677 1 12,677
12 2 12,835 12,835 2 12,835
13 3 12,995 12,995 3 12,995
14 4 13,158 13,158 4 13,158
15 5 13,322 13,322 5 13,322
16 6 13,489 13,489 6 13,489
17 7 13,657 13,657 7 13,657
18 8 13,828 13,828 8 13,828
19 9 year total 118,481 118,481 9 year total 118,481
1. Modeling Growth
Cell: A1
Comment: Viewing comments:
Excel allows you to view comments in a couple of ways (cells with comments have a small red tab in the upper right corner just like in cell A1).
1. You are doing the easiest method right now;when you place the mouse's white cross over a cell, the comment will appear. It will go away
when you move the cross away from that cell.
2. If you want to have the comment stay on the screen, place the mouse in that cell and right click then choose Show Comment. To hide, place
the mouse in that cell and right click, then choose Hide Comment.
Sometimes a comment cannot be read without moving the screen (for example, the comment to cell D19). In this instance, the comment may
have been moved from its normal location so that it can be seen without moving the screen. To check if this is the case, you must use the
second method discussed above. If the sheet is unprotected, you can move it yourself; unfortunately, this sheet is protected (but the comment
has been moved so it can be seen without moving the screen).
Cell: A6
Comment: Moving between worksheets:
To view another worksheet, click on the tab at the bottom of the screen. For example, to view the Notes worksheet, click on the Notes tab at
the bottom of the screen.
Cell: F8
Comment: The light green cells are not protected; as a result, you can enter equations for yourself to try to replicate models A, B, or C. You can also enter
new values for either of the pieces of data in Model C by changing the numbers in cells J5 and J6.
Cell: C9
Comment: Creating a counter:
To create a counter you do not have to type the numbers into cells C10:C18. It is sufficient to type the number 0 in C10, then type the equation
=C10+1 into cell C11. You can do this manually, or you could type = and then click on cell C10, then type +1, then press Enter. Once you have
C11 set up, place the mouse over the lower right corner of cell C11. Once the white cross turns into a black cross, depress the left click button
on the mouse and drag the mouse down to cell C20. Once you let up on the left mouse button, the numbers 2 through 10 will appear in cells
C12:C18. There are other ways to create counters as well; see Excel's help menus for further information.
Cell: D9
Comment: One method to model a growth in sales is to simply create an equation that multiplies last year's sales by 1+growth rate. This is done in
equation D11. This method also requires that the series be initialized -- this is done in D10. Once D10 and D11 are created, drag D11 down to
D18 in the same way you dragged the counter from C11 down to C18.
Cell: E9
1. Modeling Growth
Comment: An alternative method uses the equation in A10 with the exponent 10 replaced by the year values. With this method, only one equation needs
to be entered (in cell E10); the equations in E11:E18
are obtained by dragging E10.
Cell: A10
Comment: Equations:
To enter an equation in a cell, start with an equal sign. You can see the contents of a cell by clicking on that cell and looking at the white area
above the worksheet area and below the toolbars.
Cell: J10
Comment: Notice the $ signs:
The $J$5 and $J$6 in the equation refer to cells J5 and J6; the $ signs turn off an attribute of Excel called relative cell referencing. When this
equation is dragged to J11, the J5 and J6 remain at J5 and J6, but the I10 turns into I11 (because of relative cell referencing). If you did not
use the $ signs in your equation in J10, then J5 would turn into J6 and J6 would turn into J7 when cell J10 is dragged down to J11. You will
learn more about absolute versus relative cell referencing in the next lab.
Cell: E18
Comment: Note:
The equation in this cell is really the same as the one in A10.
Cell: D19
Comment: Note:
The equation in this cell can be typed in, or by clicking on the AutoSum sign (sigma) on the Standard Tool Bar. AutoSum guesses what you are
trying to sum; sometimes you have to adjust the bounds of AutoSum.
1. Modeling Growth
A First Tutorial in Excel
The following tutorial is quick reference to some of the most basic tools at your disposal in Excel. The tutorial goes
through a simple problem and by the end you should have a grasp of many of Excel's most basic functions. The
worksheet labeled Answers has already been completed according to the instructions on the Step-by-step guide
worksheet. First, try to do the problem on the worksheet labeled Your Worksheet without looking at the answers. The
strategies you learned in the Modeling Growth and Notes worksheets are sufficient to answer this question. Use the
Answers and the Step-by-step guide worksheets as a guides to check your work. (Do not worry if you set up your data
in a different order, or used different labels, or did not format cells, etc., as that is unimportant.)
The problem) Suppose you are the owner of a small air conditioner manufacturing company and you want to project
your revenues, cost, and profits over the next 20 years for a specific line of small room air conditioners. You have sold
10,000 units this year (year 0) and you have been experiencing a 0.5% growth per year in the number of units you sell.
You currently sell your air conditioners for $150 per unit, but you expect your price to increase at a constant rate of 2.0%
as it has in the recent past. Currently, your cost per unit is $125, but this has been increasing at a rate of 3.0% per year.
These increases are consistent with the rest of your competitors and you do not expect them to change. Calculate total
revenue, total cost, and profits for each of the next 20 years. Answer the following:
A. What happens to your profits over time on this product? When is this product no longer profitable?
B. Suppose instead that growth in unit sales is 2.0% per year and the growth in price is 0.5% per year. How does your
answer to A change? (If you have done this correctly, you will only need to change 2 numbers on Your Worksheet to
answer this question.)
2. A problem to try
Price of Unit $150.00
% change in price / yr 2.00%
Units Sold (Q) 10,000
% change in Q / yr 0.50%
Cost per Unit $125.00
% change in cost / yr 3.00%
Year Quantity Sold Price/Unit Revenue Cost/Unit Cost Profit
0 10000 150.00 $1,500,000 $125.00 $1,250,000 $250,000
1 10050 153.00 $1,537,650 $128.75 $1,293,938 $243,713
2 10100 156.06 $1,576,245 $132.61 $1,339,419 $236,826
3 10151 159.18 $1,615,809 $136.59 $1,386,500 $229,309
4 10202 162.36 $1,656,366 $140.69 $1,435,235 $221,130
5 10253 165.61 $1,697,940 $144.91 $1,485,684 $212,256
6 10304 168.92 $1,740,559 $149.26 $1,537,906 $202,653
7 10355 172.30 $1,784,247 $153.73 $1,591,963 $192,283
8 10407 175.75 $1,829,031 $158.35 $1,647,921 $181,111
9 10459 179.26 $1,874,940 $163.10 $1,705,845 $169,095
10 10511 182.85 $1,922,001 $167.99 $1,765,806 $156,195
11 10564 186.51 $1,970,243 $173.03 $1,827,874 $142,370
12 10617 190.24 $2,019,696 $178.22 $1,892,123 $127,573
13 10670 194.04 $2,070,391 $183.57 $1,958,632 $111,759
14 10723 197.92 $2,122,357 $189.07 $2,027,477 $94,880
15 10777 201.88 $2,175,629 $194.75 $2,098,743 $76,885
16 10831 205.92 $2,230,237 $200.59 $2,172,514 $57,723
17 10885 210.04 $2,286,216 $206.61 $2,248,878 $37,338
18 10939 214.24 $2,343,600 $212.80 $2,327,926 $15,674
19 10994 218.52 $2,402,424 $219.19 $2,409,753 ($7,328)
20 11049 222.89 $2,462,725 $225.76 $2,494,455 ($31,730)
Answers)
A. Profits decline over time as you would expect given that growth in cost per unit exceeds growth in price per unit.
The numbers show that this product is no longer profitable in year 19.
B. Profits now decline much more rapidly for this product. The product is no longer profitable in year 8.
Answers
Step-by-step guide
The following guide is based on the worksheet labeled Answers. Your worksheet may look
different, but that is unimportant as long as it works in the same way (i.e. gets the same answers) as
the Answers worksheet. This is easiest to follow if you print this page.
The problem) Suppose you are the owner of a small air conditioner manufacturing company and
you want to project your revenues, cost, and profits over the next 20 years for a specific line of small
room air conditioners. You have sold 10,000 units this year (year 0) and you have been experiencing
a 0.5% growth per year in the number of units you sell. You currently sell your air conditioners for
$150 per unit, but you expect your price to increase at a constant rate of 2.0% as it has in the recent
past. Currently, your cost per unit is $125, but this has been increasing at a rate of 3.0% per year.
These increases are consistent with the rest of your competitors and you do not expect them to
change. Calculate total revenue, total cost, and profits for each of the next 20 years. Answer the
following:
A. What happens to your profits over time on this product? When is this product no longer
profitable?
B. Suppose instead that growth in unit sales is 2.0% per year and the growth in price is 0.5% per
year. How does your answer to A change? (If you have done this correctly, you will only need to
change 2 numbers on Your Worksheet to answer this question.)
1.) The best way to approach this is to build a model like Model C in the Modeling Growth
worksheet. There are six pieces of data that you want to set up as variables. Create labels for
these variables in cells A1:A6 and then place their corresponding values in cells B1:B6. This
will allow you to refer to these values as variables. The rest of the worksheet is built using
formulas, in fact, EVERY cell below row 9 is a formula except cell A10. Formatting help:
If a label extends past the width of the cell you can widen the column, for example column A,
by double clicking on the AB border in the gray part of the worksheet above the first row. You
can also change the width of column A by clicking on the AB border and (while holding down
the left mouse button) dragging the mouse to the right (or left) to make the row bigger (or
smaller). This option allows for more customized column widths. The same holds for row
height (an example is the row directly above this comment on this worksheet (row 7)).
2.) It helps to format the data in cells B1-B6 to reflect what type of data they are (%, $, etc). You
can do this either by clicking on the cell then right clicking the mouse and choosing Format
Cells or you can simply click the cell and use the Formatting toolbar (that has ($) for
currency, (%) for percent and so forth). You can add and remove decimal places as well. It's
important to note that for percents if you do not have a decimal, Excel will round the value to
the nearest whole number i.e. 21.845% will appear as 22%. However, if you click the cell, the
value will appear in the text box under the toolbar in an un-rounded form, and you can increase
the number of decimal places shown by clicking on the Increase Decimal button on the
Formatting Toolbar.
3.) Decide where you want the calculation area to begin. A good choice in this instance is to use
row ten because the last digit of the year and row number will match.
Step-by-step guide
4.) In row 9 place column headings (Year, Quantity/yr., Price/Unit, Revenue, etc.).
5.) Create the year counter by placing a 0 in A10 and setting A11 to the following equation:
=A10+1. When you drag that equation until you reach year 20 you will notice that A10
changes to A11 and then to A12 and so on. This is the notion of relative cell referencing
discussed in the comment to cell J10 on the Modeling Growth worksheet. Remember, all
equations in Excel start with an = sign, and to drag a cell you highlight the cell by clicking on
it, then move the cursor down to the bottom right corner until you get a black plus sign, then
hold down the mouse's left click button and drag.
6.) Set the value of cell B11 to the current quantity sold which is located in cell B3. Instead of
typing the value 10000, you should treat B3 as a variable and hence set the value of B11 to
equal B3. This can be done very easily by the following equation: =B3. Do this for the current
price and current cost as well.
7.) Revenue is calculated as price multiplied by quantity so set up that relationship in cell D10
with the following equation: =B10*C10. Remember all equations in excel start with an equals
sign. If after entering a formula you get the following: #VALUE!. It probably means that you
are trying to use a cell in your equation that does not have relevant data in it. If you get the
following in the cell ######, it means that the column is not wide enough to hold the value so
simply widen the column (as discussed in step 1) or reduce the number of digits to the right of
the decimal (as discussed in step 2). A similar calculation is used to obtain cost per year in cell
F10. Finally, profits are calculated as total revenue minus total cost in cell G10.
8.) To calculate the quantity sold over the next twenty years based on a growth in units sold a year
of 2% we need to use the following equation: Quantitylast year * (1 + 2%). The year zero sales of
10000 units is reflected in cell B10 and the growth rate in unit sales is located in cell B4 so the
equation in cell B11 for sales in year 1 would look like this: =B10*(1+B4).
9.) If you dragged that equation for B11 down to B30 you get errors. The problem is that the
equation in B11 is not referencing cell B4 after you drag it down one cell to B12, it is
referencing B5 (you were using relative cell referencing). You can verify this by looking at the
equation in cell B12 (the same problem holds for B13:B30). In order to fix this you need to
have each equation reference cell B4 for every year that you calculate quantity sold. To do this
you need to use absolute cell referencing (as discussed in the comment to cell J10 on the
Modeling Growth worksheet). Place $ signs in front of the B and the 4. The new equation in
cell B11 will look like this: =B10*(1+$B$4). Drag B11 now and you get the right answers for
units sold across years.
10.) To calculate price per unit and cost per unit for each year, you simply need to change the cell
numbers and letters for the equation that you used for calculating quantity. See cells C11 and
E11 for these equations.
11.) To calculate revenue, cost, and profits for all twenty years you simply need to drag the year 0
equations for these quantities down to year 20. You are now ready to answer questions A and
B.
Step-by-step guide
Related docs
