The problem scenario

Throughout much of this book, we will be referring to an Excel workbook called SALESMAN.XLS. This contains a number of worksheets including that displayed in Figure 2.1. The purpose of this worksheet is to maintain weekly sales data for each representative employed by a sales company. The top section contains administrative data such as the date the worksheet was written, and so on. The lower section contains data showing a column of representative names, along with the total sales to date, then the grey shaded region that contains weekly sales data for each of four weeks (per month), the next column contains the monthly total for these weekly sales, the next column shows the monthly bonus earned by the corresponding representative, and finally, an end of month sales column. This is defined as the sales to date plus the end of month sales.

The following named ranges have been set in the weeklysales worksheet.

end_month_sales = I32:I40 month_bonus = H32:H40 month_total = G32:G40 week_sales = C32:F40 sales_to_date = B32:B40 rep_name = A32:A40 month_no = B29 bonus_rate = B28

Throughout this book, we will use many examples that are based upon the SALESMAN workbook. The file is available for downloading from the Made Simple website at: http//:www.madesimple.co.uk. Alternatively, you can create this file yourself, but make sure that it is identical to that shown in Figure 2.1 and includes the same named ranges.

Reasons for using macros

♦ Executing repetitive tasks Many Excel business tasks require frequent repetition of some keyboard or mouse operations. For example, updating a company car expenses monthly spreadsheet might involve copying a range of cells to another, followed by clearing a range ready for next month's entry, followed by updating the total sales to date. A macro can be created to represent a task like this and then invoked whenever required; perhaps saving a great deal of time to the user who would otherwise have to retype the whole task over and over again whenever it is to be used.

E3 Microsoft Extel - SA!_tSMAP<

Format lools Data Window Help I Times New Roman ▼ 10 - 0 I

^JflJiS]

Type a questioji For help ».fix morith n&

month_no range currently selected

Wo rkb o o k Name : Workheet Name : ¡ystemPinpose:

Salesman Workbook Data _

System Information _

SALESMAN

To maintain weekly sales data for each representative, ¡and to calculate sales to date, the monthly bonus for each salesperson^: month total for each representative. _J |_| |_

__[Formulae Descriptions: _|_I

End of Month Sales: (Each cell value in this is calculated by adding the Sales To Date to the Monthly Total

Monthly Bonus: Each c ell value in this is calculated by multiplying M onthly T otal by the B on

Sales lo Date: |Each cell in this range is carried over from the End of Month Sales column foi End month sales

Month No: This cell value is incremented at the start of each new month month_no range currently selected

__[Formulae Descriptions: _|_I

End of Month Sales: (Each cell value in this is calculated by adding the Sales To Date to the Monthly Total

Monthly Bonus: Each c ell value in this is calculated by multiplying M onthly T otal by the B on

Sales lo Date: |Each cell in this range is carried over from the End of Month Sales column foi End month sales

Month No: This cell value is incremented at the start of each new month

Figure 2.1 The Scenario worksheet, showing the named ranges

♦ Input validation Data may require validation. For example, when a number that represents a person's age is entered, anything outside the range 0 to 120 would probably be unacceptable. A macro can be used to ensure that data entered into a range of cells is restricted within this specified range of values.

♦ User interface control Many novice Excel users can feel overwhelmed by the complexity of using spreadsheets, even when just entering data. A macro can provide a friendlier interface that may include helpful dialog boxes. For example, a data entry clerk may work more efficiently by using a customised interface from a macro instead of entering data directly into the sheet.

♦ Decision-making A spreadsheet is a matrix of cell locations that can contain values, formulae and relationships. The key point to note is that all of the elements in the matrix are changed automatically when one or more of the assumptions are changed. This facility allows a series of outcomes to be explored, providing answers to 'what-if' questions that are an essential part of decision making. Macros can be created for this purpose.

0 0

Post a comment