Advanced Excel practical work

COURSE OBJECTIVES
The sessions dedicated to this course will allow an approach to the
notion of spreadsheet, complementary tools and very common in software of
word processing and file management. The main objectives that guide the
The course outline is as follows:
Knowledge of a standard management, simulation and support tool
decision
Appropriation d’un outil de présentation de tableaux encomplètements des
text processing and better suited.
Appropriation of a graphical representation tool knowledge of a tool
very powerful yet simple calculation (numerical, accounting, financial...)
Approach to a programming mode of computational algorithms, very
different from classical programming.

At the end of the training, the student must have:

A broad knowledge of new computer techniques;
Bases for understanding future developments of
computer science and a mastery of information technologies in their
both methodological and operational aspects.
The main functions of office software:
He must be able to:
To analyze the activities of managers;
To handle a training;
Modeling and forecasting;
Make decisions;
Provide a general presentation of office tools and;
Conduct an analysis of the company's needs.

Guinean Agency for Training and Development - University of Conakry Page 1

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link]él :625 23 07 02 / 657 54 90 80
 Advanced Excel Practicals

Conditional formatting (Inserting an alarm)

To see at a glance if your Objectives are achieved, use a special formatting for
the values that meet the specified conditions. This is also called insertion of a
alarm.
To do this, select the value range, click Home/Style/Conditional Formatting.
Then follow the instructions in the dialog box.

1)-calculate the tax

2) - calculate the profit
3) Identify the quantities less than or equal to 50
4)-Find the number of products supplied by Importex
5) Find the number of products for which PAU is greater than 5000
6)- Format the columns containing amounts in Monetary Style (GNF)
7) Calculate the average unit price of tomatoes
The average difference between the various prices of tomatoes.
1)-Enter the same value in multiple cells:
Select the cells
Enter the text and validate with Ctrl + Enter
2)-Create your own data
Click file (Office button)
Click on Excel Options
Click on Edit custom lists

Guinean Agency for Training and Development - University of Conakry Page 2

Eng. Alhassane Djamal Touré Email:thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Click on add
Enter the list by clicking on OK then OK
3)-Simultaneously entering data on multiple sheets (creating a working group)
Select multiple sheets, Enter your data, format the active sheet
according to your wishes.
To complete your simultaneous input, click on an unselected tab, and click with the
right-click and choose unbind the sheets
4)-Name a range of cells
Sélectionner les cellules
Click on the formula tab
Enter to define a name
Enter the name and validate by clicking ok

5) Protect the data of a sheet

Note: protecting a sheet prevents a person from deleting a calculation formula.
Click on the Review tab 'Protect the sheet...'
Type a password, confirm it and validate
This form of protection applies to the content of cells, on objects, and on scenarios.
6)-To not protect certain cells
Before protecting the sheet, select the cells for which the modification remains.
authorized. Multiple selection is possible.
Click on the home tab /format
Click on the cell format
Click on the protection
Disable the Locked box
Click on Ok
Then click on the Review tab and Protect Sheet to protect the rest of the sheet.
7) Remove a protection
Click on the Review tab
Click on remove sheet protection
Enter the password

8)-Locked commands
When a sheet or workbook is protected with Review / protect the sheet the command
cell format is inaccessible. It is also impossible to insert cells, rows or
columns this so as not to disrupt the structure of the sheet likewise in the Data menu the
Orders pull, subtotals etc. are disabled.

9) Protect a file
Click on file (Office button)
Click on save as...
Roll out Tools - Choose General Options...
Enter a password to protect the document and confirm it.

Guinean Agency for Training and Development - University of Conakry Page 3

Ing. Alhassane Djamal Touré Email:thassanedjamal24@[Link]él :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Validate the Save As dialog...

10) Data consolidation

Data from multiple sources (sheets, workbooks) presented in the same format
can be introduced into a summary sheet in order to be consolidated (grouped).

Example 1:
Using a spreadsheet per month, enter the tables below.

January

February

Determine the total sales by month?

On a fourth sheet, make the consolidation by determining:
The total quantities and sales;
The average of the quantities;
The maximum unit price.

Example 2:
Suppose a trading company with two branches SA, SB for which we want
Group the monthly sales. Individual monthly sales can be on sheets.
from the same workbook or from different workbooks. The model for data presentations
is the following:

Guinean Agency for Training and Development - University of Conakry Page 4

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link]: 625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Binder 1

Binder 2

To group these sales

Open all the relevant workbooks,
Activate the sales summary sheet
Click on Data, Consolidate
Choose the sheet of the company SA, then select the grouping area
Choose a summary function (in this case Sum).
To indicate the reference in the source area; one can click on the window to choose a
invisible binder. If the relevant binders are arranged in a mosaic, it is easier.
Choose "Add"
Introduce all references in the same way
If a workbook is not open, we choose the 'browse' button to open it and select a range.
Click OK to close the dialog box and display the results.
Note: the 'link to source data' checkbox automatically updates the table of
consolidation if the source data is modified.
Invoicing
Etablir une facture à l’adressedu client Alhassane Djamal TOURE qui à achète les articles
Following:
The VAT to be added is 18% of the price

Guinean Agency for Training and Development - University of Conakry Page 5

Ing. Alhassane Djamal Touré E-mail:thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

The pivot table

The pivot table allows you to quickly create a summary table.
tableau is dynamic means that you can add, remove, and modify the
presentation of the table.
Let's propose an exercise that consists of creating a pivot table that provides the total of
salaries according to gender and the position held by the employee in the company.

Creation of a dynamic table

Place the pointer on any cell between A1 and G16.
From the Data menu, select the PivotTable Report option. Excel will...
request where the source of the data is located that will be used to compose the cross table
dynamic. This data can come from four different sources.
Excel then asks you what type of report you want; table or chart?
This version of Excel allows not only to generate a table but also a chart.
dynamic.
Press the following button
Make sure the selected cells are between A1 and G16.
Click on one of the cells in the table
Guinean Agency for Training and Development - University of Conakry Page 6
Eng. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] winter : 625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Click on the insert tab

Click on pivot table
In the context menu, confirm by clicking OK

You can place the fields you need in four different areas: Filter of
rapport, Etiquettes de colonnes, Etiquettes lignes et Somme des valeurs.
Sum of values: this area displays the results you want to see for a .par field.
défaut, le tableau affiche la somme des valeurs si celui-ci est composé dechiffres. S’ilest compose
The table will display records that meet the criteria.
Column Labels: displays each value of a field in its own column.
Line Labels: displays each value of a field on its own line.
Report filter: allows you to 'filter' the values of the table based on the values of a field.
This allows you to see only the records that meet a certain criterion.

Questionnaires
Place the fields to answer the different questions
Question 1: find the total of all the salaries of the company.
Holding a finger on the left mouse button, move the salary field into the area
Sum of values. Release the mouse button as soon as the box for the salary field is...
on the area above the range of values.

Guinean Agency for Training and Development - University of Conakry Page 7

Eng. Alhassane Djamal Touré Email:thassanedjamal24@[Link]él :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Question 2: What is the distribution of this amount by title within the company?
Keeping a finger on the left mouse button, move the title field into the area of
The label lines. Release the mouse button as soon as the square for the title field is done.
above the column area.
Question3 :répartir le total des salaires par titre et par Sexe.
While holding a finger on the left mouse button, move the gender field into the area of
The column labels. Release the mouse button as soon as the square for the title field is
above the column label area.
Question 4: give priority to the title field over Gender.
Place the pointer over the title field of the column area of the pivot table.
Keeping a finger on the left mouse button, move the title field in front of the field
sex.
Once in front of the gender field. Release the mouse button. See the data.
Question 5: What are the records of the total administrators?
-(153 500)
Double-click on the cell.
Question 7: Determine the total number of women
The pivot table allows you to 'mask' or hide the values that you do not have.
No need. In this case, men must be hidden.
To the right of the sex field, click the button with a downward pointing triangle. By
For example, there are only two possible values: F or M.
Question8 :montrer les valeurs des employés qui sont de la catégorie 3.
Click on the button with a downward-pointing triangle to the right of the category field.
From the list of possible values, select value 3. Press the ok button.
Note: Move the Name field below the Title field in the Rows area.
Move the first name field above the last name field in the rows area.
The IF function: consider the table below:

1)-calculate the average and display:

Assignor, if the average is less than 5;
Passable if the average is below 6;
Satisfactory if the average is below 7;
Guinean Agency for Training and Development - University of Conakry Page 8
Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] winter : 625 23 07 02 / 657 54 90 80
 Advanced Excel Practical Work

Well, if the average is below 8;

Very well if the average is above 8.
A student is admitted if they have a score higher than 7.5 in Algorithms OR if they have an average.
greater than 5.
Syntax: Enter the formula in cell G3: =IF(OR(B3>7.5, E3>5), "pass", "repeat")
Then validate. Copy the formula to fill the column.
3) Display the rank of each student based on their average
In H3 enter +Rank ( }
Click on cell E3 to select the 1ername to be classified
Select the range E3:E8, press the F4 key to make the references absolute.
beach.
Type to classify in descending (or ascending) order of grades.
Don't forget to separate the function parameters with semicolons.
THE [Link] FUNCTION
The SUMIF function adds specified cells according to a certain criterion. For example, a
sale of land with the commissions that the seller will receive is as follows:

We want to calculate the amount of the commission if the sale of the land is above 1200.
000 GNF. To select the SUMIF function, click on the Insert menu and then function. If the
function in the category of Maths and Trigonometry. A dialog box opens where:
Beach: these are the data that will allow us to perform the calculation i.e. A4:C4
Criterion: it is the condition for a calculation to occur, i.e. > 1,200,000 GNF
Sum range: Addition to the calculation range, based on the criterion, i.e. A9:C9
Note: the syntax for sum if is as follows:
Sum if (cell_range_to_compare; cell_range_to_sum)
MATRICES AND MATRIX CONSTANTS:
Array formulas act directly on an array of values. An array formula
can result in a table or a unique numeric value. These formulas can be identified
accolades (introduced Ctrl+Shift+Enter).

Guinean Agency for Training and Development - University of Conakry Page 9

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Exercise 1

In cell C4, calculate the total as follows: =SUM(B2:B3*C2:C3) instead of pressing Enter.
on tape Ctrl+Shift+Enter
In the matrix formula above, if instead of typing B2:B3: you type {10 ; 15} then {10,15} is
matrix constant called; thus a matrix formula can contain a constant
matrix
The formula above would be=SUM(10:15*C2:C3)

A matrix formula can generate multiple values. For example

To calculate the benefit obtained for each item, instead of typing the formula in E2,
then copy it to the other cells, select the range E2:E5, and then introduce the formula
array{= (D2:D5-C2:C5)*B2:B5}. Then Ctrl+Shift+Enter.
Note: to remove an array formula and keep only the values, copy it to
the clipboard and use Special Edition-College and choose values, in the dialog box
special collage.

Formula audit:
To create an audit of a formula, click on the cell containing the formula to be audited:
Click on the Formulas menu and then hover over Formula Auditing
Click on Identify precedents (to know the cells involved in this)
formula) or click on locate on the dependents (to know the formulas in
which are involved in this cell).

Creation of custom menus:

It is possible to delete menus, menu items, or create new ones.
custom menus by the menu editor are only available in the workbooks where they
are created.
For a menu: choose–File–Customize the Ribbon–New Tab. In category, choose
new menu, and in the orders area, click and hold down then drag new
menu up to the place where you want to place it. for example between Data and Window. And for

Guinean Agency for Training and Development - University of Conakry Page 10

Ing. Alhassane Djamal Touré Email:thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80
 Travaux pratique Excel avancé

rename this new menu, right-click, and in the Name field: type the name of the menu.
For example, organizational chart.

And to insert the submenus, still in category commands. Choose for example:
insertion and in the Commands–categories–choose Hierarchical Flowcharts zone, which must
pull and place.
For example, between Data and Window. And to rename this new menu, click the button.
right, and in the Name area: type the name of the menu. For example, Organizational Chart.
And to insert the submenus, still in Category Commands. Choose a category by
example: Insertion and in the command area, choose a category to drag and place in the
menu organigram. And so on for the other submenus.
To delete a menu, select it and click the "Delete" button to restore the
In the standard Excel menus, click the "Restore All" button.

THE REFERENCES
1- Relative reference: A relative reference is dependent on the position of the cell.
active.
Example: B1 is a relative reference
2- Absolute reference: An absolute reference is independent of the position of the
active cell.
$B$1 is an absolute reference, invariant when copied.
Example of compound interest calculation:
Mr. Touré has placed his savings, 1000 GNF, in a savings book, listed at 3.5% per year.
How old will he be next year?
Réponse :L’anprochain il aura 1000+1000*0,035. En Excel. On obtiendra :

This sum of 1035 euros obtained at the end of year 1 will earn interest during year 2. At the end
from this 2, he will have on his booklet the capital 1035 GNF plus the interests of the year 1035*0.035. And so
next...
Capital year at the beginning interests new capital
Of the year at the end of the year

0 1000 1000*0.035=35 1000+35=1035

1 1035 1035*0,35 1035+1035*0,035
2 1035+1035*0.35(1035+1035*0.035)*0.035…

Guinean Agency for Training and Development - University of Conakry Page 11

Ing. Alhassane Djamal Touré E-mail:thassanedjamal24@[Link]: 625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

The calculation is therefore always done with the same rules as for the first year, it is enough to
copy below the established formulas for this first year. Content is on the beach
A5 :[Link] leads to :

The error comes from the reference to the rate.

The reference to the rate should therefore not have been relative; it must be an absolute reference.
Invariant by copying.

Mixed references
$B$1 is a mixed reference: relative for the row, absolute for the column.
$B$1 is a mixed reference: absolute for the row, relative for the column

REMINDERS ON FINANCIAL FUNCTIONS

A- SIMPLE INTERESTS:
Interest is the rent of an amount of money lent.
The interest will depend on the importance of the service rendered and the duration during which the service is provided.
rendered.
The interest on an investment is proportional to the capital, the rate, and the time.
L’intérêt I obtenu par un placement d’un capital C pendant une durée n’a un tau t est :
I = c * t * n / 100 (annual)
When the duration of the investment is expressed in months we have:
I = c * t * n / 1200 (monthly)
When the duration of the investment is expressed in days, we have:
I=c*t*n/36000 (for commercial interest) daily
I=c*t*n/36500 (for civil interest) on a daily basis
In the particular case of the savings bank, we will have:
I = c * t * n / 2400 where q is the number of fortnights in the year.

B- COMPOUND INTERESTS
B1Principle:

Guinean Agency for Training and Development - University of Conakry Page 12

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] winter :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

An amount is placed at compound interest for a fixed period of time, the

Interest earned is added to the amount invested, generating new interest. At the end of each period, the
Interest for the next period is calculated on the capital.
Capital + interest from the previous period.

The logic is as follows:

1eraperiod: C + i1= C1
2thperiod: C1+ i2= C2
3thperiod: C2+ i3= C3
…………………………
Nthperiod: Cn+ in = Cn

B2Acquired Value:
Let C be a given initial capital, the interest for one Franc (1F) invested. The value acquired at the end of a
duration expressed in years is:
1erayear: Va = C + i = C + Ci = C(1+i)
2thannée: Va =C (1+i) + [C(1+i)] = C(1+i)[1+i]=C(1+i)2
…………………………….…………….
…………………………………………..
nthyear: Va = C (1+i)n called the Capitalization formula

AMORTIZATION OF MEDIUM-LONG TERM LOANS

1-Repayment by constant annuity of interest and amortization.
To facilitate the borrower's service and spread the financial charges over time.
supported by the borrower, it is often agreed to set a repayment schedule such that
Annuities remain constant. That is to the common value.

THE VALUE OF THE ANNUITY:

At the interest rate i of the loan, there is equality between the present values of the lent capital and the interest annuities.

depreciation

1  1 i n o*i 0*I
a a
0
I 1  1 i  n ;
  1  i 1 n

The amortizations vary in a geometric program with a ratio of (1+i)
The value of the sum of the n constant annuities is expressed by:

a 1 I  n 1
n
i

Ou n = nombre de versement, a = annuité, A = amortissement, i =taux d’intérêt

Exercise 2: A loan of nominal 2,650,000 FG is contracted for a duration of 5 years at the rate
11%. The loan service is provided by a constant annuity.
Draw up the repayment schedule
Guinean Agency for Training and Development - University of Conakry Page 13
Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] winter :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

2- Repayment by constant amortization

Exercise 3: A capital of 3,750,000 is lent for 6 years at an interest rate of 14%. The repayment of the loan
is ensured by constant amortization. Prepare the repayment schedule.

3- Depreciation of fixed assets:

Goods depreciate through use or over time.
Depreciation serves three (3) functions:
Record the value losses to spread the initial expense (when purchasing) over the
Useful life of the asset.
Provide the company with the necessary resources for the renewal of the asset that has become
unusable by including the value losses in operating expenses.
Avoid leaving in the books values that would artificially inflate.
The benefits: The depreciations are a component of the cost price.

SOME CALCULATION METHODS.

Excel brings together functions of various types.
Now let's describe some of the financial functions: borrowing, interest rate, amortization.
Without seeing them all, let's focus on the main ones.

Depreciations
The types of amortizations can be:
Linear depreciation: the amount of depreciation is the same each year
of the duration
Declining balance depreciation: the amount varies according to the year of depreciation.
The initial depreciation being greater than the final depreciation (end of the life of the
good investment).
Declining balance depreciation at double rate: the amount varies depending on the year.
of depreciation. The initial depreciation being greater than the depreciation
final (end of life of an investment asset). It is a variation of diminishing depreciation.

1-L’amortissement linéaire (constant)

The value of the property decreases each year by the same amount called annuity. This fraction
represents part of the initial value of the asset (purchase price increased by costs
accessories), calculated based on the life of the asset.

Exercise 4: A company purchases a machine tool for 4,525,000 FG and incurs costs
transport of 125,000 FG. The asset is depreciated over 5 years (at a rate of 20%)
Draw up the amortization table.

Softy Method
It is an American method that consists of:
Calculate the total years planned for amortization,

Guinean Agency for Training and Development - University of Conakry Page 14

Ing. Alhassane Djamal Touré Email:thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80
 Advanced Excel Practical Work

Divide the value to be amortized by this sum,

Multiply the quotient obtained by the order number of the years, starting with the highest.
raised

FINANCIAL FUNCTIONS
The arguments:
Most financial functions accept similar parameters. The most common ones
used are:
Settings Description
Future value (Cv) Term investment value
Valeur 1, Valeur 2, Valeur n Receipts, payments
Period name (npm) Investment terms
Payment (VPM) Periodic payments
Type When the repayment is due, we have:
0 at the end of the period
1 at the beginning of the period
1-The VPM function: amount period
2-Deposit (economy, savings account)
3-Withdrawing (placement)

She calculates the annuity for a loan.

Syntax :=VPM (rate, npm, va, vc, type) where the parameters vc and type are optional. If they
are omitted, Excel takes the default value.
Exercise 5: A capital of 25 million is borrowed for a duration of 5 years at an interest rate of 14%.
calculate the annuity.
Let's set up the repayment schedule by monthly payment.

2- The PV function (Present Value, initial amount):

It allows you to calculate the present value of a series of constant annuities.
It is equal to 0 for savings.
Positive for an investment
Negative for a loan
Syntax=Va (rate, npm, vc, type) where the parameters vc and type are optional.
If we calculate the present value of a lump sum payment, we can replace the argument Vpm.
for you.

Exercise 6: An investment of 300,000 yields 100,000 per year for 5 years. The rate of
The profitability on the stock market is 10%. Is it interesting to invest 300,000 today to withdraw?
5000000 spread over 5 years?
Solution :
=Va (0, 10, 5,100000)=-3790787f
So it is very interesting to invest 300,000 today to get 500,000 over the years.
Guinean Agency for Training and Development - University of Conakry Page 15
Ing. Alhassane Djamal Touré Email:thassanedjamal24@[Link] winter :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Because in reality, you need to invest 3,790,787 today to get 500,000 instead of 1,000,000.
At the end of each year. Is the investment still interesting?
=Va (0, 10, 5,500000)=-3104607f or 5000 000 =vc
One can always invest, but the first option is better.
NB: If I get a current value result <3000000 it is better to buy the shares.
The type parameter: Type: due date of payments (beginning or end of the month).
The term is whether the repayment is at the beginning or at the end of the period (default value of Excel).
If type=1 the reimbursements due at the beginning of the period.
Note: loans are generally repayable at the end of the month. Some credits.
Mortgages are repayable at the beginning of the month.
Note: RATE, NPM, and VPM must be calculated over the same period (monthly,
quarterly, annual.

4- The NPV function:

It allows you to calculate the net present value of an investment. In general, a
An investment with a net present value > 0 is considered profitable.
Syntax: =NPV(rate; value1; value2; ... value n)
The NPV function differs from PV in one important respect.
V A is based on a constant cash inflow. While NPV allows for irregular payments.
In addition, the included receipts or payments can be a maximum number of 29.
or be integrated into a matrix (there are no limits then)

Exercise 7: An investment of a capital of 250,000,000 is placed at a rate of 12%

it causes a loss of 5,500,000 on the 1sttheyear, then a gain of 9500000, 140000000 and
185000000 at the end of 2emeyear 3emeyear and 4emeyear. Is it profitable?
inversely?
5- The VC function: It calculates the future value of an investment.
It is equal to 0 on a loan, residual value for a leasing.
Syntax: VC (rate; npm; va; type)
The Vpm and type parameters are optional. If we calculate the future value for a payment
flat-rate. One can replace vpm with va.

Exercise 8: You deposit 2,000,000 at the beginning of each year and wait for this
investment that gives you an average return of 11% per year in the long run.
Today you are 30 years old. How much will you have saved by the age of 65?
6- The INTERPEL function:
It calculates interest on an investment for a given period based on payments.
constant periodic payments at a constant interest rate
Syntax :=INTERPEL (rate; period; npm; va; type)

Exercise 9: calculate the interest of the first period of an investment of 25,000,000 at 14%
for a duration of 60 periods.

Guinean Agency for Training and Development - University of Conakry Page 16

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80
 Advanced Excel Practical Work

7- The PRINCPER function:

It calculates the principal payment (borrowed capital portion) based on payments.
constant periodic payments at a constant interest rate
Syntax :=PRINCPER (0,14/12 ; 40 ; 60 ; 250000000)=455950.28

8- The NPM function: (NPM: total number of periods):

It calculates the number of periodic and constant repayments of an investment at a rate
constant interest.
If the reimbursement is monthly, the number of periods corresponds to the number of months.
If the number of quarters for the reimbursement:
If the number of repayment (amortization) is annual, number of years
Syntax: NPM (rate; vpm; va; optional vc; optional type)
If the repayments are insufficient to amortize the loan at the given interest rate.
The function returns an error value (error message) the refund right always
must be at least equal to the number of periods times the principal repayment. Otherwise, the loan
you will never be able to completely absorb.

Exercice10 :Capital=25000000 ; i=0,14 ; Vpm=581706,27

SIMULATION BY A TABLE
When a formula is introduced in a spreadsheet, it is possible to perform a
simulation to observe the influence of modifying a parameter for this. we use the
command Data, Total. This command creates a matrix of values.
Exercise 11: Consider a loan of 920,000 GNF placed at an interest rate of 5% repayable over 120 months.

Determine the monthly payments of this loan

2-Trouver les mensualités pour des taux d 5,5% ,6%, 6,5%,9,5%.
3-Determine the respective monthly payments at these same rates for durations of 96, 120, 180, and
240 months.
THE TARGET VALUE ORDER (Testing different strategies)
All tables are designed to allow the modification of the value of an input cell.
and see the impact of this modification on the result.
If we know the result of a formula but not the values that this formula needs to
to achieve this result, Excel varies the specified value in 'value to reach' until the
The formula dependent on this calculation returns the desired value.
Cell to define: it is the reference or the name of the cell containing the variable we want.
adjust until the target value is reached.
Value to be reached: Value that the formula must reach.
Cell to modify: reference or not of the cell where we want to obtain a specific solution.
Note: the cell to be modified must always contain a value and not a formula.
Excel also allows for the reverse maneuver. (Calculation in reverse)

Guinean Agency for Training and Development - University of Conakry Page 17

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Exercise 12: Suppose you want to see how much you should limit your loan.
sur 30 ans si le taux d’intérêt est 9,5% et que lesmensualités ne doivent pas dépasser 4 000 000
GNF.
Solution12: consider the following parameters:
Prêt=50 000 000GNF taux=9.50% ; année=30 ; paiement= ?
=Vpm (taux ; Année ; Va)=Vpm (9,5 ; 30 ; 50 000 000)=-5084 029 ,22GNF

To find the size of our loan, click on cell B4 where it is located.

payment, open the tools menu, select Target Value.
In the cell text box to define, select cell B4 if it is not already selected. Click in
the value to reach and enter the value to reach (-4,000,000 for our case). Click in the
zone cell to modify then click on B1 (go), click ok.

And we find: 39 33876.95 GNF which will be the amount to borrow.

Example: let the tables below be

The value of cell B4 is the result of the formula=NPV (B3/12, B2, B1)

Target value to determine the interest rate of cell B3 based on the amount of the
cellB4
Use the goal seek function to incrementally adjust the interest rate in cell B3.
until the repayment value of cell B4 is equal to 900 GNF.

Exercise 13: Let's take the example of calculating the interest on a sum that we know the
amount, the rate, and the duration. The problem can be solved by the following formula:
Intérêt=Montant*Taux*Durée

Guinean Agency for Training and Development - University of Conakry Page 18

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link]: 625 23 07 02 / 657 54 90 80
 Advanced Excel practical work

Exercise 14: We want to request a loan that we plan to repay in 15 annual installments. We can
reimburse 29,500 GNF at the end of each period. The current interest rate is 7.75% for c
type of loan. What should be the amount of this loan?
Solution: this problem is solved with the function V A() := V A (rate: duration: repayment)
The function returns +256 405.25 which corresponds to the time of the loan that we can.
to ask.
But we observe that this amount is insufficient to finance the project. We need 300,000 GNF.
Since the bank is not willing to lower the interest rates, there are only two
possibilities! either we extend the loan duration, or we increase the amount of
refunds. To implement the target value function, let's build a sheet of
calculation in which we copy the initial calculation twice in order to be able to answer both
questions.

To determine the amount of annual repayments necessary to obtain a loan of

300 000 GNF.
select the cell containing the result (E13 in the example):
Make tools/target value
The cell to be defined is already correctly specified. The target value is 300,000.
(Don't forget the sign) the cell to modify is in the case .the one that contains the reimbursement
(D13 in this case).
Click on the ok button
After a brief moment, a dialog box appears announcing that the problem has been resolved.
A new click on ok transcribes the results found in the cell sheet.

Guinean Agency for Training and Development - University of Conakry Page 19

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] winter: 625 23 07 02 / 657 54 90 80
 Advanced Excel Practical Work

In the same way, we can see what happens if we extend the duration of the loan.
new enter the value of -300,000 as the target value the cell to be modified would this time be this
cell containing the duration of the loan, that is to say the number of annuities. (C18 in the example)
If you try in the same way to extend the duration to obtain a loan of 385,000 units.
Monetary, the research status dialog box indicates that no solution could be found.
found. This simply comes from the fact that it is impossible to finance a loan in this way
since the annual interest exceeds 29,500 in annual repayment and there would be none left
so not a single cent to start paying back the principal.

Exercise 15: The company Djamal Multiservices sells printers at a unit price of 6500.
The profits of this company have recently fallen due to competition and
price.
The cost price of an HP printer is 210,000 GNF. We are given the relation: sales
mensuelles =500 000 000/ (prix) 2 GNF
The Director of Halal asks you to use Excel and make a decision for
improve its profit outlook.
a) What is the highest monthly selling price (i.e. the profit) based on a simulation?
the highest)?
b) Establish the profit curve.

Exercise 16: The Djamal Multiservices company wants to know what unit selling price will ensure it
the maximum monthly profit knowing that she cannot sell more than 275 printers per month
has a profit margin of 50%

THE SOLVER
The solver allows solving problems that are more or less complex by obeying certain rules.
constraints defined by the user.

Exercise 17: Solve the system of equations below:

2x 4 y  3z 15
3x y 2z 20
4x 3y z 12
Using the solver method, find the values of x, y, and z.
Exercise 18: We want to make cylindrical cans whose prescribed volume is
example 1500 ml, by maximizing the saving of raw materials in order to limit as much as possible
possible production courses.
The surface area of material required is calculated using the formula:

Exercise 19: The company Djamal multiservice has seven mutually exclusive projects.
A, B, C, D, E, F, and G. The net revenues and required investments are given in the table below.

Guinean Agency for Training and Development - University of Conakry Page 20

Eng. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] : 625 23 07 02 / 657 54 90 80
 Advanced Excel Practical Work

The company has an investment budget of 20,000 GNF.

Using the present value method. Determine the most profitable project if the rate of
profitability is 9%

Guinean Agency for Training and Development - University of Conakry Page 21

Ing. Alhassane Djamal Touré E-mail :thassanedjamal24@[Link] tél :625 23 07 02 / 657 54 90 80