Excel-Exercise-Free

 

1. In order to start the calculations tutorial please refer to the fruits and vegetables exercise (menu Presentation)

2. The shopkeeper estimates that March's income is the same as for February's. D2 cell will therefore be displayed as :
Formule de la cellule =C2

 

3. Important If February's estimation exceeds 1100 euros, March's value will automatically display the same.

 

4. Therefore, if the shopkeeper wants to change January's figures, it will not have an effect upon the other month's values because they are not linked to January.

 

5. Although the result might seem the same, this process is in no way related to a copy / paste procedure.

 

6. In April, as the first summer fruits are ordered, the shopkeeper estimates an income of 1800 euros

7. Now, Let's make or first complex formula (Yeah !). In May and June the income increases 50 in relation to April. Cell F2 is therefore displayed as
Formule de la cellule =E2+E2*0.5

8. Or The parentheses are necessary for the mathematical priority of calculation. For example, the multiplication is done before the addition.
Formule de la cellule =E2*(1+0.5)

 

9. For cell G2, which corresponds to June's income, do not copy F2's formula. This would result in an incorrect calculation because the reference of the formula of the cell G2 will be F2 and not E2

 

10. But, in this exercise, the income of May and June are 50% higher than April. So the function of G2 must pointed to E2 (corresponding to April)

11. JThere are two possible displays for cell G2
Formule de la cellule =E2*(1+0,5)

12. or And there, we have the previous result 2700 and not 4050
Formule de la cellule =F2

 

13. July's estimated income is 3300 euros.

14. The estimation for August is a 10 % increase in relation to July.
Formule de la cellule =H2*(1+0.1)

15. There will be a drastic decrease in the income in relation to the two previous month' 30 % decrease in relation to July. September's income is therefore displayed as :
Formule de la cellule =H2-H2*0.3

16. Or
Formule de la cellule =H2*(1-0.3)

17. In October, the income is equal to September's minus 25 %
Formule de la cellule =J2*(1-0.25)

 

18. In November the income is estimated at 1200 euros.

19. In December there is 15 % increase estimation in relation to November.
Formule de la cellule =L2*(1+0.15)

 

20. Here, we have finished the monthly income estimation, now we calculate the variable expenses

21. In March the variable expenses are the same as February's
Formule de la cellule =C4

22. In April a temporary worker is hired. His salary is estimated at 1000 euros. The variable expenses are to be included. In the same formula, we have a data fixed (1000) and a date variable (D4)
Formule de la cellule =D4+1000

23. In May the heating expenses are omitted (200 euros), but the stocks have increased. The stock increase is equal to 20 % of the variable expenses of the previous month.
Formule de la cellule =E4+E4*0.2-200

24. The formula may also be written as
Formule de la cellule =E4*(1+0.2)-200

25. In June, July, and August the stocks have regulary increased of 15 per month. The formula corresponding to the month of June is thus written
Formule de la cellule =F4*(1+0.15)

26. This formula is also applicable for July and August. By recopying it the reference cell always corresponds to the previous months cell that is what we want to obtain.
Formule de la cellule =G4*(1+0.15)

27. And for August
Formule de la cellule =H4*(1+0.15)

28. September's variable charges should be equal to those of May
Formule de la cellule =F4

29. In October, the seasonal worker has gone and the heater has been reprogrammed for May 15th (so 200 / 2 = a half month). The stock decrease is 10 % of last month's total charges.
Formule de la cellule =J4*(1-0.1)-1000+(200/2)

30. In November, all the expenses, except the heater, are equal to the previous months. The entire month will be heated. We will, thus add a half month.
Formule de la cellule =K4+(200/2)

31. In December the stocks' decrease represents a total of 10 % in relation to the previous months.
Formule de la cellule =L4*(1-0.1)

 

32. Fill out this document throught this exercise