Introduction To Excel - University of Florida



Spreadsheet Modeling Assignment

1. Objectives

The purpose of this assignment is to use some of the more advanced tools that Excel provides to develop a model of a complex business problem. In particular, you will use the following Excel features:

1. Importing data from text files

2. Sorting data

3. Database functions

4. Pivot Tables

5. Data Tables

6. Goal Seeking

2. What You Will Need

1. Access to a computer with a printer and Excel 2007.

2. Note that the instructions in this handout are written for Excel 2007. There are significant differences in user interface between Excel 2007 and 2003.

3. Download the following three files: Mymodel.xls, labor.txt, and demand.txt.

3. What You Will Hand In

1. A 9”x12” envelope containing a CD or a USB Drive that has the model your group develops. On the envelope, write the course number, group number, the name of each group member. Your CD (or USB Drive) with your graded homework will be returned to you when the grading is completed.

2. two full-page charts (with your course number and group number as a footer)

3. four worksheet printouts (with your course number and group number as a footer)

4. Make sure your CD or USB Drive is free of any computer virus. A contaminated CD or USB Drive will result in a 50% penalty in grade!

4. Due Date

Check the course syllabus for assignment due date. A penalty will be assessed against assignments handed in late (including during class).

5. Background

You work for the NOVA Corporation, a mid-tier supplier of hoses and hose assemblies for the Automotive and Aerospace industries. Several of your plants are considering the acquisition of computer-controlled lathes that will significantly change the cost structure of one of NOVA’s major products. In particular, the lathes will reduce the amount of manual labor that must be performed during the manufacturing process (the precise amount of the reduction is unknown). You have been asked to conduct a financial analysis of this proposal, and to include in your analysis the possible impact of a price change for the product in question. You know that price and sales volume are related, but the precise nature of this relationship is unknown. You have also been asked to consider several sales growth scenarios (i.e.: increases in sales ranging from 0 to 8%). You are expected to derive the NPV of the project over a five-year period.

You know that there is a “learning curve” associated with the use of the new lathe equipment, and that labor costs should therefore diminish as workers become more familiar with this sophisticated equipment over time. You also know that labor productivity will depend on the amount of training that is provided to lathe operators during the implementation period. Fortunately, you have some data that can quantify these relationships. Several of NOVA’s plants have installed these lathes during the past five years to manufacture a product whose requirements on this machinery are identical to the product you’re investigating. You have obtained this data from the cost accounting system on the corporate mainframe, but it is not in Excel format. It is in a file called labor.txt. The first seven records look like this:

For each Plant that has installed this equipment, and for each Year since installation, the data reports the Labor Cost Per Unit that have been obtained on this type of lathe, as well as the Year Of Installation of the equipment and the Initial Training Expense Per Operator. Notice that the last two fields (Year Of Installation and Initial Training Expense Per Operator) are the same for any particular plant. The Jackson plant purchased their lathes at the beginning of 1991, spent $41,000 per operator (including training materials, instructor fees and an allowance for lost productivity). In 1992 (their second year of operation), their labor costs per unit were $94.326. Their labor costs per unit during their third year of operation were slightly lower ($86.162). The Toledo plant purchased their equipment at the beginning of 1992, spent $17,000 per operator on training, and in 1995 (their fourth year of operation), spent $88.64 per unit on labor. You will use this data to determine how much should be spent on Initial Training for the new lathe operators.

You also have data from the corporate mainframe that will assist you in determining how income will vary depending on the product price. Your Marketing Department has conducted a research study to determine how sales will vary across several price points. This data is available in a file called demand.txt - the first few records look like this:

Notice that because of differing opinions about the interpretation of the study results, there is more than one estimate for each price point. You will use this data to develop a "Demand Curve", which your model will then use to show how income varies as a function of price as well as sales growth.

Finally, an Excel spreadsheet called mymodel.xls contains the beginnings of the model that you will complete for this assignment.

Specific Instructions

Getting Started:

We are interested in NPV, and we know that NPV is computed from cash flows over some period (in our case, 5 years). Our first task is to develop a spreadsheet that will compute the cash flows for this analysis. Cash flow in any year will depend on sales volume in that year and profit margin per unit in that year. Furthermore, we know that sales volume is a function of price (remember the downward-sloping demand curve) and that profit margin per unit is the difference between price per unit and costs per unit. One important component of cost per unit is labor cost per unit, which will be directly influenced by installing the new lathes.

We must therefore develop a model that allows us to measure NPV as we change sales volume, price and labor costs. In the first year of our analysis, sales volume will depend only on price. In the following years, however, it will depend on a sales growth assumption (i.e.: increases in sales ranging from 0 to 8%).

1. Download labor.txt, demand.txt and mymodel.xls from the class website and save them in the root directory of an empty CD.

1. Start Excel and open the workbook called mymodel.xls. All of your analysis will be done in this workbook - you should save it periodically.

1. Examine the worksheet called Analysis carefully - it contains the skeleton for computing the cash flows associated with this project. Note that all cells at the top of the sheet have names. You should use these names in any formula that refers to these cells. Take a few minutes to understand the formulas in the "1997" column of the spreadsheet, and to gain an overall understanding of the composition of Unit costs, Net income before tax, and how these values depend on the values in the cells called fixedFactoryOverhead and Sales1997.

1. Complete the table for years 1998-2002 using the following guidelines:

5. Most cells in D12:D27 should contain formulas, not numbers, and they should be written in such a way that they can simply be copied to cells E12:H27.

6. Cells in the Unit Price row from D12 to H12 should obtain their values from the cell named Price, while cell C12 should contain the value 500.

7. Assume for now that the values for the following costs will be the same in years 1998 through 2002 as they were in 1997:

8. Direct labor cost/Unit (we’ll change this later to reflect the new lathes)

9. Material cost/Unit

10. Transportation cost/Unit

11. Material handling cost/Unit

12. Selling cost/Unit

13. G&A/Unit

14. Interest expense/Unit

15. Fixed manufacturing cost/Unit for each year is fixedFactoryOverhead divided by that year’s Sales

16. Each year, sales should increase from the previous year by the value contained in the cell called SalesGrowth. This increase should start with 1998 increasing over 1997’s sales (we’ll change 1998’s sales to be a function of price later).

17. The row called Marginal net income before tax should reflect the amount by which each year’s Net income before tax exceeds the Net income before tax in 1997 (i.e.: NOT the increase from the previous year). Use an absolute cell reference for the formulas in this row.

Check: Marginal net income before tax in 2002 should be $1,808,582.

1. Next, we compute the amount of capital investment associated with the acquisition of the new lathes and the amount we will spend on initial training. The equipment cost is known - its value is in a cell named equipmentCost. Training cost is the product of the number of operators to be trained and the training cost per operator. The number of operators is known (numberOfOperators), and we have a rough estimate of how much we might spend on training per operator (trainingCostPerOperator - we’ll change this later). In the cell named totalInvestment, enter a formula to compute the total investment as the sum of equipment cost and training cost. Remember to use cell names in this formula. Check: totalInvestment should be $20,045,000.

2. In the cell named NPV, enter a formula to compute the 5-year NPV of the project on January 1, 1998 based on the cash flows in the Marginal net income before tax row, the totalInvestment computed above, and the APR (Annual Percentage Rate). Again, remember to use cell names whenever you refer to a cell in the top part of the worksheet. Also, take care to insure that you handle the timing of the cash flows properly. The investment in the new lathes and operator training will occur at the beginning of 1998 and the marginal income is considered to occur at the end of each year. Refer to the Help topic on NPV to see how this situation should be handled if you are unsure. Check: NPV should be -16,720,236. This is a large negative value because we have included our $20M+ investment without including any benefit in terms of reduced labor costs.

3. Print Step 1: Choose Formulas tab and, in the Formula Auditing group click the Show Formulas button. Then select range printArea1 and print the selection in landscape with your group number and course number as a footer (e.g.: Group 1 –ISM6485) - ask Excel to fit the entire image on 1 page. Don’t worry about formatting. Then turn off the Show Formulas option. (Suggestion: at each printing point, make backup copies of your work using File/Save As and name these intermediate results myModel-Stepnn.xls. Many of you will be glad that you did this.)

Incorporating New Labor Costs:

1. Recall that data about labor productivity is in a file called labor.txt. We want to “import” this data into our workbook so that we can do some analysis with it. To do this, perform the following tasks:

• Open the file labor.txt clicking the Office button on the upper left corner, then choosing Open, just as you would open another spreadsheet. In the Open dialog, you will have to change the “Files of type” to display Text Files so that it will display the file we want.

• Excel recognizes that the file we’re trying to open is a text file and automatically starts the Text Import Wizard dialog. This wizard allows you to control the way Excel places the data in the text file into the cells in the spreadsheet it will create. In Step 1, the default settings in the first screen are correct for our case. Click Next.

• In the second step of the Text Import Wizard you should click Comma since the data fields in the file are separated by commas. Notice that the dialog provides a preview of how it will create cells. Click Next.

• In the third step of the Text Import Wizard, accept the default settings - just click the Finish button.

• Excel creates a new workbook called labor.txt with the imported data arranged in cells. Notice that this is not a new worksheet in our original workbook—it is an entirely new workbook. Use the Window menu to move between our original workbook myModel.xls and the new workbook labor.txt.

• Copy all of the imported data into our original workbook—a worksheet called laborData has been set up for this purpose. The upper-left corner of the data range should be in cell A1 of laborData. Close the labor.txt workbook (don’t bother to save this workbook).

• In the laborData worksheet, enter the constant Year Of Operation in cell F1 and in cells F2:F36, enter formulas to compute the year of operation based on the Year Installed and the Year columns. Assume that the equipment was installed on January 1 of the year indicated. Thus, in the year that the equipment was installed the Year Of Operation should be 1, the year after installation should be 2, and so on. Name the entire data range laborData (cell range A1:F36).

9. We wish to obtain a summary from laborData that tells us how, on average, labor cost varies jointly as a function of training (Initial Training Expense) and experience (Year Of Operation). To do so, we’ll use a powerful data summarization tool called a Pivot Table.

• If you are new to Pivot Tables,

• Click the question mark button (the Help button for MS Office 2007) on the upper right corner, type Pivot Table in the search box, and press enter key.

• Explore the following topics:

• PivotTable I: Get started with PivotTable reports in Excel 2007

• Design the layout and format of a PivotTable report

• Create or delete a PivotTable or PivotChart report

• Overview of PivotTable and PivotChart reports

• Activate the worksheet called laborData in the myModel.xls workbook

• Select the range called laborData. Hint: Do this by clicking on the arrow of the Name Box - a drop-down list box in the upper left corner of the screen. By clicking on this arrow, a list of the defined names should appear. Click on laborData.

• To create a Pivot Table, choose the Insert tab. In the Tables group, click PivotTable button, and then highlight the PivotTable selection.

• In the Create PivotTable screen, select the Existing Worksheet option, then enter H1 in the box provided to indicate where to place the Pivot Table.

• In the PivotTable Field List screen, drag the button called Plant from the top of the dialog and drop it in the area Report Filter.

• Drag the button called Initial Training Expense from the top of the dialog and drop it in the area Row Labels

• Drag the button called Year Of Operation from the top of the dialog and drop it in the area Column Labels

• Drag the button called Labor Cost Per Unit from the top of the dialog and drop it in the area Σ Values. It will change to a button called Sum Of Labor Cost Per Unit. We want to use the average of labor costs across our plants, so

• Click the Sum Of Labor Cost Per Unit button in the Σ Values area, then choose Value Field Settings…

• Select Average from the Summarize by and click OK

• Click anywhere in the Pivot Table, right-click, and then choose PivotTable Options… and turn both Grand Totals off in the Totals & Filters tab.

• Examine the Pivot Table that was created. This is the way it should be set up after you’ve completed the steps below. Notice how the Pivot Table summarizes our data.

• Click the “Drop Down” list arrow beside the Plant button and select different plants.

• Exchange the positions of the buttons called Initial Training Expense and Year Of Operation by dragging and dropping. You can just drag Initial Training Expense and drop it next to Year Of Operation, then drag Year Of Operation and drop it where Initial Training Expense used to be. Notice how the content of the table changes.

• Experiment with other combinations of row, column, data and page settings. Continue to experiment with this feature—it’s a wonderfully simple and powerful tool for summarizing data! You should make sure you know how to compute and display multiple statistics (e.g., minimum, maximum, count, and average) at the same time. End by restoring page, row, column and data settings to their original values, select All from the Plant list, and insure that the Average Labor Cost Per Unit is specified. It should look like this:

10. Name the range H5:M9 laborPT. It should look like this (the word Plant is in cell H1):

11. Examine laborPT. Determine the optimal level of Initial Training Expense Per Operator (there’s one level that’s best for all years of operation) and enter this value as a number in the cell called trainingCostPerOperator in the Analysis spreadsheet.

12. In cell D13 of the Analysis worksheet, enter a function that retrieves the appropriate cell from laborPT. The function should be such that it can be copied to cells E13:H13. Then copy this formula to cells E13:H13. Hint: Don’t make this too hard - no lookup is required - you know which row to use from the previous step. Check: When salesGrowth is 3% and Price is $500, NPV should be $13,524,537.

Incorporating Price Changes:

Recall that data about price versus sales volume is contained in a file called demand.txt. This file contains several estimates of demand for each $10 price increment in the range $400 - $700. We will import this data, sort it, and explore two ways (a Database function and a Pivot Table) to compute the average estimate for each price. In this case, the Pivot Table will be more convenient, and we’ll use a lookup function in the Analysis spreadsheet to search the Pivot Table for 1998 sales volume using Price as a search argument.

13. Import the file named demand.txt and copy its data to the worksheet named demandData. The upper-left corner of the data range should be in cell A1. Close the demand.txt workbook.

14. Name the data range that you copy demandData in the demandData worksheet. It should refer to cell range A1:B373.

15. Sort demandData by Price in ascending order, then by Demand in descending order.

16. In cell D9 of worksheet demandData, enter a database function to compute the average demand for all records in demandData whose Price is 670. Begin your criteria range in cell D1. Test your formula by entering other values in your criteria and confirming that the proper average is being found by consulting the sorted table. Make sure your criteria is for 670 when you finish testing. Check: The value displayed in cell D9 should be 76636.58.

17. Copy the value of cell D9 to cell D11. To do this, select cell D9, click Copy icon from the Clipboard group in the Home tab, select cell D11, click Paste icon from the Clipboard group in the Home tab and then choose Paste Values.

18. Print Step 2: In the demandData worksheet, choose Formulas tab and, in the Formula Auditing group click the Show Formulas button. Increase the column width of column D until the entire formula in cell D9 is visible. Then select range printArea2 and print the selection in landscape with your group number and course number as a footer - ask Excel to print the entire image on a single page. Don’t worry about formatting. Then turn off the Show Formulas option.

19. We could use our database function to compute the average sales volume for each price by systematically changing the criteria value to each possible price and then recording the results in a table manually. This would be tedious and prone to error, so we instead create a Pivot Table (with no Grand Totals) in cell F1 of the demandData worksheet from the demandData range. Follow the procedure outlined earlier, but this time put Price in the Row area and the Average of the Demand fields in the data area (nothing in the column or page areas). Name the range F3:G33 demandPT.

20. Use Insert/Chart to create a chart in a new worksheet from the demandPT range. Try to make it as close to the following as possible (this is a line chart - note also the enlarged font sizes for titles and the vertical font on the x-axis):

[pic]

21. Print Step 3: Print your chart on a full page with no header and your group number and course number as a footer.

22. Your Sales for 1998 (cell D25 in the Analysis worksheet) presently depend on 1997 Sales and the Annual Sales Growth. Create a formula in this cell that will instead look up the Average Of Demand in demandPT by searching through the Price column in that range using the cell named Price in the analysis worksheet as a search argument. If you’re not sure how to do this, refer to the Help topic on the VLOOKUP command. Check cells E25:H25 to make sure that sales increase each year by the annual sales growth. In particular, the 1999 Sales figure should based on the (new) 1998 value, 2000 should depend on 1999, and so on. They probably won’t need to be changed, but check them to be sure. Check: the NPV should be $16,629,909 at this point.

23. Print Step 4: Activate the Analysis worksheet, choose Formulas tab and, in the Formula Auditing group click the Show Formulas button. Increase the column width of column D until the whole formula you just entered is visible. Then select range printArea1 and print the selection in landscape with your group number and course number as a footer - tell Excel to print the entire image on a single page. Don’t worry about formatting. Then turn off the Show Formulas option, and reset column D’s width.

Completing Your Analysis:

24. In the worksheet called Table1, construct a Data Table beginning in cell C6 that examines the behavior of NPV as annual sales growth varies from 0 to 8% in increments of 2% and price varies between $400 and $700 in increments of $50. Remember that the Data/Table... command needs its cell references on the sheet that will contain the Data Table, so you’ll have to adjust the salesGrowth and Price cells on the Analysis worksheet to refer to corresponding cells that you create in the Table1 worksheet. Your data table should look like this when you’re done:

25. Create and format a 3-D chart from the Data Table in Table1 with Price on the X-axis, Sales Growth on the Y-axis and NPV on the Z-axis.

26. Examine the 3-D chart. It appears that the optimal price is between $500 and $600. Create another Data Table in the worksheet called Table2 in cell C6 to investigate the behavior of NPV as annual sales growth varies from 0 to 8% in increments of 2% and price varies between $500 and $630 (not $600) in increments of $10.

27. Create and format a 3-D chart from the Data Table in Table2 with Price on the X-axis, Sales Growth on the Y-axis and NPV on the Z-axis. Label and format the axes appropriately, don’t include a legend, and make the chart title Net Present Value. It should look like this (note the enlarged fonts and number formatting on the axis labels):

[pic]

28. Print Step 5: Print your chart on a full page with no header and your group number and course number as a footer.

29. From the Data Table in the Table2 worksheet, it appears that there is one optimal price for all values of sales growth. Find this optimal price and enter it as a number in the cell named Price on the Analysis worksheet. Check: At 8% annual sales growth at the optimum price, NPV is $34,438,784. Hint: After you complete this step, the charts based on Tables 1 and 2 should appear flat.

30. Use the Goal Seek function in the Analysis worksheet to determine the salesGrowth required to generate an NPV of $31,000,000. When you’re done, the cell named salesGrowth should contain the required value to fourteen decimal places, and the NPV cell should be 31,000,000. Hint: If you haven’t already done so, you will have to put a number instead of a formula in salesGrowth.

31. Change the number format the cell called SalesGrowth to include all 14 decimal places, then increase the column width of column F until the whole number in the SalesGrowth cell is visible.

32. Print Step 6: Select range printArea1 and print the selection in landscape with your group number and course number as a footer - tell Excel to print the entire image on a single page. Don’t worry about formatting.

33. Save your workbook on your CD (be sure this final version is called myModel.xls), collect your printed output (2 charts, 4 worksheets), place the pages in the order they were created, and staple them together.

-----------------------

|Plant |Year |Labor Cost Per Unit |Year Installed |Initial Training Expense Per Operator |

|Toledo |1995 |88.64 |1992 |17000 |

|Van Wert |1996 |100.79 |1992 |9000 |

|Ann Arbor |1995 |73.72 |1991 |25000 |

|Jackson |1992 |94.326 |1991 |41000 |

|Kalamazoo |1994 |87.81 |1992 |33000 |

|Van Wert |1993 |124.18 |1992 |9000 |

|Jackson |1993 |86.162 |1991 |41000 |

|… |… |… |… |… |

|Price |Demand |

|560 |152356 |

|560 |152565 |

|550 |161194 |

|580 |135072 |

|650 |86082 |

|… |… |

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download