computer

PRACTICAL EXERCISE EXCEL Module 1

Exercise 1

Projected College Cash Flow Analysis Worksheet

Problem: Attending College is an expensive proposition and your resources are limited.

To plan for your four-year college career, you have decided to organize your anticipated

resources and expenses in a worksheet. The data required to prepare your worksheet is shown in

Table.

1. Using the numbers in above Table 1–9, create the worksheet shown in columns A

through F in next Figure.

2. Format the worksheet title as Calibri 24-point bold purple.

3. Merge and center the worksheet title in cell A1 across columns A through F.

4. Format the worksheet subtitles in cells A2 and A11 as Calibri 16-point bold red.

5. Format the ranges A3:F3 and A12:F12 with the Heading 2 cell style and center the text in the

cells.

6. Format the ranges A4:F9 and A13:F19 with the 20% - Accent 2 cell style,

7. Format the ranges A10:F10 and A20:F20 with the Total cell style.

8. Change the name of the sheet tab to College Cash Flow Analysis.

9. Save the workbook using the file name, Case2 College Resources and Expenses.

10. Select the range (A2:A9), press and hold control key and select the range (C2:C9). Release control

key.

11. Insert an Exploded pie in 3-D chart to show the contribution of each category of resources for the

sophomore year.

12. Select the range (A12:A19), press and hold control key and select the range (C12:C19). Release

control key.

13. Add the Pie chart title as shown in Figure.

14. Save the workbook as EX-PE1-Ex1-YourName. Change it to landscape orientation.

15. Submit the assignment to as indicated for your instructor.

Exercise 2

Enter the data given below into a worksheet.

A B C D E

1 Stationery Supplies Ltd

2

3 Date Sales Person Item Receipt No Amount

4 21-Nov Carl Toys 1238 1,782.10

5 26-Nov Carl Stationery 1255 4,853.55

6 26-Nov Carl Toys 1395 51.35

7 Carl’s Total

8 21-Nov John Cards 1141 91.15

9 24-Nov John Books 1982 442.60

10 21-Nov John Toys 1885 561.50

11 26-Nov John Toys 1875 62.75

12 John’s Total

13 22-Nov Judy Books 1032 234.50

14 26-Nov Judy Sports goods 1920 472.60

15 Judy’s Total

16 25-Nov Mary Toys 1774 364.15

17 Mary’s Total

18 22-Nov Susan Electronics 1160 52.95

19 23-Nov Susan Cards 1075 81.60

20 23-Nov Susan Others 1745 132.95

21 24-Nov Susan Sports goods 1662 2,580.10

22 Susan’s Total

23

24 Grand Total

(i). Calculate the totals for each salesperson and get the grand total.: (ii). Format the worksheet as follows:

Make all the Totals bold, two decimal places, comma, center the title across columns A-E and make it size 16, bold and Italic.

(iii). Put a double border round the whole table and a single line border inside the table. (iv). Add a Pie 3D Chart with the Total Sales of each Sales Person (v). Save the worksheet as EX-PE1-Ex2-YourName