The question is attached to the attachment section.

Note: Need it within 24 hrs after I post the question.

Assignment 5 – Excel Exercises

Upon completion of the assignment, save and upload your Excel File using the Assignment 5 link in eCampus.

1. Plotting simple functions

Open a new Excel file and create 3 worksheets as described below.

2.
DEVELOPMENT OF LOAN AMORTIZATION TABLE

In this exercise, we will learn how to use spreadsheets to solve simple accounting problems. We will also learn how easy it is to use multiple formulas within Excel. The handout is prepared with instructions to be used in Microsoft’s Excel 2007. Some of the instructions are similar if an earlier version of Excel is used; however, it is required that you use Excel 2007 or a later version for all the exercises in this class.

Loan Amortization tables show how much you owe a bank every month when you borrow money from them. This specific problem is a useful practical example adapted from a problem discussed in Engineering with Excel, by R.W Larson. Let us assume that you recently graduated and bought a brand new Ford Mustang (with cool white stripes, of course!). Thanks to government bailouts you got a nice deal of $25,000 at an annual percentage rate (APR) of 6%, with no down payment. You want to pay the loan within 5 years. In this exercise, you will create an amortization table (monthly basis) that shows your payment, how much went as interest, how much principal was paid, and the amount you owe to the bank. The primary concept in preparing this table is that, each time you make a payment, you have to pay interest on the outstanding borrowed amount, called Principal. The amount that exceeds the interest amount paid reduces the principal before the next payment. Before we proceed into the detailed calculations, we have to set up a spreadsheet for your loan. We will design a general layout to organize information and make it easy to understand.

Open Excel and click on “Blank Worksheet” to open a blank worksheet. On the bottom of the page, double click on “Sheet 1” and change the name of the sheet to “Amortization Table.”

Click on the cell A1 and type “Loan Amortization Table”. It can be observed that the cell width is insufficient to fit the contents of the cell. To adjust the cell size, move the cursor to the area shown in Figure 1.0 and click and drag to resize the cell width. Alternatively, you can double click in the area to automatically adjust the cell width to fit the contents. Make the font bold; this is the heading for the worksheet. Every worksheet should have a heading; this is not a necessity, but it is recommended to keep the spreadsheet organized. Click on cell A3 and type “Loan Amount”, “APR” in cell A4, “Loan Term” in cell A5, “Payments/year” in the cell A6. These are called labels. If the worksheet is filled with numbers, it will be difficult for the user to understand it. So, we give names to each number to communicate with the end user what each number means. Always use appropriate labels. Enter the value 25000 in cell B3, 6% in B4, 5 in B5 and finally 12 in B6.

We have now entered all input data needed for our calculation. Now we can additionally format the cells to make them more presentable. Since the loan amount is in dollars, we should tell Excel that the number in cell B3 is currency. This can be done by right clicking on the cell B3 and then clicking on the “Format cells” option. Click on the “Numbers” tab and choose “Currency” and click OK.

As observed in Figure 1.3, the loan amount is displayed in currency format and hence has a $ symbol before the value. You could also change the color and make the labels bold. Play with the formatting a little to differentiate the input values from the end results.

Since we make the payments every month, it is necessary to calculate the periodic interest rate. The APR is 6%; therefore, the interest rate for each month would be (6/12) = 0.5%. The loan term is 5 years; hence the loan will be repaid in 12*5=60 payments. We should add this additional information into the spreadsheet with appropriate labels. Assign different colors to the cells to differentiate the input, output and titles. To change the color of a cell, select the cell and click on the button highlighted by a red box in the Figure 1.4. Pick an appropriate color from the list.

Enter “Total payments” in cell A7 and in cell B7 enter the formula “=B5*B6”. Type “Periodic Interest Rate” in cell A8 and in cell B8 enter the formula “=B4/B6”. To ensure that your number displays as a percentage, Right click on cell B8, select “Format Cells”, Click on the “Number” tab, Select “Percentage” and change the “Decimal places” to 1 as shown in Figure 1.5. Finally we calculate the monthly payment amount from the periodic interest rate, principal amount and total number of payments. The payment per month can be calculated using the formula (which you will learn in your engineering economics class):

Enter “Payment amount” in cell A9 and enter the formula “=B3*B8/(1-((1+B8)^- B7))” in cell B9. This number is currency, so follow the instructions as before and change the cell to display the value as currency. Now the spreadsheet has all the data required to build the amortization table.

Now set up the spreadsheet to calculate the payment information for every month. This should calculate the interest for each month based on the principal, the amount of money paid towards the principal after subtracting the interest amount and finally the principal amount after the payment. Set up the spreadsheet as shown in Figure 1.6.

To ensure that your text fits the cells, you should select the cells and right click on them and choose the “Format cells” option, click on the “Alignment” tab and check the “Wrap text” option. The text you entered into the selected cell will automatically fit into the cell.

The principal amount for the first payment is simply the same amount that was originally borrowed from the bank. So we will enter the formula “=B3” in the cell C12. Now the interest on this amount is calculated using the simple interest formula i.e. “Principal amount before payment * periodic interest rate”. The periodic interest rate is the same for every month; therefore, we must use an
absolute address
when we use the periodic interest in the formula. By using the absolute address, we make sure that the input value does not change when we copy the formula.

Remember, as we discussed in class, absolute addressing can done by explicitly typing two dollars signs in front of a cell address or by pressing F4. Two dollar signs imply that the cell copied along a row or column will have a fixed value. However, if you put just one dollar sign in front of an address (e.g. $F8) then the column indicated by F will stay constant but the row numbers will change. On the other hand if you put a dollar sign in front of the number (e.g. F$8) then the row number 8 will remain constant and the columns will change automatically when you copy.

We subtract the interest from the monthly payment of $483.32 to calculate the amount paid on the principal. We will use absolute addressing when we reference the cell with monthly payments, as this stays constant throughout the calculation.

Finally, we subtract the “amount paid on principal” from the “principal amount before payment”. This will be principal amount for the next month’s payment.

This completes the calculation for the first payment. Now we can calculate the amount of interest and principal paid by the second payment. The principal for the second payment is equal to the principal amount after the first payment. So enter the formula “=F12” in C13.

To calculate the rest of the entries in this table, all we have to do is copy the formula for cells D12 to F12. This can be done in a couple of ways: Select all cells from D12 to F12 and right click on them and select “Copy”. Next right click on the cell D13 and choose “Paste”. This calculates all the entries for the second payment.

The second way to accomplish this is to select the cells for D12 to F12 and move your mouse to the bottom corner of the selected cells as shown in Figure 1.13.

When the mouse arrow turns to the + button as shown in Figure 1.13, click and drag to the next row. This will calculate all the entries for the next payment.

Calculating the next 58 entries is a breeze, as we just have to copy the formula from cells C13 to F13 and paste it in the next 58 cells either by using the copy-paste method or the click-and-drag method. In the copy-paste method, select all the cells from C14:F14 to C71:F71, right click on the cells and choose paste. However, when using the click-and-drag method, all you have to do is to select the cells C13 to F13 and click at the bottom corner, then drag down to C71:F71 cells. Excel will automatically fill all the entries for the next 58 payments. Figure 1.15 shows the amortization table for the first 10 payments.

Notice that the table was already configured to show the $ sign for the all entries. Ensure that proper formatting is followed to improve the appearance and readability of the table.

1. Do a Google search for “median home price in Dallas”. To avoid Private Mortgage Insurance (PMI), most lenders require a down payment of 20%. Assume you will pay the 20% down payment. How much will you need to borrow to purchase a median priced home in Dallas? Enter this amount in cell B3.

2. Do a Google Search for “30 Year Fixed Mortgage in Dallas”. Select the lowest rate you see. Enter this value in cell B4.

3. Update cell B5 to reflect a 30 year term. Cell B7 should update automatically.

4. Pick a cell to calculate the total dollar amount you will spend over 30 years. Total = Payment Amount x Total Number of Payments.

5. Now, do a Google Search for “10 Year Fixed Mortgage in Dallas”. Select the lowest rate you see. Enter this value in cell B4.

6. How did the monthly Payment Amount Change? How much did the Total Dollar Amount Change?

3.
GENERATING A PLOT FROM MULTIPLE DATA SETS:

Create a new sheet by clicking on the “+” on the bottom of the page next to the sheet title “Single Data Set”. A new sheet will appear with the name of “Sheet 2”. Re-name the new sheet “Multiple Data Sets.”

To generate a graph from multiple datasets, we need to first create the multiple data sets. In this example, we will create these data sets by using the same line graph problem with different slopes. Consider m = 0.1, 0.2, 0.3, 0.4 and 0.5. Now, recreate the layout as shown in Figure 2.4 and generate the data for the other 4 lines using the formula y = mx + c.

To generate multiple graphs from scratch, we just have to select all the data A3:F3 to A14:F14, click on the “Insert” ribbon and choose “scatter” and follow the instruction for plotting a graph. The graph will be displayed as shown in Figure 2.5.

To add a line to an existing data set, we can copy the new data and paste it on the graph. We could also click on the graph and this would highlight the cells on the spreadsheet that are included in the graph. You can then add additional data to an existing graph by extending the selected cells boundary (blue boundary shown below) to include the adjacent columns as shown in Figure 2.6. Finally, we could right click on the chart and click on the “Select Data” option and click on “Add” to add a new series.

2.3 DEVELOPING PROFESSIONAL LOOKING GRAPHS:

Create a new sheet by clicking on the “+” on the bottom of the page next to the sheet title “Multiple Data Sets”. A new sheet will appear with the name of “Sheet 3”. Re-name the new sheet “Professional Looking Graphs.”

The graphs shown above are some figures that can be rapidly prepared using EXCEL. While they look reasonable on the screen, they lack professional quality. Figure 2.7 below shows a nice, professional-quality graph for the same problem.

In order to improve the quality of the graph, do the following: Pay attention to the details and reproduce all the aspects shown in the figure below. Note all the lines are in black and white, no colors are used.

Right click on the horizontal axis numbers and select Font and change fonts to Arial 11 point; repeated the same for the vertical axis.

Right click on the horizontal axis number and select format axis. Under Axis Options, fix the minimum to zero, maximum to 10, major unit to 1.0. Go to Line style and increase it to 1.5 pts; go to Line color and select black. Select Number and use 1 decimal place.

Right click on the horizontal axis number and select format axis. Select Major tick mark type to “Cross” and this will make the tick marks cross the axis line.

Right click on each of the lines, select “Format Data Series,” and modify the “Line Color” to solid line and select black, change the Line style to 1.5 pt, and selected the appropriate Dash type. Select Marker option, build-in and select the appropriate symbol. Select Maker Fill and, select solid fill and select black. You might also want to check other options. Repeat the same for all the lines.

Right click on the axis label (value of X) to select to the font to Arial 12 point bold. Repeat the same for other axis.

For creating axis titles and editing the titles or other such formatting functions, click on the graph and this will take you to the “Chart Tools” tab on the top, then select “Layout.”

In the Layout tab and under Gridlines get rid of the horizontal grid lines.

Select the plot, right click and select Format Plot area, change the border color to solid line, black, and the border style to 1.75 pts.

The label box can be moved into the graph by simply selecting it and dragging it into the plot.

2.4 PLOTTING MULTIPLE FUNCTIONS WITH VARYING MAGNITUDES (ADDING A SECONDARY AXIS)

Create a new sheet by clicking on the “+” on the bottom of the page next to the sheet title “Multiple Data Sets”. A new sheet will appear with the name of “Sheet 4”. Re-name the new sheet “Plotting Multiple Functions.”

Time (hr)

Qin (cfs)

Qout (cfs)

Depth (ft)

0

0

0

0

1

296739

66168

1.31

2

359963

112505

3.8

3

327493

141706

6.03

4

264846

158367

7.53

5

200796

165877

8.26

6

146147

166730

8.35

7

103417

162755

7.96

8

71686

155287

7.24

9

48915

145297

6.34

10

32965

133490

5.35

11

21993

120375

4.35

12

14552

106316

3.39

13

9562

91578

2.52

14

6246

76349

1.75

15

4059

60764

1.11

Table 1: Detention Pond Data

While analyzing engineering problems, often one encounters data for a system that has vary in several orders of magnitude.. For example, the table above provides data for a detention pond where inflow rate, outflow rate, and the depth of the pond are measured over time. As you can see from this table, the numbers used for describing the depth variations are considerably smaller than the flow rates. If you blindly plot this data using time as your x axis, it will be impossible to see the depth variations. Try this first!

An approach to resolve this problem is to use two different scales for your Y-axis. We will use a larger scale for Q, which will be shown on the right axis, and a smaller scale for h, which will be shown on the left axis. To accomplish this, first plot all three graphs in the standard way. Now right click on the curve (height curve) that could not be viewed clearly. Now select “Format Data Series” option and then select secondary axis option button. This will automatically create a secondary axis. Now go ahead and clean up your graph and make a professional looking graph, as the one shown below.