Preparing Various Statements with Excel VBA

Apartment Sales Statement

Suppose we need to prepare an apartment sales statement according to the template. Perform the following steps:

• Prepare the necessary apartment data in the form of a list.

• In cell G2, enter the formula:

• For the range G3:G5, either use the AutoFill handle or copy the formula.
• If necessary, format the resulting list of announcements using the options in Format as Table, located in the Styles group on the Home tab of the ribbon.

Statement on Revaluation of Fixed Assets

Next, let’s look at an example of preparing a statement for revaluation of fixed assets according to the template in Figure.

• In cell A1, enter the title: Statement on the Revaluation of Fixed Assets.
• In cells A4:F4, enter the field names: Object Name, Book Value (BV), Depreciation (Dep), Net Book Value (NBV), Replacement Full Value (RFV), Replacement Net Value (RNV).
  • The Object Name field includes the following rows: Management & Marketing Dept., Transport Dept., Assembly Shop, Finishing Shop, Warehouse 1, Warehouse 2, Warehouse 3, Total.
• Formulas for calculations:
  • NBV = BV – Dep
  • RFV = BV * K
  • RNV = NBV * K

where K is a coefficient:

• • 3.3 if BV ≤ 650 million rubles
  • 4.2 if BV > 650 million but < 1000 million rubles
  • 5.1 if BV ≥ 1000 million rubles
• Use the following formulas for automatic calculations:

In D5:

=B5-C5

In E5:

=B5*IF(B5<=600,3.3,IF(AND(B5>600,B5<1000),4.2,5.1))

In F5:

=D5*IF(B5<=600,3.3,IF(AND(B5>600,B5<1000),4.2,5.1))

• For the total row (Итого), for example in cell B12, enter:

=SUM(B5:B11)

Or select cells B12:F12 and use AutoSum (Formulas tab → Function Library → AutoSum).

Note: The dropdown arrow of the AutoSum button allows automatic calculations with other functions (Average, Max, Min, etc.).

• Format the resulting table and the title of the statement.

Reporting Statement for a Computer Club Network

In the next example, we prepare a reporting statement for the operation of a computer club network.

• In cell A1, enter the title: Statement of Computer Club Network Operations.
• In cells A3:H3, enter the field names: Club, January, February, March, Total Revenue, Rank, Average Revenue, Percentage.
  • The Club field includes: Altair, Gruvit, Polygon, Galax, Zvezda, Hexen, Antei, Arsenal, Arena, Bunker, Total.
• The main formulas for calculations (to be copied for other rows) are given.
• Format the resulting statement.

Table. Formulas for Calculation

Payroll Statement

Now let’s prepare the payroll statement for the employees of the Scientific and Project Department “Alpha” .

• In cell A2, enter the title of the statement: Payroll Statement of the Employees of the Scientific and Project Department “Alpha”. Align it to the right (for example, using the Align Right button in the Alignment group on the Home tab of the ribbon).

• In cells A3:K3, enter the field names of the statement:
  • № (serial number),
  • Last name & initials,
  • Position,
  • Salary rate,
  • Years of service,
  • k,
  • Service allowance,
  • Total,
  • Tax rate,
  • To withhold,
  • Net pay.
• Add hidden comments to the header (see Figure 3.27):
  • № — employee number;
  • Last name & initials — all employees of the department;
  • Position — job title at the time of filling in the statement;
  • Salary rate — monetary equivalent of the job position;
  • Years of service — integer number of years worked;
  • k — coefficient for years of service;
  • Service allowance — monetary equivalent for years of service;
  • Total — gross salary including base rate and service allowance;
  • Tax rate — percentage of budget deductions;
  • To withhold — monetary equivalent of deductions;
  • Net pay — amount to be issued.

To create comments: go to the Review tab → Comments group → New Comment (other tools in this group allow managing comments in the workbook).

1. Calculations: k, Service allowance, Total, Tax rate, To withhold, Net pay are calculated with formulas using AutoFill or copy.
   • Coefficient k is assigned as follows:
     • 0.1 — up to 5 years (inclusive),
     • 0.2 — from 5 to 10 years (inclusive),
     • 0.25 — from 10 to 15 years (inclusive),
     • 0.3 — more than 15 years.

Formula for cell F4:

=IF(E4<=5,0.1,IF(AND(E4>5,E4<=10),0.2,IF(AND(E4>10,E4<=15),0.25,0.3)))

Formula for cell G4 (service allowance):

=D4*F4

Custom number format for G4:

# ##0.00р.

Formula for cell H4 (Total):

=D4+G4

Custom number format for H4:

# ##0.00р.

Formula for cell I4 (Tax rate):

=IF(H4<=7000,0.02,IF(AND(H4>7000,H4<=10000),0.1,IF(AND(H4>10000,H4<=25000),0.2,0.35)))

Format of I4: Percentage.

Formula for cell J4 (To withhold):

=H4*I4

Custom number format for J4:

# ##0.00р.

Net pay (column K): Total minus To withhold.

1. Requirements for the Years of Service column (E):
   Create a custom conditional formatting style:
   • up to 5 years → yellow,
   • 5–10 years → blue,
   • 10–15 years → green,
   • more than 15 years → red.

Use Conditional Formatting (Home tab → Styles group).

If a negative number of years is entered, a corresponding error message should appear (Figure 3.29). This is set up through Data Validation → Error Alert tab.

1. For the Salary rate field, display a constant message:
   “Salary rate. BE CAREFUL WHEN ENTERING THE SALARY RATE” .

To do this, use Data Validation → Input Message tab.

If negative salary values are entered, a warning appears:

This is done using a custom number format:

# ##0.00р.;[Red] »Salary rate cannot be negative! »