8. Exercise - Working with spreadsheets

Prerequisites

It is advisable to read the Excel 2016 Course up to the 8th chapter Work with spreadsheets before starting this exercise.

Question

Let's take the example from Exercise 1 - Calculation, Formulas and Series. We assume that the equipment and training project involves a number of subsidiaries of a parent company and that a price schedule for each subsidiary and an overall price schedule must be drawn up:

We assume the existence of 4 subsidiaries with quantities as follows:

Excel 2016 Example Working with spreadsheets

We assume the existence of 4 subsidiaries with quantities as follows:

No. Designation Subsidiary 1 Subsidiary 2 Subsidiary 3 Subsidiary 4
1 Server - Xeon 8-Core Processor 2 2 2 2
2 Laptop PC, Intel Core i5-7200U processor 50 40 60 32
3 PC de bureau processeur Intel® Core™ i5-7500 250 200 300 160
4 Line interactive UPS with AVR 250 200 300 160
5 Scanners, A4, 4,800 dpi x 4,800 dpi 60 45 72 40
6 Video projector - Display technology: DLP 10 8 12 8
7 Monochrome laser printer 250 200 300 150
8 Windows 10 Prefessional License 300 240 360 200
9 Microsoft Office 2016 License 300 240 360 200
10 Design software License 50 40 60 32
11 Management software License 40 32 50 24
12 Microsoft Office 2016 Training 800 720 880 640
13 Design software Training 100 80 120 72
14 Management software Training 80 72 92 64

Indications Exercise – Working with spreadsheets

Fill in a sheet as shown in the extract above with the unit prices and with all the formulas in column F (See formulas to be entered in column F in Exercise 1 - Calculation, Formulas and Series.

Then make 4 copies of this sheet. You will have 5 copies which you will name Subsidiary1, Subsidiary2, Subsidiary3, Subsidiary4 and Global.

For the 4 sheets Subsidiary1, Subsidiary2, Subsidiary3 and Subsidiary4, fill in the columns D with the quantities according to the table above.

For the 5th sheet, which you have named Global, enter in cell D5 the following formula

"=Subsidiary1!D5+Subsidiary2!D5+Subsidiary3!D5+Subsidiary4!D5"

Copy this formula to the other cells in column D.

<< 7. Exercise - Finding duplicates

9. Exercise - Text Functions >>