Quarter Number Calculation Worksheet

Define a Name for columns A:E in worksheet 14 — Months Lists. The Name to define is MonthTable (to define the Name, select columns A:E, press <Ctrl+F3>, type MonthTable in the Name in workbook text box, and then press OK).

Paste MonthTable into the first argument of the INDEX formula, as shown in Figure 12-3.

NOTE:

An explanation of the formulas entered into columns C:E is provided on page 210.

File Edit View Insert Format Tools Data Window Help

A I B

*

P

X

Quaitei

Quarter

Quarter $ -

Combo[Jex, TB Table

Number

Number Fiscal

Calendar or

1

Month Number

Month List

Calendar year

year

Fiscal Year

m

1 |January-31,1998

1

1

1

3-

2

February 28,1990:

1

1

1

4

3

iwsssrff

1

1

1

5

4

April 30. i 998'

2

2

2

6

rS

May 3t, 1998

2

2

2

7

6

June-30,1998

2

2

■2

S

7

July 31 1993

3

3

3

3

S &usf31 1'9®8

3

3

'3

10

9

¡September 30,1998

3

3

3

11

00

Ifflfessf 31,1398

4

4

4

Figure 12-4: Worksheet 14 — Months List

Figure 12-4: Worksheet 14 — Months List

Cells from B2 onwards contain the month list range. The Name defined for the range is MonthsList, which is the list entered in the Combo Box input box (as described on page 6, Chapter 1, Introducing Financial Statements.xls Worksheets.

When selecting the month-ending period, September 2003 (in the example here), from the Combo Box MonthsList, the linked cell MonthSelectionNumber receives the number 69 (this is the month number, starting from January 1998).

In the INDEX formula's second argument, the row number of the selected months is MonthSelectionNumber+1 (the sequence numbers in column A start from cell A2).

In the INDEX formula's third argument, the column number is column 5 (column E in the worksheet, as shown in Figure 12-4).

Figure 12-5: Index Formula That Returns the Quarter Number

The Formulas in Columns C:E of Worksheet 14 -Months List

Columns C:E in Figure 12-4 contain formulas that calculate the quarter numbers based on either a calendar or fiscal year reporting method:

4 Cell C2 contains a formula that calculates the calendar quarter number:

4 Cell D2 contains a formula that calculates the fiscal quarter number, based on the month the fiscal year ended, as explained in the next section.

=MOD(CEILING(22+MONTH(B2)-FiscalYearMonthNumber-1,3)/3,4)+1

Chapter 12

♦ Cell E2 contains an IF formula that returns calculation results from cell C2 or D2 depending on the reporting year end period (either calendar or fiscal), as explained in the next section.

=IF(CalendarYear=True,C2,D2)

0 0

Post a comment