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:


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.


Download

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)


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

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 --