Question

The course project involves the completion of a comprehensive consolidation of financial statements in

EXCEL. Please note that most of the points are associated with the sophistication of the EXCEL program. The

goal here is to design an EXCEL application that will complete the consolidation in future years and will

minimize the chances that a future user of the EXCEL application will make an error or will struggle with the

application. The fewer required inputs ongoing, the better. The fewer required decisions by future users the

better. Essentially, attempt to design an EXCEL spreadsheet that automatically completes the consolidation

process once the current year financial statement information is input. As a hint, cases earning all the points

usually involve the use of HLOOKUPS, VLOOKUPS, or series of embedded IF statements.

Answer 1

We can use VLOOKUP to create Excel application for the consolidation of financial statements.

VLOOKUP means Vertical Lookup. It is an inbuilt function in Excel.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here, lookup value means the value that we want to search, table_array means table name, col_index_num means index number and range_lookup means True i.e. appropriate match or False i.e. exact match.

In the above picture of Excel, an example of financial sheet is taken where there are 3 columns i.e. Companies, Revenues and Annual Budget. Using VLOOKUP function we can find the values of consolidated data.