Lab 2 – Financials (Excel)


Lab 2 Financials (Excel)

 

Project Objective

To offer an introduction to building spreadsheets, creating charts, and entering functions.

Part 1 – Financial Projections

One of the most important aspects of analyzing the feasibility and profitability of a new business is creating financial statements, projecting cash flows, and keeping track of information.  For the purpose of this part, you will be creating a 3-year income statement for two different business opportunities:  1) operating a plant in Location A or 2) operating a plant in Location B.  This will allow you to assess the profitability of your new endeavor after a few years of commerce and therefore make an informed decision as to which opportunity you should pursue.

You will begin by creating the 3-year income statement for Location A.

Income Projection for Location A

Figure 1:  Location A Financials worksheet (dates and numbers may differ from instructions below).

Initial Layout:

Figure 2:  Initial Data for Location A (use these numbers for location A)

Refer to figures 1 and 2 for the following tasks.

  1. Start Microsoft Excel. If you use Microsoft Excel 2010: You will notice you have three worksheets in your new Excel workbook. (Excel 2013 gives you only one sheet.)
  2. In Sheet1, enter the following text in cell B3: Location A’s Projected Income Statement.
  3. Select the cells B3:E3 and click on the Merge & Center button in the Home
  4. In cell A5, enter the text: Revenues.
  5. Enter the text in column A (cells A5:A37) as shown in Figure 2, including the text in the second table at the bottom (The Financial Summary Table). Cell A37 contains the text:  * pre tax.
  6. In cell C31, enter the text: FINANCIAL SUMMARY.
  7. In cell B4, enter the text: YEAR.  Enter 2017, 2018 and 2019 across the row (assuming the first year your imaginary business can make money is 2017).
  8. Enter numbers for your Revenues, Cost of Sales, and Operating Expenses for the next years. Use the numbers provided in Figure 2 above.  Do not enter the Total amounts.
  9. Use the SUM function to calculate all of the totals for the Revenues, Cost of Sales, and Operating Expenses for cells C10, D10, E10, C16, D16, E16, C26, D26, and E26. There are numerous ways you can perform a SUM function. One way is to use AutoSum.
  10. Enter a formula to calculate the Gross Profit for the three years (cells C18:E18). For the first year, you would enter the following formula in cell C18:  =C10-C16.
  11. Use a formula to calculate the Net Revenue/Loss for all three years (cells C28:E28). For the first year, you would enter the following formula in cell C28:  =C18-C26.
  12. Format all of the values with $ and no cents. Under the Home tab, click the currency icon (dollar sign on Windows and dollar bill with coins on Mac), and click the Decrease Decimal button (.00à.0) twice.
  13. Use the shading, border, and font color features to make your income statement look like the one in Figure 1 above. The colors and widths do not need to be exact, but every feature must be utilized as per the specifications implied in Figure 1. Numbers in Fig. 1 and Fig. 2 may differ; use the numbers from Fig. 2.
  14. In Cell C1, enter your company’s name. Then merge & center it in cells C1:E1.
  15. Adjust the column widths as necessary to display all cell contents.

The Financial Summary Table:

 

  1. Use cell references in the Financial Summary Table to display the numbers for Revenues, Cost of Sales, Gross Profit, and Operating Expenses from the Income Statement above in the worksheet. Do not simply type the numbers in or your sheet will not work.  For cell C32, enter = and then click on cell C10.
  2. Merge & center the text Financial Summary in cells C31:E31.
  3. Format the table similar to the table shown in Figure 1. If you would like, you can use the Cell Styles option in the Home

The 3-Year Financial Statistics Table:

 

  1. Enter the text for Average, Largest, and Smallest Net Revenue/Loss in cells G32:G34.
  2. For cell K32, use the Average function to calculate the average net revenue/loss by using cells C28:E28.
  3. For cell K33, use the Max function to calculate the largest net revenue/loss by using cells C28:E28.
  4. For cell K34, use the Min function to calculate the smallest net revenue/loss by using cells C28:E28.
  5. Merge & center the text 3-Year Financial Statistics in cells G31:K31.
  6. Format the table using shading, borders, and font colors. Again, make them similar, but they do not have to be exact.

 

 

The Chart

 

  1. Select all of the Revenues Totals (C10:E10), Cost of Sales Totals (C16:E16), and Operating Expenses Totals (C26:E26) simultaneously by holding down the [Ctrl] (or control) button and selecting the three sets of cells.
  2. Select the Insert
  3. Add a chart. There are many ways to do this in Excel. In Excel 2010 you can do this by selecting the Line pull-down, and then the Line with Markers  In Excel 2013, after you have clicked the Insert tab, you’ll see that the tool ribbon is divided into numerous sections (it is possible that you won’t see a tool ribbon at all — if you see no tool ribbon, go online and Google “how to turn on ribbon in Excel 2013” and turn your ribbon on, and if need be, “pin it” with the pin icon at the right side. These instructions depend on you having your tool ribbon visible). Tool ribbon sections collapse if the Excel window is narrow, so if you don’t see a Charts section, you can widen the Excel window until you see it. At the lower right corner of the Charts section is a small pulldown arrow; clicking it causes an annoying popup (so don’t click it; if you get a popup that says you must select cells, you can simply click OK and close the popup).  Instead, in the Charts section, look at the different “Recommended Charts” (don’t click “Recommended Charts” — that only causes the same annoying popup. If you want to help Microsoft improve its products, click “Was this information helpful?” and select “NO“).   To the right of “Recommended Charts,” you see three rows of chart types. Mousing over them pops up their names. You want to click the “Insert Line Chart” icon. Then select the Line with Markers option.
  4. Define the parameters of the chart:
    1. Under the Chart Tools, select the Design In Excel 2013, you are automatically already in the Chart Tools / Design tab.
    2. Click the Select Data
    3. Look for the Horizontal (Category) Axis Labels section, and click Edit. Select the years cells (C4:E4) of your spreadsheet and hit the Enter
    4. Change the name of Series1, Series2, and Series3 to Revenues, Cost of Sales, and Operating Expenses, respectively, by selecting the Edit button under the Legend Entries (Series) Change them by clicking in the area for Series name and selecting the cell containing the label for Revenues.  Do this for all three labels.
    5. In the Select Data Source window, press the OK
  5. Excel 2010: Under the design tab, expand the Chart Layouts area by clicking on the arrow below the down arrow. Select Chart Layout 5. Excel 2013: at the left end of the tool ribbon, click Quick Layout, and then select Layout 5.
  6. (If you are working in Excel 2013, skip down to step 30.b.) Excel 2010: Adjust the layout of your chart by selecting the Layout tab under Chart Tools:
    1. Remove the Y-axis label by going to Axis Titles à Primary Vertical Axis Title à None.
    2. Click on the Chart Title on the chart. Change the label to your company’s name.
    3. Excel 2013: Click on Add Chart Element, at left end of the tool ribbon, mouse over Axis Titles, and click Primary Vertical to remove the vertical axis title on your chart.
  7. The graph should now be complete. Move it to the right side of the spreadsheet and resize it, as per Figure 1.

 

 

Additional Items

 

  1. To rename the worksheet, right-click on the Sheet1 tab at the bottom of the Excel sheet and select the Rename option (or just double-click the sheet name and type the new name). Enter Location A Financials.
  2. Add a textbox somewhere on your spreadsheet by selecting the Insert à Text Box Then “draw” the box, using your mouse. Comment on the trend by typing something like Net profit is decreasing or Net profit is increasing. Drag the text box to the proper location (near the Net Revenue/Loss numbers). Adjust the font and color of the text.
  3. Include a comment on cell E36 by right-clicking on the cell and selecting the Insert Comment option (or selecting the Review tab and the New Comment) Comment on the third year’s Net Revenue/Loss.
  4. Add a descriptive header and footer to your spreadsheet by selecting the Insert tab and the Header & Footer For the header, enter ITP 101 Spring 2017.  For the footer, enter your name.
  5. Save your Excel workbook as Lastname_Firstname_Excel1, replacing Lastname and Firstname with your own last name (family name) and first name (given name).


Income Projection for Location B

 

Figure 3:  Location B Financials worksheet (dates and numbers may differ from instructions below)

 

 

Copy Worksheet for Location A to Begin Analysis for Location B

Refer to figure 3, 4, and 5 for the following tasks.

  1. Copy the Location A Financials worksheet by right-clicking on it and selecting the Move or Copy… Refer to Figure 4 below.

 

Fig. 4. Move or Copy dialog

 

  1. Verify your worksheet is listed under To book.
  2. Below Before sheet, select “(move to end).”
  3. Click the Create a copy
  4. Click the OK
  1. Rename your copied worksheet to Location B Financials.

Figure 5:  Financials for Location B (use these numbers for Location B)

  1. In the Location B Financials worksheet, use the exact values of the Revenues, Cost of Sales, and Operating Expenses for all three years as shown in Figure 5.
  2. Look at the source data at the bottom of Location B’s chart, and make sure the Operating Expenses, Cost of Sales, and Revenues in the chart are referring to the figures in Location B’s income statement.
  3. Rename the title in cell B3 to Location B’s Projected Income Statement.
  4. Change the message in the text box and the comment in E36.
  5. Save your Excel workbook, and upload it to Blackboard. Submit your Excel file on Blackboard (http://blackboard.usc.edu) under Assignments.
  6. Click on the Lab2 Part1
  7. Next to Attach File, click on the Browse My Computer
  8. Find your Lastname_Firstnamexlsx file and click the Choose button.
  9. Click on the Submit button in the bottom right corner.

 

 

Part 2 – Projections

When doing projections for future revenues and expenses with different scenarios possible, it is useful to have comparison charts for breaking down percentage of revenues and expenses.

For this part of the project, you will breakdown the revenues, cost of sales, and operating expenses for the first year of your business (note: illustrations show past years; you should work in present and future, not past years). 

We will also create a chart showing growth in revenues and expenses.  We will show later the adjustment growth based on inflation.
Figure 6:  2017 Projection Breakdowns worksheet (note: years and figures may vary from your worksheet)

 

Excel provides many ways to perform an action.  If your version of Excel does not precisely match any of these directions, hunt around the toolbar and menus and sidebar. Don’t be afraid to try things. You can always backtrack by using Control-Z (Windows) or Command-Z (Mac).

  1. Use your existing Excel file (turned in for Lab2 Part 1), and work in a new sheet. Excel 2010: Rename Sheet2 to 2017 Projection Breakdowns. Excel 2013: create a new sheet and name it 2017 Projection Breakdowns.
  2. Excel 2010: Insert a new Pie Chart by selecting the Insert tab, clicking on the Pie pull-down and selecting the Pie in 3-D Excel 2013: remember the discussion re the graph in the Location A sheet, about how the Excel tool ribbon works. In your new blank sheet, click the Insert tab. Look for the Charts section of the tool ribbon. Just above the C in Charts, you should see a tiny pie chart (it looks a little bit like Pac-Man). Click Pac-Man, and select 3-D Pie. A blank chart area appears (there is no pie chart yet because you haven’t selected the data for it yet).
  3. Select the Design tab (Excel 2013: you are already in the Chart Tools/Design tab), and select the Select Data That opens the Select Data Source dialog.

Figure 7:  Select Data Source window

  1. Put the cursor in the Chart data range text field at the top of the dialog, then go to the Location A Financials worksheet and select cells C6:C9. Now your “Chart data range” shows =’Location A Financials’!$C$6:$C$9 (Note: if you wish, you can type that into your “Chart data range” field.) These numbers (C6 to C9) give you the amounts that will determine the pie wedges in your first chart.
  2. Now look at the left side of the dialog, under the Legend Entries (Series) label, and click on the Edit button (“Series 1” is highlighted, and you’re editing Series 1).
  3. In the Edit Series window, for the Series name type Location A Projected Revenues, 2017 and then click the OK Now you’ve written the title of your pie chart. NOTE: there is a problem that can occur during the process of typing the Series name, if you hold down the Shift key while hitting the Space bar on your keyboard. Some unwanted text will appear in your series name, like the illustration below. Do not press Shift and Space at the same time while typing the series name!
  4. Now look at the right side of the dialog and find Horizontal (Category) Axis Labels, and click on the Edit
  5. In the Axis Labels window, for the Axis Label range go to Location A Financials worksheet and select cells A6:A9. Click the OK You have now selected the labels for your pie segments (see image below).
  6. Now you are back in the Select Data Source window. You’re done here, so click the OK
  7. Now you’re going to change the layout of your pie chart. In Excel 2010: Under the Design tab in the Chart Layouts section, click the pull-down and select the Chart Layout 2 Excel 2013: at the left end of the tool ribbon, click Quick Layout and select Layout 2.
  8. Position the chart so it approximately takes up the space from A1 through E15. Note: in Excel 2013, if you pull the edge of the chart area too close to the edge of A1, the whole chart will just bounce back – so leave a smidgeon of space at the edge.
  9. Click on the Pie Chart. Right-click inside the pie to bring up some dialog options, and select the Format Data Labels… Depending on whether you have Excel 2010 or 2013, you’ll see one of the following menus (Figure 8):

                             Left: Excel 2010 (dialog)                               Right: Excel 2013 (sidebar)

Figure 8:  Format Data Label(s)

  1. In the Format Data Label dialog or sidebar, for the Label Contains checkboxes, select the Value (Percentage and Show Leader Lines should already be checked.)  For the Label Position radio buttons, select the Inside End (or Best Fit) option.  For the Separator pull-down, select the (New Line) option.  Excel 2010: Click the Close button. Excel 2013: if you want to close the sidebar, click the X icon at upper right corner of the sidebar.

 

Important point for Excel 2013. The Format Data Label window has been rolled into the properties sidebar at the right side of the interface. Right-clicking on a specific part of the chart will pull up a pop-up menu that allows you to engage this side properties menu relating to the content you right-clicked. As examples:

  1. Clicking on the pie chart itself will reveal “Format Data Series…”.
  2. Clicking on the chart title will reveal “Format Chart Title…”
  3. Clicking on the data labels will reveal “Format Data Labels…”

The content of the sidebar is populated based upon what you selected or clicked most recently. This allows one section of the interface to service a range of different operations and functions. Since many of Excel 2013’s features rely on this menu, if you aren’t seeing an option or menu you are looking for, it may be worth right-clicking on the specific chart element to see if it can be modified in the sidebar.

Lastly… If you are looking to add chart elements you may find those elements in the Chart Tools Design Tab via “Add Chart Element”

 

  1. Now change the pie’s rotation settings. In Excel 2010: Under the Layout tab, click on the 3-D Rotation In Excel 2013: right-click the pie chart itself, and select 3-D Rotation…
  2. In the Format Chart Area window, set the X value to 310, the Y value to 30, and the Perspective value to 20. While you are changing values, click the up and down arrows to change the angles, and watch the pie chart to see what happens. Excel 2010: when done, click the Close Excel 2013: you don’t need to close the sidebar. Why not just leave it open.

 

 

  1. Excel 2010: Under the Design tab, change the Chart Style to whatever you wish. Excel 2013: to change the chart style, click the paintbrush icon at the upper right side of your chart’s box (see image above – if you don’t see a paintbrush icon in your program, click in the chart area); you can alter the Style and/or the Color by clicking the tabs at top. Note that the colors come in color groups, and cannot be individually changed. To close the Style/Color dialog (see image below), click in an empty cell.

 

 

  1. Select the entire pie chart by clicking its center. Excel 2010: Under the Format tab, click on the Shape Effects pull-down and select the Bevel à Circle This will soften the edge of the chart. Excel 2013: in the sidebar, click the Pentagon icon and click 3-D FORMAT to select your desired top and bottom bevels. You can also change the Material if desired.

 

 

 

  1. Change the size of the font of the Title so it fits nicely in the chart. Select the title, go to the Home tab, and reduce the size of the font, if necessary.
  2. Now you’ve made a pie chart for the Location A Revenues. Repeat these same steps and make pie charts for the Location A Cost of Sales and Operating Expenses, and the Location B Revenues, Cost of Sales, and Operating Expenses. That’s six pie charts in all, all in the same worksheet. Arrange the charts so that all three of Location A charts are on top and all three of Location B charts are on the bottom as shown in Figure 6. Try to get the values inside of the chart’s “pie slices” by changing the perspectives.  Note: if desired, you can copy your first pie chart and make five copies of it, then change the data and labels of each. Know, however, that this may not be a time-saver in the end.

 

Figure 9:  Financial Trends worksheet

  1. Rename Sheet3 to Financial Trends. If you need to add a new slide (Excel 2013 users will need to), click on the Insert Worksheet button (it looks like a plus sign in a circle) at the bottom and then rename the sheet.
  2. Excel 2010: Insert a new column chart by selecting the Insert tab, clicking on the Column pull-down and selecting the Clustered Column Excel 2013: the Column charts are the top-left icon in the Charts section of the tool ribbon.
  3. Under the Design tab, click on the Select Data
  4. In the Select Data Source window, under the Legend Entries (Series) label, click on the Add
  5. In the Edit Series window, for the Series name enter Location A Revenues. (Note: when typing a series name, be careful not to accidentally hold the Shift key down while hitting the Space bar.) For the Series values, delete the text that is there (by using the delete or backspace key).  Go to the Location A Financials worksheet and highlight cells C10:E10.  Click the OK
  6. In the Select Data Source window, under the Horizontal (Category) Axis Labels label, click on the Edit
  7. In the Axis Labels window for the Axis label range, click on the Location A Financials worksheet and select cells C4:E4 (the years). Click the OK
  8. Back in the Select Data Source window, repeat the previous steps (23-26) for Location B Revenues, Location A Cost of Sales, Location B Cost of Sales, Location A Operating Expenses, and Location B Operating Expenses. You will need to use your brain and select the appropriate worksheet and cells.  Once you are done, click the OK
  9. Under the Chart Tools/Design tab, click on the Chart Layouts pull-down (Excel 2010) and select Layout 7. Excel 2013: you’ll need to click Quick Layout (in the Chart Layouts section of the tool ribbon, left side of the window) to find Layout 7.
  10. Excel 2010: Under the Design tab, click on the Chart Styles pull-down and choose a style.

Excel 2013. You can select from a few different styles and color combinations by clicking the paintbrush icon to the right of your chart. You can fiddle with the formatting by selecting sections of your chart and using the sidebar (pentagon icon), but Excel 2013 is not very friendly (you can adjust one series at a time — not all the columns at once, unless you discover a secret method). Just make a nice-looking clustered column chart.

  1. Remove all axis titles by clicking on the Layout tab (Excel 2013: click Add Chart Element, mouse over Axis Titles) and using the Axis Titles option, remove both horizontal and vertical titles.
  2. Add a chart title by going to the Layout tab (Excel 2013: click Add Chart Element, mouse over Chart Title) and using the Chart Title Enter the title Revenue and Expense Projections.
  3. Save your file under a new name: Lastname_Firstnamexlsx
  4. Submit your Excel file on Blackboard (http://blackboard.usc.edu) under Assignments.
  5. Click on the Lab2 Part2
  6. Next to Attach File, click on the Browse My Computer
  7. Find your Lastname_Firstnamexlsx file and click the Choose button.
  8. Click on the Submit button in the bottom right corner.

 

 

Part 3 – Further Financial Analysis

Quite often, economic conditions govern which investment a company should make.  The financial calculation of an investment’s net present value enables a company to consider these economic conditions and make an educated decision as to which investment to make.  In this exercise, you will learn how to use Excel as a tool to make these kinds of informed decisions.

For this part of the project, you will be assessing the expansion of your business.  Your company, whether it is a retailer or manufacturer, will be opening up a new location for business operations.  You will choose between the two locations that you earlier created income statements.  Each location will generate different revenues, which you previously estimated.  This assignment will allow you to determine which location will be the most lucrative, based upon the prevailing economic conditions.

You will first use Excel’s built-in functions to “look up” values based upon certain criteria.  Then, you will calculate the Net Present Value so as to compare investment in the two different locations with varying returns over the next three years under different economic inflationary conditions.  Using an If-Then function, you will have Excel determine which location you should invest in.  Then using a Payment function you will determine the required monthly payments when the cost of investing in each location is borrowed.  Finally, you will chart the results of the Net Present Value calculations to “see” the complete potential of the two locations under the varying economic conditions and to determine at which inflationary rate both locations become equally attractive.

 

Figure 10:  NPV worksheet (note: dates may vary)

The NPV Sheet Layout

Refer to Figure 10 for the following tasks.

  1. Using your most recent Excel file (turned in for Lab2 Part2), create a new worksheet by clicking on the New Worksheet icon at the bottom (Excel 2013: looks like a circle with a plus sign in it).
  2. In the new worksheet, create the spreadsheet shown in Figure 10, including the cell formats shown. Merge & center the text Net Present Value at Various Inflationary Rates in cells F12:K12.  The only numbers you will enter manually are the Square Footage and %NPV
  3. For the %NPV rates (cells F13:K13), enter in 0, 01, 0.02, 0.03, 0.04, and 0.05, and then format them as a Percentage.
  4. The text in cell A8 that display the tax rate needs to be wrapped. In the Home tab, click the Wrap Text button (which is above the Merge & Center button).
  5. In cell A1, enter your name.
  6. In cell H1, type in your USC email address. Then select this cell by right-clicking on it and choose Hyperlink.  Click on the e-mail address button on the left, and type in your USC email address.
  7. Under Comparison of Two Locations, reference the Net Revenue/Loss amount from the Location A and Location B Financials worksheets for 2017, 2018, and 2019. For cell B15, you would reference cell C28 on the Location A Financials  You will not have a value for B14 and C14, which is the amount you would have paid for the locations today.
  8. Rename the worksheet to NPV.

 

The Purchase Costs Sheet Layout

Figure 11:  Purchase Costs worksheet

Refer to Figure 11 for the following tasks.

  1. Create a new worksheet.
  2. Rename the worksheet to Purchase Costs.
  3. In the new worksheet, enter the text and values as shown in Figure 11.
  4. Format the numbers as currency with no cents. Format the interest rates as percentages.
  5. Format the Purchase and Remodeling Costs Based Upon Square Footage data similar as it appears above. Under the Home tab, use the Format as Table Excel 2013: when you do this, you’ll get a Format As Table dialog, which wants to know which parts of your table are data, and whether or not the table has headers. If you selected A1:C9, you’ll see =$A$1:$C$9, and “My table has headers” is checked. The dollar signs are for absolute referencing (as opposed to relative referencing). Change $1 to $2, and hit OK. That’ll give you the format seen in figure 11, except that your row 1 is shown unformatted. Merge and center A1:C1, and format the table title to look similar to the headers in row 2. You’ll probably need to resize columns A through G; just highlight columns A through G and drag any column divider to the right, to resize all the selected columns.
  6. Give the loan table a range name by highlighting cells B13:G14. Click on the Formulas tab, select the Define Name option and enter LoanRates for the Name.  Click the OK  Alternatively, you may enter LoanRates in the Name Box (to the left of the formula box).
  7. In cells F3 and G3 reference the two locations’ square footage from the NPV Do not simply type in their values.  In the formula bar, enter an =, click the NPV sheet, click the appropriate cell, and hit the enter key.
  8. In cells F4 and G4 use the VLOOKUP function to determine the purchase costs for the two different locations based upon the Purchase and Remodeling Cost table. For the VLOOKUP function for Location A, the first argument is F3, the second argument is $A$3:$C$9, and the third argument is 2 since we want the value from the second column.
  9. In cells F5 and G5, use the VLOOKUP function to determine the remodeling costs for the two locations. For the third argument you want the value from the third column.
  10. Use the ∑ AutoSum function in cells F6 and G6 to calculate the costs before taxes. Do not include the value for the Square Footage.
  11. Save your workbook.

Calculate Total Cost:

  1. Return to the NPV
  2. In cells B6 and C6, reference the purchase costs that were determined on the Purchase Costs worksheet for Location A and Location B.
  3. In cells B7 and C7, reference the remodeling costs that were determined on the Purchase Costs worksheet for Location A and Location B.
  4. In cells B8 and C8, calculate the 8.75% tax on the purchase cost of each location in cells B7 and C7 by using the appropriate formulas and cell references. For cell B8, enter =B6*8.75%.  Display the results to the nearest dollar by utilizing the currency formatting.
  5. In cells B9 and C9, use the AutoSum function to add up the total cost of the two locations after taxes. Be careful not to include the value for the square footage in your AutoSum calculation.
  6. In cells B14 and C14, use cell references to include the cost of the two locations in the comparison table for Today. Since the values for the years are the net revenues/loss (if positive, then these are profits), the Today values for each location are the costs negated.  For cell B14, the value is =-B9.  These values should be negative which will be represented with a negative sign or parenthesis or red text.

Calculate the loan amounts:

  1. Return to the Purchase Costs
  2. In cells F7 and G7, use cell references to get the total cost from cells B9 and C9 from the NPV This is your loan amount.
  3. To determine the annual interest rate for the two locations, an HLOOKUP will be necessary. In cell F9, use the HLOOKUP function to get the interest rate.  The first argument will be the total cost, since the interest rate is dependent on the principle.  The second argument is the table LoanRates.  The third argument is the row index number, which is 2 since we are trying to retrieve the values from the second row of the LoanRates table.
  4. Change the format of cells F9 and G9 to Percentage.
  5. In cells F10 and G10, calculate the monthly payments for the loan amount using the PMT
    1. The first argument for the PMT function is the interest rate for the period of the payment. Since our interest rate is annual, and our payments are monthly, we have to divide by 12.  So the value should reference cells F9 and G9, divided by 12.
    2. The second argument for the PMT function is the number of payments. Since there are 12 months in a year and we have a 3 year loan, the value is 36.
    3. The third argument for the PMT function is the present value. Since this is a loan, use the loan amount.

Calculate the NPV values:

  1. Return to the NPV
  2. Use the NPV function in cells F14:K15 to calculate the Net Present Values for the two locations under different inflationary rates.
    1. The first argument for the NPV function is the rate of discount over the length of one period. This is the inflationary rate.  For cell F14, this argument is F13.
    2. The second argument is a range for the income. For cell F14 which is for Location A, this argument is $B$15:$B$17.  For cells that need to reference Location B, use $C$15:$C$17.
    3. For the cells F14:K15, the value of Today needs to be added to the NPV result. For cell F14, the value is NPV(…)+$B$14.
  3. In cells D19 and D20, use an appropriate IF function to have Excel determine the correct answers to the two questions in rows 19 and 20 based upon the results of your NPV calculations.
    1. The first argument for the IF function is the logical test. Compare the values in the Net Present Values  For cell D19 which asks at 1% inflation, this argument is G14>G15.
    2. The second argument for the IF function is the value if the logical test is true. For cell D19, we want to print the text Location A since location A would be more appealing.  Thus this argument is E14.
    3. The third argument for the IF function is the value if the logical test is false. For cell D19, we want to print the text Location B since location B would be more appealing.  Thus this argument is E15.
  4. In cells D21 and D22, reference the monthly payment values calculated on the Purchase Costs Do not simply type in the calculated values from the sheet.

Creating the NPV chart:

Figure 12: Sample NPV Chart (Note: your chart may differ)

On the NPV worksheet, create a line chart displaying the NPV values.  Your chart data may differ from the above chart.  Adjust your commentary accordingly.

  1. Select the range of cells you want to use for the chart (E13:K15).
  2. Select the Insert tab, click on the Line option (in the Charts area) and select the 2-D Line Excel 2013: Insert/Charts/Line (chart type 1).
  3. Under Chart Tools, select the Layout Use the Chart Title option to add the following title above the chart: Net Present Value Calculation. Excel 2013: just click the chart title and type its new name.
  4. Use the Axis Titles option (Excel 2013: this is in Add Chart Element) to add the following title to the Horizontal Axis below the values: Inflationary Rates.
  5. Remove the chart’s background pattern and border by selecting Plot Area à None. Excel 2013: right-click within the chart box but not within the chart area itself, and select Format Chart Area. The sidebar appears, with the title Format Chart Area. Click the Chart Options dropdown and select Plot Area. Select No line under BORDER.
  6. Remove the gridlines by selecting Gridlines à Primary Horizontal Gridlines à None and Gridlines à Primary Vertical Gridlines à None. Excel 2013: click Add Chart Element (left side of the tool ribbon), mouse over Gridlines, and click on Primary Horizontal Gridlines. Repeat for Primary Vertical Gridlines if there are vertical gridlines in your chart.
  7. Change the Y-axis to remove some of the unneeded white space at top and bottom of the chart. To do this:
    1. Select the Vertical (Value) Axis section in the chart and click on the Format Selection option.  (Excel 2013: first left-click then right-click a number at the left of the chart, then select Format Axis, and look at the sidebar.)
    2. In the Axis Options section of the Format Axis window, for the Minimum option select the Fixed radio button  (Excel 2013: there is no Fixed button) and change the value in the Minimum text field to be the nearest major dollar amount below the lines (to reduce the amount of white space in the chart).
  1. Note: the Minimum value is shown above the Maximum value, which can be confusing for the visually-oriented.
  2. If you see a value such as “2.7E6” that means “2.7 million.” E6 is “ten to the sixth power.”
    1. Change the Maximum option to be the nearest major value above the lines (again: to reduce the amount of white space).
    2. The Major unit amount may be either 50000 (50,000) or 100000 (100,000).
    3. (Excel 2013: you may not need to perform the following step.)  Select the Number option, and change the Category to Currency and the Decimal places text field to 0.
    4. (Excel 2013: skip the following step.) Click the Close button.
  1. Change the two Location NPV data lines to thicker lines. Select each line and click on the Format Selection  (Excel 2013: click a line and look at the sidebar.)   Click on the Line Style option, and change the Width to 4 pt.
  2. Make sure the tick marks cross the X and Y-axes, and the X-axis labels should align with the tick marks. To do this:
    1. Click on the Horizontal (Category) Axis section (the percentages) in the chart, and select the Format Selection (Excel 2013: first left-click then right-click one of the percentages in the horizontal axis in your chart, and select Format Axis, and open the Tick Marks section. If you do not see the Tick Marks option, right-click the horizontal axis number again and select Format Axis again.) In the Format Axis window, make sure the Axis Options is selected.  For the Major tick mark type, change the pull-down to Cross.  For the Position Axis, select the On tick marks radio button.
    2. Click on the Vertical (Value) Axis section in the chart, and select the Format Selection (Excel 2013: if your sidebar is still open, you want Format Axis / Axis Options/ [three columns icon]/TICK MARKS.) For the Major tick mark type, change the pull-down to Cross.  Click the Close button (Excel 2013: close the sidebar with the X at upper right corner).
  3. Add a Text Box to the worksheet. Under the Layout tab in the Insert section (Excel 2013: under the Text section of the Insert tab), click the Text Box
    1. If the two lines cross, then the two locations are equally attractive at the corresponding inflation rate. In the text box, enter a message stating the inflation rate such as (for example) “The locations are equally attractive at 10%.”
    2. If the two lines do not intersect, then one location is always preferable. In the text box, enter a message stating the preferable location such as “Location A is preferable.”

Final checks:

  1. Verify all dollar amounts are to the nearest dollar (no decimals) on all sheets. Every dollar amount should be formatted using the standard accounting format.
  2. To test the project once you have finished, change the Square Footage on the NPV For example, change Location A to 6000 and Location B to 8000.  All the cell values that you calculated, including the Chart, should change.
  3. Change the square footage back to 5575 for Location A and 3750 for Location B. (Hitting Control-Z twice should return those values to what they were before.)
  4. Check all sheets for formatting. Match font boldness and cell borders and cell color fills to the illustrations in these instructions. (You may choose different colors, but where a cell is color-filled in the illustrations, they should be color-filled in your file.)

 

Data1 Data2 relative relative absolute absolute abs col, rel row abs col, rel row rel col, abs row rel col, abs row
1 10 1 1 1 1
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
Start at one of the thick boxes (C2, E2, G2, I2)
Auto complete down the column (C, E, G, I)
Auto complete to the right of the column (D, F, H, J)

 

Submitting the project

  1. There should be a total of 6 worksheets in your Excel workbook. Arrange them in the following order:  Location A Financials, Location B Financials, 2017 Projection Breakdowns, Financial Trends, NPV, Purchase Costs.
  2. Save the Excel file as Lastname_Firstnamexlsx, where Lastname and Firstname are replaced with your Lastname (family name) and Firstname (given name).
  3. Submit your Excel file on Blackboard (http://blackboard.usc.edu) under Assignments.
  4. Click on the Lab2 Part3
  5. Next to Attach File, click on the Browse My Computer
  6. Find your Lastname_Firstnamexlsx file and click the Choose button.
  7. Click on the Submit button in the bottom right corner.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: