This is a Clilstore unit. You can .

CALC PRACTICA 2

# ACTIVITY 2

## ACTIVITAT 2.1. ABSOLUTE AND RELATIVE REFERENCES TO CELL

Start LO Calc. Open your personal template and select the sheet 2.1 Planetes

We are going to study the differences between relative and absolute references. A reference to cell is a formula written in a cell which uses as operands the values contained in other cells. In the image below inserting the formula =J4 in L7 the contents of J4 will be copied to L7

In addition, we can use the cell extensor to propagate the contents of a cell to others. In the following image, the cell extensors is the black square in the down right corner of the cell, highlighted at the beginning of the red arrowand. Dragging it to the right of L4, its contents are propagated to the nearby right cells.

Notice that the propagated contents are not the formula =J4. You might expect to get the word 'Mercury' propagated to the nearby cells, but as you can see in the following image, that's not what happens.

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.1.1

To figure out better what's happening, we need to see the formula in the cells instead of their value. Select the menu Ferramentes | opcions | vista and then click in the box visualitza les fórmules

This action makes the cells show the formula instead of the values they represent. Notice that the action of dragging the cell extensors doesn't propagate J4 to the nearby cells. Instead the column number is increased in 1 in each cell where it is propagated resulting in the values J, K, L, M…..while the number of row (4) remains unchanged

The cell extensor can be dragged to up and down, producing a similar but different result:

• Before carry on with the activity create the following table starting in J9,

• Put the cursor in Q9 , insert the formula =O9 and drag down the cell extensor up to Q14. Notice that the action of dragging the cell extensors down doesn't propagate O9 to the nearby cells. Instead the row number is increased in 1 in each cell where it is propagated resulting in the values 9, 10, 11, 12….. while the number of column (O) remains unchanged

There are three different types of references : relatives, absolutes and mixeds.

• In a relative reference, for example =O9 in the cell Q9, the formula expresses a distance between the cell where it is written (Q9) and the cell referenced in the formula (O9). In this case the distance is -2 columns, 0 rows, because Q9 and O9 are at the same row but at a distance of 2 columns. In the case of the formula =J4 in the cell L7, the distance is +2columns, -3 rows as seen in the images below:

extending one cell to it's nearby cells means to write on it the formula referencing the cell at the same distanceFor example. In Q9, =O9 states a distance is (-2 col.,0 row.). extending it to Q10, we get the formula =O10, that is Q- 2. 10+0. .

• In an absolute reference, when the cell is extended using the cell extensor, the referenced cell doesn't change. To define a reference as absolute write the character '\$' before the number of row and column. For example =\$O\$9. In the image below is shown what happens extending down the cell Q9 whith an absolute reference. The propagated formula is always =\$O\$9.

•

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost. Save a version and call it 2.1.2

• In a mixed reference, the absolute reference marker, the character '\$' is written before the number of row or column but not in both. In the following examble =\$L9, the reference to the colum (\$L) is absolute and doesn't change while being propagated. However the reference to the row is relative, and states a distance to the referenced table, and therefore it might change while being propagated

• The other type of mixed references is shown below. In this case the column is relative and the row absolute, for example =L\$9. Thence the Column changes while being extended the cell and the row remains invariable

Make 4 copies of the table solar systema, in different parts of the sheet 2,1 planetas, using the four type of references

• Put in a cell a relative reference to the cell J9 and then extend to get a relative copy of the whole table

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost. Save a version and call it 2.1.3

• Put in other cell an absolute reference to the cell J9 and then extend to get an absolute copy of the whole table

• Repeat in other cell using a mixed reference (absolute column, relative row)

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.1.4

• Repeat in other cell using a mixed reference (relative column, absolute row)

MAKE A BACKUP COPY OF YOUR ACTIVITIES FILE. Open the file browser Nautilus using LLocs | inici. Open your calc activities folder. Select your Calc activities file and copy it (ctrl+c) and paste it (ctrl+v) in the same folder. This will create a perfect safety copy of your activities file, keeping in it the versions. Carry on working with the original file. Don't upload it to Moodle until you end the last activity

## ACTIVITY 2.2. LEASING

In this activity we are going to make the amortization table of a vehicle purchased using a leasing. Use the sheet 2.2 Leasing

• Input the data between the cells A3:B6 and format them as shown in the image bellow. Use the menu option format | cel·la . It's important to state that except the values in the range A3:B6, you shouldn't write manually the any other value . We will use different formulas to make the rest of the table

• Insert manually the headers of columns in A7:I7

•

• Format the table, setting a border line, and area colors. Use the button , or use the menu option format | cel·la |  fons, to give a pink color to the area of the cells in the row 7

• Use the drop down button or use format | cel·la | bordes to set the visible cell borders

• Align all cells contents to the center using the button .

•

• Set money format to the values in B7:I45 using Format | cel·la or the button

• The result must look like the table in the image below

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.2.1

• The cells B6 and B4 must be formatted as percentages using the menu option Format | cel·la solapa números | percentatge)

• In E8 insert the value of the lent capital. So we must insert there a relative reference to B3

• In the range A9:A45 you must write the number of the monthly payments. Fill up A9,A10,A11 with the values 0, 1, 2, select these three cells and drag down the cell extensor until A45.

• In B9 we must calculate the monthly payments, using the formula  . Notice that B3 has a negative sign (because it represents debt) and the number of periods are B5+1 since in a leasing there's a 0 payment executed the moment the leasing is formalized. If your Calc is set to Spanish castillian use the function PAGO with the same parameters.

 Tassa: interest rate Nper: Loan duration in number of years Va: Capital pending of payment Vf: Final value(Optional) tipo: Type of formula used to perform the calculation. In this case just write 1

• The value calculated in B9 is the amount of the monthly payments and wont change along the leasing duration. We should extend it down to B45, but its important to keep in mind that the loan duration is variable, and will depend on the value in the cell B5. For example if B5=14, B9 will show the first payment, B23 the last one and B24 must be empty. We can use the function Valor(text) to turn a text into a number. The function =Si(valor(AX)<=B5;B9;””) writes B9 if the number of payment in the column A is lower than B5 (number of periods). For example in B10 you must write the formula...

• Drag down the cell extensor in the cell B10 untill B45. This wont work as expected: in B11 the formula has changed to  . And in B6 there is the IVA not the number of payments. This has happened because the reference to B5 in the formula was a relative reference. As told before relative references are distances to the referenced cell, meaning that when you propagate the formula =Si(valor(A10)<=5;B6;B9;””) from B9 to B10 , the parameters on it change: B5 turns into B6 and B9 turns into B10, etc. We have to rebuild the formula in B10 using absolute references instead of relative ones

•

IN CALC BY DEFAULT ALL REFERENCES ARE RELATIVE UNLESS WE USE THE ABSOLUTE REFERENCE MARKER \$

• In other words...

\$A1
Absolute Reference to column: we stick to column A when the cell is extended.

• Relative reference to row: The row changes if extended vertically

A\$1

Relative Reference to column. The column changes if extended horizontally
Absolute Reference to row: We stick to row 1 when the cell is extended

\$A\$1
Absolute Reference to Column: we stick to the column A when the cell is extended
Relative Reference to row. We stick to row 1 when the cell is extended

• Coming back to B10, let's modify the formula to   and let's extend the cell to B45

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.2.2

• After the first payment (the payment 0, we will have amortized a first amount of money. Write in D9 the formula=B9

• And as a consequence, the pending capital must be reduced in the amount paid,so write in E9 the formula =E8-D9

• The interest is the part of the monthly payments, which is paid as interest. The interest can be calculated using this simple formula Interest= pending capital* interest rate. In our case insert in C10 the formula E9*\$B\$4. Then extend until C23.

• The amortized amount (column D) is the part of the monthly payment which is used to pay back the lent capital. Amortization= Monthly payment (column B)- interest (column D).

• In D9 insert =B9-C9

• Extend D9 to D23

• The pending capital (column E) is the amount still unpaid

• Initially its value is the lent amount, so in E8 write =B3.

• But from the first month onwards the amortized capital (column D) must be calculated subtracting it from the previous pending capital. That is in E9 insert =E8-D9

• Extend the cell E9 until E23.

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.2.3

• The VAT rate (el IVA) to apply to this leasing is in the cell B6. To work out the VAT to be applied to each monthly payment, in the column G, multiply B6 for the amount paid that month ( in the column B). For example in G9 write =B9*B6 and extend to G23. Remember, it won't work if yo use relative references to B6. You must use an absolute reference, and you are supposed to know how to do it

• The column Cuota final (column H) is the the monthly payment plus the VAT. That is, in H9 write =B9+G9 and extend to H23

• The column I will show the accumulated amount of money paid in the leasing. This means add every month to all the money previously paid, (in column H). In I9 insert =I8+H9 and extend to I23

The accumulated capital (columna F) is the sum of amortized capital in each monthly payment. In F9 insert =F8+B9 and extend to F23

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.2.4

• This table is useful for a 14 months leasing. We are going to extend it so that we can analyze leasing operations of up to 36 monthly payments. Select B23:I23 and extend it until the row B45:I45. Look at the image below, there are #VALOR! Errors in all the cells under the 14ht payment. This happens because after the 14th payment the leasing is over and the formulas have negative of null values

•

• Actually we know how to solve this problem. Indeed we have already done it in the column B. What you have to do is to replace all the current formulas by a an If formula as For example in I9 we had =I8+H9. Replace it by  . and then extend it down to I45.

• Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost. Save a version and call it 2.2.5

• Extend B9:H9 to B45:H45 with a formula. All the #VALOR! errors must disappear

• Test out the table. Change the values in B3:B6 and check out if the loan numbers are recalculated

MAKE A BACKUP COPY OF YOUR ACTIVITIES FILE. Open the file browser Nautilus using LLocs | inici. Open your calc activities folder. Select your Calc activities file and copy it (ctrl+c) and paste it (ctrl+v) in the same folder. This will create a perfect safety copy of your activities file, keeping in it the versions. Carry on working with the original file.  Don't upload it to Moodle until you end the last activity

# ACTIVITY 2.3. VIABILITY STUDY OF AN INVESTMENT

For this activiy use the sheet 2.3 estudio de viabilidad

A company is studying to purchase a robot to improve the production. The company can choose an alternative investments which my produce an estimated return of 9%. A financial analyst must study if the investment in arobot will be profitable in a period of 10 years. The company will decide to purchase the robot if it is more profitable than the other investment. After analyzing all available information the financial analyst comes to the following conclusions

• The cost of the initial investment is 684,512 €

• The investment will generate an income the first year of € 70,430.6 The second year is expected to increase the income to € 225,413. The third year the income will increase by 25%, (regarding to the second year). The 4th year will increase by 7%, and another 5% by the 5th year. After 5 years, the income generated will be reduced by 3% each year

• The initial maintenance costs will be € 71,346. The deterioration due to use will increase the maintenance costs of the robot 8% each year

• The robot value suffers an annual depreciation of 10% of the purchase price. Therefore the tenth year of the project the robot will be scrap and worth nothing. Keep in mind that if the company decides to abandon the project in the second year, the only way to recover the investment will be to sell the robot for its residual value at that time. For this reason, the cost of depreciation is very important and should be considered as an expense or a cost in each annual balance of the project.

Depreciation= Purchase price*0,10

Save the document. Save a version and call it 2.3.1

• The sum of costs per year must be shown in the table.

Sum of costs = Maintenance costs + Depreciation

• The profit before and after taxes must be obtained per year
Profit before taxes = Generated income –
sum of costs

• To calculate the profit after taxes, the rule is: If the profit is positive (the robot has generated profit instead of losses that year), a profit tax of 30% will be applied. If the robot generates losses, no tax will be applied. Notice that you need a SI function to correctly work out the profit after taxes. So:

• profit after taxes=profit before taxes - taxes

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost.Save a version and call it 2.3.2

• Calculate the Free Cash Flow or FCF (Flujo de Caja Libre) which measures the capability of a company to generate cash. In this case the FCF is worked out adding the depreciation to the profit after taxes.

• FCF year 0=-684.512€

• FCF rest of years=Profit after taxes +Depreciation

• Finally, the key of the viability of the investment is the calculation of the IRR (TIR in spanish) of the investment. The internal rate of return (IRR)or Tasa Interna de retorno (TIR) of an investment is the geometric average of the expected future returns of that investment. The IRR can be used as an indicator of the profitability of a project: the higher the IRR, the greater the profitability. The company is analyzing an alternative investment with a calculated IRR of 9%. As a result, to be acceptable this project must generate a TIR above 9%.

• To calculate the IRR you have to apply the LO Calc TIR function over the range of cells in which is located the FCF of the project. In the case of the image below in B20 write TIR (B18: L18)

Save your template and make a backup copy. Don't upload it to Moodle until you end the last activity

Save the document using the save button or Archivo | guardar. Never use Save as (archivo |  guardar como ) otherwise the previous versions in your document will be lost. Save a version and call it 1.4.1

MAKE A BACKUP COPY OF YOUR ACTIVITIES FILE. Open the file browser Nautilus using LLocs | inici. Open your calc activities folder. Select your Calc activities file and copy it (ctrl+c) and paste it (ctrl+v) in the same folder. This will create a perfect safety copy of your activities file, keeping in it the versions. Carry on working with the original file.  Don't upload it to Moodle until you end the last activity

Short url:   http://multidict.net/cs/6292