Create+and+Use+Spreadsheets

= =

Create and use spreadsheets

Using Microsoft Excel 2007.
[|Microsoft Excel 2007 Keyboard Shortcuts]

[|Make the switch to Excel 2010. Tutorial]

[|Welcome to the Excel 2010 tutorials info page. A page that covered everything related to Microsoft Excel 2010 from the basic features to the advanced tips and tricks.]

[|Welcome to Free Tutorial Central]

**Basic Spreadsheet Concepts.**
Download exercises.

Charting with Excel.
Necessary files below. You could also refer to the charting tutorial above. Follow this link.

Files needed for the charting files

Additional Tasks:
These tasks do not form part of your portfolio. However, they will be reviewed in class.

These are extension exercises for those who have completed the required tasks.

These tasks provide an opportunity for you to demonstrate your ability to use MS Excel independently. You may have to refer to online "Help" for assistance.

Learning how to use "Help" is an important part of any software application.

Task 1:
This task, called "DailyTakings.xls", sounds on the surface relatively easy. However, you will be required to give considerable thought to design, presentation and formulas.

The Scenario:

 * At the beginning of each day a shopkeeper commences his working day with a '[|cash float]'. The cash float is shown in figure 1.
 * At the end of each day the shopkeeper enters the number of each denomination into the sheet called 'Daily Takings'. For example, he may have 12 $50 notes, and 15 $20 notes. This sheet is shown in Figure 2.

Figure 1: Float


Figure 1 shows a worksheet called **Float.** It describes the shopkeepers cash float at the beginning of each working day.

The shopkeeper commences every day with the same cash float.

You are required to reproduce this sheet, name the sheet **'Float'** and insert appropriate formulas in the cell range C1:C12

Figure 2: Daily Takings
As mentioned above, at the end of each day the shopkeeper enters into the sheet "Daily Takings" the number of each denomination (coins & notes) in his cash register at closing time.

The shopkeeper does not accept EFTPOS, cheques or lay-by.

He deals only in cash.

__The theory behind this worksheet:__
The shopkeeper does not wish to add the value of each denomination to arrive at the days takings.

He wishes to keep it simple. That is, enter how many $50 notes, how many $20 notes, how many 5c coins and so on.

The Excel worksheet is supposed to do the hard work.

Column B is to enter the number of each denomination only.

Column C contains formulas. For example, should the shopkeeper enter 2 x $100 notes in column B; column C should return $200. There are formulas in any other columns.

To determine the daily takings the shopkeeper will need to deduct the cash float.

Cell C13 in this example refers to the sheet 'Float'. That is you must make cell c13 //**point to**// the appropriate cell address in 'Float'.

The table above shows the cash register contents at the end of seven consecutive days.
 * < Denomination ||< Monday ||< Tuesday ||< Wednesday ||< Thursday ||< Friday ||< Saturday ||< Sunday ||
 * < $100 ||< 15 ||< 12 ||< 3 ||< 21 ||< 11 ||< 9 ||< 0 ||
 * < $50 ||< 29 ||< 23 ||< 13 ||< 12 ||< 14 ||< 14 ||< 15 ||
 * < $20 ||< 17 ||< 15 ||< 15 ||< 14 ||< 11 ||< 9 ||< 13 ||
 * < $10 ||< 37 ||< 15 ||< 23 ||< 15 ||< 21 ||< 13 ||< 41 ||
 * < $5 ||< 28 ||< 32 ||< 35 ||< 24 ||< 32 ||< 45 ||< 12 ||
 * < $2 ||< 23 ||< 43 ||< 53 ||< 12 ||< 26 ||< 46 ||< 34 ||
 * < $1 ||< 58 ||< 15 ||< 23 ||< 13 ||< 11 ||< 34 ||< 37 ||
 * < $0.50 ||< 34 ||< 23 ||< 35 ||< 51 ||< 12 ||< 34 ||< 9 ||
 * < $0.20 ||< 18 ||< 64 ||< 42 ||< 45 ||< 31 ||< 23 ||< 18 ||
 * < $0.10 ||< 36 ||< 26 ||< 24 ||< 34 ||< 21 ||< 16 ||< 18 ||
 * < $0.05 ||< 37 ||< 16 ||< 35 ||< 14 ||< 35 ||< 34 ||< 12 ||

Enter the data into your workbook for each day.

You should end up with 7 workbooks called "Monday_YourName", "Tuesday_YourName", and so on.

The workbooks should reveal the Daily Takings for each of the 7 days. That is, contents of cash register minus float for each of 7 days.

If you really want to impress create an Excel [|template]. Your research on how to do.