## ENGG1811 Lab 04: Data Analysis using Calc, Part II

In this lab, like the previous lab, you will learn how to analyse your data using some of the advanced functions and features offered by OpenOffice.org Calc.

Some of the skills you will practise in this lab include:

• Solving optimisation problems using Solver
• Aggregating data using pivot tables
• Using financial functions and goal seek

### Marking Scheme

Each lab is worth 3 marks: 1 mark from the online assessment and 2 marks from your tutor's assessment of your lab work.

• You must answer one online assessment question (allocated to you for this lab) during the two hours of your week 4 lab time. Complete the first lab exercise before attempting the on-line question.
• Please follow the Online Assessment link under "CourseWork" in the left panel of the class web page.
• For your tutor's assessment, you will need to have everything done in good time, including some before the class. Remember you must be ready for your tutor's assessment at least half an hour before the end of the class.

Download the lab workbook in which to complete the exercises. In the lab, save it to the Desktop or somewhere permanent: don't open it directly from the browser.

### Exercise 1: Optimisation using Solver

A plant has two processing units, #1 and #2, making products x1 and x2 respectively.
Unit #1 requires feedstocks of components A and B.
Unit #2 requires feedstocks of components B and C (see diagram below)

• To produce 1 kg of product x1 requires 0.4 kg of A and 0.6 kg of B;
• To produce 1 kg of product x2 requires 0.25 kg of B and 0.75 kg of C.
• The maximum amount of raw materials A, B and C available daily, are, respectively, 2000kg, 4300kg and 5700kg.
• Unit #1 has a capacity of 3500 kg/day and Unit #2 has a capacity of 9000 kg/day.
• Assume the net revenue after expenses for product x1 is \$1.60/kg and for product x2 is \$2.20/kg.
so,
• Set up a Solver model that allows for any reasonable combination of raw materials to be fed to each unit, and find out:
• How much of product x1 and product x2 should be produced to maximise profit, given the above parameters?
• What quantity of each raw materal is used?

We've started the analysis by putting the parameters on sheet Ex1_Factory. You will need to complete it by

1. naming the two pink amount cells ProdX1 and ProdX2 and the grey materials-used cells MatA, MatB and MatC;
2. putting appropriate formulas in cells on row 12, including names where appropriate; and
3. entering the relevant parameters in the Solver dialogue. Pay special attention to the constraints.
Note that the model shown allows for the two products to depend on amounts of any of the three raw materials, and a correct solution must use all the yellow parameter cells appropriately.

### Exercise 2: Using OpenOffice's Pivot tables

An engineering project management firm has collected a data set containing information on the last 500 completed projects that the firm has supervised. The information includes the

• Project manager
• Estimated Time (weeks)
• Actual Time (weeks)
• Estimated Cost
• Actual Cost
• Category (Commercial Construction, Residential Construction, etc)
• OnTime? - a calculated field that reports "Delayed" if the actual time is more than 5% over the estimated time
• OnBudget? - a calculated field that reports "Over Budget" if the actual cost is more than 2% over the estimated cost

The worksheet Ex2_Pivot contains 500 rows of data, one per completed project. Use a pivot table to answer the following questions. You only need to group and filter the data sufficiently so you can easily see the result. Then copy or retype the answer into the tan-coloured boxes starting at I530 (pushed well down to avoid being overwritten by the table). If you disrupt the sheet structure the assessor will only work properly if your answers are in I530:I536.

#### Questions:

1. Which project manager had the largest number of Transportation projects that were Delayed? (Hint: Category should be the page field so you can easily filter on it; row - project manager, column - OnTime?, data - any field, select the count option.)

2. What is the maximum number of Municipal Construction projects handled by any one project manager? (Hint: just refilter and inspect the table).

3. Which project manager had the highest proportion (not total) of As Budgeted projects in all categories? (Hint: right-click, edit layout, replace the column field.)

4. Which category had the smallest number of Delayed projects? (Hint: remove page field, change row and column.)

5. Which project manager had exactly two projects that were both Delayed and Over Budget? (Hint: use two column fields OnTime? and OnBudget? and filter both of them.)

6. What was the total Actual Cost for all the On-Time projects managed by Josh Degas?
(Hint: page filter on project manager, row - category, column - OnTime?, data - sum of actual costs.)

7. What was the total Actual Cost for Mary Murphy's Delayed projects, in the category that she managed most projects? (Hint: just add a data field to count projects, refilter.)

(The above data set and some of the questions are from the chapter titled "Excel Pivot Tables" in the previous ENGG1811 textbook, see course handout).

### Exercise 3: Financial Calculator

Design a very simple financial calculator for use on sheet Ex3_FinCalc. It should calculate weekly and monthly instalments for a given loan amount, loan period, and interest rate (fixed for the entire loan). Use the PMT function to calculate the required instalment amounts. Your calculator should look something like this, and you'll have to use identical cell positions if you want to use the assessor.

All amounts displayed should be positive, as labels like "loan amount", "payment" make it clear how the values should be interpreted.

### Analysis with Goal Seek

Using the Goal Seek tool and your calculator, answer the following questions (there are boxes on the sheet so the assessor can check).

1. How much could I borrow if I can afford to repay \$100 per week for 3 years, at an interest rate of 9.5%?

2. If I can afford to repay only \$150 per month, how long will it take to pay off \$10,000? Assume the same fixed interest rate of 9.5%.

-- end --