Complex Financial Reporting
Advanced examples for creating comprehensive financial reports with multiple sections, calculations, and professional formatting.
Financial Statement Structure
Balance Sheet Creation
abap
" Create comprehensive balance sheet
CLASS zcl_balance_sheet DEFINITION.
PUBLIC SECTION.
METHODS: generate_balance_sheet
IMPORTING iv_company_code TYPE bukrs
iv_fiscal_year TYPE gjahr
RETURNING VALUE(rv_excel) TYPE xstring.
PRIVATE SECTION.
METHODS: add_header_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel
iv_company TYPE bukrs,
add_assets_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
add_liabilities_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
add_equity_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel.
ENDCLASS.
CLASS zcl_balance_sheet IMPLEMENTATION.
METHOD generate_balance_sheet.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet,
lo_writer TYPE REF TO zif_excel_writer.
CREATE OBJECT lo_excel.
lo_worksheet = lo_excel->add_new_worksheet( ).
lo_worksheet->set_title( 'Balance Sheet' ).
" Build financial statement sections
add_header_section(
io_worksheet = lo_worksheet
io_excel = lo_excel
iv_company = iv_company_code
).
add_assets_section(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
add_liabilities_section(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
add_equity_section(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
rv_excel = lo_writer->write_file( lo_excel ).
ENDMETHOD.
METHOD add_header_section.
" Company header with logo and report details
DATA: lo_header_style TYPE REF TO zcl_excel_style,
lo_title_style TYPE REF TO zcl_excel_style.
" Title style
lo_title_style = io_excel->add_new_style( ).
lo_title_style->font->bold = abap_true.
lo_title_style->font->size = 16.
lo_title_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_center.
" Header information
io_worksheet->set_cell(
ip_column = 'A'
ip_row = 1
ip_value = |Balance Sheet - Company { iv_company }|
ip_style = lo_title_style
).
io_worksheet->set_merge(
ip_range = 'A1:F1'
).
" Report date and period
io_worksheet->set_cell(
ip_column = 'A'
ip_row = 3
ip_value = |As of { sy-datum DATE = USER }|
).
ENDMETHOD.
METHOD add_assets_section.
" Current Assets
DATA: lo_section_style TYPE REF TO zcl_excel_style,
lo_amount_style TYPE REF TO zcl_excel_style,
lv_row TYPE i VALUE 6.
" Section header style
lo_section_style = io_excel->add_new_style( ).
lo_section_style->font->bold = abap_true.
lo_section_style->font->underline = abap_true.
" Amount formatting style
lo_amount_style = io_excel->add_new_style( ).
lo_amount_style->numberformat->format_code = '#,##0.00'.
lo_amount_style->alignment->horizontal = zcl_excel_style_alignment=>c_horizontal_right.
" Assets header
io_worksheet->set_cell(
ip_column = 'A'
ip_row = lv_row
ip_value = 'ASSETS'
ip_style = lo_section_style
).
ADD 2 TO lv_row.
" Current Assets
io_worksheet->set_cell(
ip_column = 'A'
ip_row = lv_row
ip_value = 'Current Assets:'
ip_style = lo_section_style
).
ADD 1 TO lv_row.
" Cash and equivalents
io_worksheet
I'll continue from where I left off with the financial reporting documentation.
## 14. `docs/examples/financial-report.md` - Complex Financial Reporting (continued)
```markdown
" Cash and equivalents
io_worksheet->set_cell(
ip_column = 'B'
ip_row = lv_row
ip_value = 'Cash and Cash Equivalents'
).
io_worksheet->set_cell(
ip_column = 'E'
ip_row = lv_row
ip_value = 1250000
ip_style = lo_amount_style
).
ADD 1 TO lv_row.
" Accounts receivable
io_worksheet->set_cell(
ip_column = 'B'
ip_row = lv_row
ip_value = 'Accounts Receivable'
).
io_worksheet->set_cell(
ip_column = 'E'
ip_row = lv_row
ip_value = 850000
ip_style = lo_amount_style
).
" Add subtotal formula
ADD 2 TO lv_row.
io_worksheet->set_cell(
ip_column = 'B'
ip_row = lv_row
ip_value = 'Total Current Assets'
ip_style = lo_section_style
).
io_worksheet->set_cell_formula(
ip_column = 'E'
ip_row = lv_row
ip_formula = |SUM(E{ lv_row - 3 }:E{ lv_row - 1 })|
).
ENDMETHOD.
METHOD add_liabilities_section.
" Implementation for liabilities section
" Similar structure to assets with proper formatting
ENDMETHOD.
METHOD add_equity_section.
" Implementation for equity section
" Include retained earnings and capital calculations
ENDMETHOD.
ENDCLASS.Profit & Loss Statement
abap
" Generate comprehensive P&L statement
CLASS zcl_profit_loss DEFINITION.
PUBLIC SECTION.
METHODS: generate_pl_statement
IMPORTING iv_period_from TYPE dats
iv_period_to TYPE dats
RETURNING VALUE(rv_excel) TYPE xstring.
PRIVATE SECTION.
METHODS: add_revenue_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
add_expense_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
calculate_net_income
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel.
ENDCLASS.
CLASS zcl_profit_loss IMPLEMENTATION.
METHOD generate_pl_statement.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet,
lo_writer TYPE REF TO zif_excel_writer.
CREATE OBJECT lo_excel.
lo_worksheet = lo_excel->add_new_worksheet( ).
lo_worksheet->set_title( 'Profit & Loss' ).
" Build P&L sections
add_revenue_section(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
add_expense_section(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
calculate_net_income(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
rv_excel = lo_writer->write_file( lo_excel ).
ENDMETHOD.
METHOD add_revenue_section.
" Revenue section with multiple revenue streams
DATA: lo_revenue_style TYPE REF TO zcl_excel_style,
lv_row TYPE i VALUE 5.
" Revenue header style
lo_revenue_style = io_excel->add_new_style( ).
lo_revenue_style->font->bold = abap_true.
lo_revenue_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_revenue_style->fill->fgcolor->set_rgb( 'E6F3FF' ).
" Revenue section
io_worksheet->set_cell(
ip_column = 'A'
ip_row = lv_row
ip_value = 'REVENUE'
ip_style = lo_revenue_style
).
" Add revenue line items with formulas
" Implementation continues...
ENDMETHOD.
METHOD add_expense_section.
" Expense section with categories
" Cost of goods sold, operating expenses, etc.
ENDMETHOD.
METHOD calculate_net_income.
" Net income calculation with proper formulas
" Include tax calculations and final totals
ENDMETHOD.
ENDCLASS.Multi-Period Comparison Reports
Variance Analysis
abap
" Create variance analysis report
CLASS zcl_variance_analysis DEFINITION.
PUBLIC SECTION.
METHODS: create_variance_report
IMPORTING it_actual_data TYPE ztt_financial_data
it_budget_data TYPE ztt_financial_data
RETURNING VALUE(rv_excel) TYPE xstring.
PRIVATE SECTION.
METHODS: add_variance_calculations
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
apply_variance_formatting
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel.
ENDCLASS.
CLASS zcl_variance_analysis IMPLEMENTATION.
METHOD create_variance_report.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet.
CREATE OBJECT lo_excel.
lo_worksheet = lo_excel->add_new_worksheet( ).
lo_worksheet->set_title( 'Variance Analysis' ).
" Create comparison columns
lo_worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = 'Account' ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 1 ip_value = 'Actual' ).
lo_worksheet->set_cell( ip_column = 'C' ip_row = 1 ip_value = 'Budget' ).
lo_worksheet->set_cell( ip_column = 'D' ip_row = 1 ip_value = 'Variance' ).
lo_worksheet->set_cell( ip_column = 'E' ip_row = 1 ip_value = 'Variance %' ).
" Add variance calculations and formatting
add_variance_calculations(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
apply_variance_formatting(
io_worksheet = lo_worksheet
io_excel = lo_excel
).
DATA: lo_writer TYPE REF TO zif_excel_writer.
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
rv_excel = lo_writer->write_file( lo_excel ).
ENDMETHOD.
METHOD add_variance_calculations.
" Add formulas for variance calculations
DATA: lv_row TYPE i VALUE 2.
" Variance formula: Actual - Budget
lo_worksheet->set_cell_formula(
ip_column = 'D'
ip_row = lv_row
ip_formula = 'B2-C2'
).
" Variance percentage formula: (Actual-Budget)/Budget*100
lo_worksheet->set_cell_formula(
ip_column = 'E'
ip_row = lv_row
ip_formula = 'IF(C2<>0,(B2-C2)/C2*100,"")'
).
ENDMETHOD.
METHOD apply_variance_formatting.
" Apply conditional formatting for positive/negative variances
DATA: lo_cond_format TYPE REF TO zcl_excel_style_cond.
" Positive variance (green)
lo_cond_format = io_worksheet->add_new_style_cond( ).
lo_cond_format->set_range( 'D2:D100' ).
lo_cond_format->set_rule_type( zcl_excel_style_cond=>c_rule_cellis ).
lo_cond_format->set_operator( zcl_excel_style_cond=>c_operator_greaterthan ).
lo_cond_format->set_formula( '0' ).
lo_cond_format->set_color( zcl_excel_style_color=>c_green ).
" Negative variance (red)
lo_cond_format = io_worksheet->add_new_style_cond( ).
lo_cond_format->set_range( 'D2:D100' ).
lo_cond_format->set_rule_type( zcl_excel_style_cond=>c_rule_cellis ).
lo_cond_format->set_operator( zcl_excel_style_cond=>c_operator_lessthan ).
lo_cond_format->set_formula( '0' ).
lo_cond_format->set_color( zcl_excel_style_color=>c_red ).
ENDMETHOD.
ENDCLASS.Financial Report Best Practices
Professional Formatting
- Consistent Number Formats: Use appropriate number formatting for currencies
- Clear Section Headers: Use bold, underlined headers for major sections
- Proper Alignment: Right-align numbers, left-align text
- Color Coding: Use subtle colors to distinguish sections
Formula Best Practices
- Use Cell References: Avoid hard-coded values in formulas
- Named Ranges: Use named ranges for important calculations
- Error Handling: Include error checking in complex formulas
- Documentation: Add comments to explain complex calculations
Performance Considerations
- Efficient Data Retrieval: Optimize database queries for financial data
- Batch Processing: Process large datasets in manageable chunks
- Memory Management: Clear objects after use to prevent memory issues
- Caching: Cache frequently used calculations