COMPUTER SCIENCE AND INFORMATION PROCESSING exercises

1
COMPUTER SCIENCE AND INFORMATION
PROCESSINGexercises

• S. Alessandro SARCIA, PH.D.

2
Importing tables

• Directory for the exercises files is
  \\ubz01fst\Courses\Course_Coletti\Excel_Labs
• table1.doc includes a table. Import it in an
  Excel empty workbook
• Import text_fixed.txt using fixed width
• Import text1.txt specifying the correct
  delimiters
• Set the following cell formats
• 1st column text
• 2nd and 3rd columns numbers (no decimal)
• 4th column currency (1.50 )
• 5th column percentage (1 decimal digit)
• 6th column date (17-Apr-2007).

3
Importing tables exercise

• Import the table in text2.txt
• Use the correct delimiters
• Set the following column formats
• 1st date (17-Apr-07)
• 2nd time (130 PM)
• 3rd currency ( 1.45)
• 4th text
• 5th number, no decimal digits.

4
Cell format

• Open laboratory.xlsx, sheet Economics
• Modify as follows the cell format
• Cells A5,A6 and A26 with borders
• Cells in the range D54M54 with double orange
  borders
• Cell A4 Bold, Times New Roman, red, 12pts
• Merge cells A1 and B1
• Cells D3M3 must be Bold, Underlined, 12pts
• Unhide column C
• Hide column H
• Color column M green
• Color row 54 red.

5
Cell format (3)

• Alignments and orientations
• Column B center
• Column C on the left
• Row 3 top
• Row 38 center and 45 degrees
• Text
• Fit text in D2 using wrap
• Fit text in I2 using shrink.

6
Series

• Open a new file at Sheet1
• Create in column B a list of dates (month-year)
  from January 1973 up to December 2004
• Create in column C a list of interest rates from
  0 to 3 with a 0,2 step
• Set the correct cell format before typing
  anything
• Drag the cells.

7
Mathematical operations

• Put in column D the squares of the values in
  column C
• Insert in column E a series of numbers from 1 to
  10 with step 0.5, then
• Column F log2 of column E
• Column G values of column F rounded to the
  nearest lower number with 1 decimal digit.

8
Cross-sheet formulas

• Put a random number from 0 to 1 in cell A1 of
  Sheet3
• Insert 10000 euro in cell C3 of Sheet2
• In column H of Sheet1 multiply cell C3 in Sheet2
  by the rates in column G of Sheet1and divide the
  results by the random number in cell A1 Sheet3
• Pay attention to cell addresses (use )
• Pay attention when going back and forth between
  sheets.
• Compute
• Product and sum of all the numbers in column H
• Sum of numbers in column H greater than 15000.

9
Logical functions

• Open laboratory.xlsx, sheet First
• For each non-german student, determine the
  highest mark, otherwise return -
• Divide the ID-number by the course year
• Compute the average marks of each Eng and Agr
  student, while for Polito students do not display
  anything.

10
Working with text

• Open laboratory.xlsx, sheet Text
• Compute
• The length of text in B2 and put the result in
  B3
• Put in B4 the first 10 characters of the content
  of B2
• Get the first 10 and the last 20 characters of
  the text in B2 and print them together in B5 with
  capital letters.

11
Matrices determinants

• Open laboratory.xlsx sheet matrices
• Compute the following determinants
• C7det(Ma)
• H7det(Mb)
• M7det(Mc)
• Use copy-paste Excel automatically modifies the
  references.

12
Matrices operations

• In G9, print the result of MbMc
• By default Excel shows you only the top-left cell
  of the resulting matrix
• To show the whole matrix
• Select the 4x4 area of the matrix
• Press F2
• Press shiftctrlenter.
• In G14 invert the matrix (MbMc) matrix in
  G9J12 and show the whole matrix.

13
Dates and Time

• Open a new sheet and call it DateTime
• Put
• A1 today
• A2 7 days after today
• A3 exactly 2 months after the date in A2 (use
  DATE)
• A4 exactly 1 year before the date in A2 (use
  DATE)
• A5 A2 at 6 pm (change cell format)
• A6 difference (in days) between A4 and A3 (cell
  format numbers)
• A7-A56 sequence of static dates 1 Mar 2007, 6
  Mar 2007, 11 Mar 2007,

14
Cash Flows

• An investor receives 4000 from a bank. He has
  to pay 150 each month for 2 years (24 payments)
  plus a final 1000 payment. The investor pays
  also 10 for administrative expenses when he
  receives the money.
• Insert a new sheet and call it CashFlows
• Create the cash flow of the investment
• Compute the NPV for a discount rate of 2,5.

15
Cash Flows (2)

• Compute the NPV for a sequence of different
  interest rates (from 0.5 to 20 with a 0.5
  step)
• Hints
• Create a list of interest rates
• Write the XNPV function for the first one and
  then drag it
• Take care of references.

16
IRR

• Compute the IRR of the previous investment
• Use XIRR
• Compute the IRR of the following investments
• 110000 received and paid back in 80 payments of
  1500 every three months plus a final payment of
  6000.
• You give 3000 to an investor and then you
  receive yearly 5 payments of amount 500.

17
Constant payment loan

• Open a new sheet and consider a loan of 10000
• Given 14 payments of amount 989, in B2 compute
  the TAN (yearly net rate) using RATE function
• Given 14 payments and a 5 TAN, in B3 compute the
  amount to be paid yearly using PMT function
• Using cash flows and XIRR, check the results of
  the two previous points (column C and D)
• Given payments equal to 989 the result of XIRR
  must be the same as RATE()
• Given payments equal to the result of PMT, the
  value returned by XIRR must be 5

18
Constant payment loan (2)

• Given a 6,75 TAN and a maximum affordable
  payment equal to 746, in B4 determine the number
  of payments
• Use NPER function.
• Given a 6,75 TAN and 14 payments, from B5
  compute the amount of required interest year by
  year
• Use IPMT function.

19
Monthly payments loans

• Given a 6,75 TAN and 1412 monthly payments,
  compute in B6 the amount to be paid (monthly)
• Given a 6,75 TAN and assuming that the investor
  pays monthly an amount equal to 100, compute in
  B7 the number of payments required.
• When you deal with payment periods shorter than a
  year remember to convert the yearly rate to the
  correct rate

20
XIRR

• What happens when there is more than one IRR?
• Consider the cash flow in the sheet called
  finance
• Find an approximation of the IRR using a sequence
  of rates and NPVs
• Use the XIRR function
• XIRR behavior is not reliable when there are
  several IRR.
• When you create a cash flow, try to avoid
  multiple sign changes.

21
Bonds-1

• Find on the Web the current quotation for bond
  TELECOM 11EUR 4,50 ISIN code XS0184374063
• Fixed coupon 4,5 every 12 months
• Maturity date 27/1/2011.
• Compute
• IRR
• Price to pay to get a 6 IRR
• Use Yield and Price.
• http//www.borsaitaliana.it/quotazioni/obbligazion
  i/obbligazioni/obbligazioni.htm

22
Bonds-2

• Repeat the previous exercise with the following
  bonds
• BTP-1AG15 3,75, ISIN code IT0003844534
• Fixed coupon 3,75 every 6 months
• Maturity date 1/8/2015.
• CTZ-31ST10, ISIN code IT0004413909 
• Zero coupon
• Maturity date 31/12/2008.
• In the last case build a cash flow!

23
Bonds - 3

• Consider bond with ISIN code IT0004060783http//w
  ww.mediobanca.it/files/66/123/Reg_IT0004060783.pdf
  
• Buy date 15/06/2006
• Maturity date 15/06/2012
• Coupons 2,90 3,00 3,15 3,30 3,60
  3,80
• Current quotation 93,26
• Compute price to pay on 15/06/2006 in order to
  get an IRR very close to 5.
• Compute IRR if bought today at current quotation.
• Hint build the cash flow and use XIRR and
  GOAL_SEEK

24
Bonds for homework

• Consider Unicredit bond with ISIN code
  IT0003765291
• Buy date 20/12/2004
• Maturity date 20/12/2010
• Coupons 2,50 2,60 2,75 2,90 3,10
  3,35
• Current quotation 99,07.
• Compute price to pay on 20/12/2004 in order to
  get an IRR very close to 6.
• Compute IRR if bought today at current quotation.

25
Some statistics

• Open laboratory.xlsx, sheet statistics
• For each sequence compute
• Average, median, maximum, minimum, standard
  deviation
• Correlation and Covariance between two sequences
• Open laboratory.xlsx, sheet probabilities
• For each value x and for a normal distribution
  with mean 1.5 and standard_dev4 compute the
  area from 8 to x (use NORMDIST).
• Using the same normal distribution, put in cell
  D4 the x corresponding to probability 95 (use
  NORMINV)

26
Goal Seek

• Open a new worksheet. In column A put variable x,
  which is a sequence of 101 values from p to p,
  with step 2p/100.
• Compute
• In column B
• In column C
• In column D
• Insert a new row at the beginning and manually
  type -3.14 in cell A1. Using Goal Seek, for each
  of the functions find
• For which value they are equal to 0
• For which value they are equal to 2.

27
Conditional Formatting

• Open file laboratory.xlsx sheet cond_format
• Highlight all the cells containing the word Dog
• Select the Favorite Pet column
• Click the Conditional Formatting button
• Choose Highlight Cells Rules ? Equal To
• Type the word Dog, select the color and press
  OK
• Apply a color scale from light green to dark
  green to the column Weakly Allowance.
• Put a red circle when the value is 20, yellow
  when 10 and green otherwise.

28
Playing with worksheets

• Open laboratory.xlsx
• Create a copy of the sheet economics
• Name duplicate
• Tab color green
• Move it at the end of the sheet list.
• Lock the first two rows of the sheet
• Use split and freeze.

29
Print as PDF

• Open laboratory.xlsx
• Print the sheet duplicate with the following
  features
• Show the gridlines
• Landscape
• Fit the sheet in 1 page
• Header your name in the middle
• Footer time on the left, computer in the
  middle and date on the right.

30
Mathematical graphs

• Open laboratory.xlsx Sheet3
• Create a mathematical graph representing f2(x)
  (SCATTERPLOT using only lines).
• Set the following options in the graph
• red and very thick line
• Thick axes, correct format of the values
• y-axis font blue, times new roman, 11 pts.
• Insert a text box
• Text Zero Crossing
• Box 1 pts black
• Background white
• Font times new roman, 12 pts
• Arrow from the box to the zero crossing points.

31
Graphs

• Add the other two functions to the same graph
• Include a grid
• Use different colors and different line types
• Scatter plot VS lines
• Plot functions using line instead of scatter
  plot and see what happens to the x axis.
• This is not a mathematical graph.

32
Bar plots

• Open the sheet called fruits in laboratory.xlsx
• Create a bar plot that represents for each fruit
  the tons sold in 2006
• Features
• Green bars
• White background
• Horizontal step set to 20 tons
• Values on the right of each bar
• No legend
• Title and labels on each axis

33
Bar plots (2)

• MODIFY the charts including also data on 2005 and
  2004 productions
• 2005 and 2004 bars adjacent to 2006 bars
• Colors red for 2005 and yellow for 2004
• Insert a legend
• Use overlapping bars (30)
• Put the chart in a new sheet.

34
Pie charts

• Open file laboratory.xlsx and take the labels in
  Pie Labels and the data in Pie Data
• Create a pie chart
• Manually insert the labels using select data
• Legend at the bottom
• Data category labels outside the pie

35
Sheet protection

• Open the file laboratory.xlsx sheet pivot
• Apply a password sheet protection to the whole
  sheet except for the party column
• Solution
• Go to Review ? Changes
• Click on Allow Users to Edit Ranges and select
  New to free the party column
• Click on Protect Sheet and type your password
  twice

36
Sorting

• Open laboratory.xlsx sheet Sorting
• Order the small database by
• Sales, descending
• Surname, ascending
• Surname and then name (since there are duplicate
  surnames)
• Now include Headers (unselect My data has
  headers) the sorting messes up your database!

37
Diagrams

• In a new sheet
• Build the university structure diagram
• Organization charts
• Rector, 3 deans, 10 professors and 5 secretaries
  (between deans and professors as assistants).
• Build a pyramid diagram
• People with PhD, master, bachelor, high school,
  middle school.

38
Pivot Table and Charts

• Open the file laboratory.xlsx sheet pivot
• Exercise 1
• Build in a new sheet a pivot table with age
  groups as rows, parties as columns, and vote
  count as cells
• Build a stacked column diagram
• Exercise 2
• Build in a new sheet a pivot table with parties
  as rows, ballot status as columns and vote count
  as cells
• Remove ABT and (empty) columns
• Build a pie chart for party including all ballot
  statuses

39
Pivot Table and Charts

• Solution of exercise 1
• Click Insert?PivotTable and select your Table
  Range
• Select Radio button for New Worksheet then click
  OK
• Drag an item such as AGE GROUP from
  the PivotTable Field List down to the Row
  Labels quadrant.
• Drag the PARTY field from the PivotTable Field
  List to the Column Labels quadrant.
• Drag the PARTY field to the Values quadrant.
• Click on the button PivotChart and select stacked
  diagram.
• Solution of exercise 2
• Click Insert?PivotTable and select your Table
  Range
• Select Radio button for New Worksheet then click
  OK
• Drag an item such as PARTY from the PivotTable
  Field List down to the Row Labels quadrant.
• Drag the BALLOT STATUS field from the PivotTable
  Field List to the Column Labels quadrant.
• Drag the PARTY field (or the voter field) to
  the Values quadrant, paying attention that COUNT
  appers.
• Click on the button PivotChart and select the pie
  chart. In order to have all ballot status, select
  all of them from the chart window.

40
Statistics with Excel

• Warning Analysis ToolPak add-in must be
  installed!
• Office button ? Excel Options ? Add-Ins ?
  Analysis ToolPak ? Go ? Analysis ToolPak
  (Analyse-Funktionen)
• Open the file laboratory.xlsx, sheet
  probabilities
• Display descriptive statistics (Popolationskenngrö
  ßen) for x
• Display an histogram for variable x, paying
  attention to
• Histogram intervals (you must specify them
  directly)
• Distance among bars (reduce it to 0)