Balance Sheet

Procedure

Step 1 Create the Report Options and dates for our report

Navigate to the Ribbon tools and click on Report Options.

when you do that, a new Report Option Page will pop up. on the pop-out, you can fill in as shown below.

We now have our Report Option created. A new page titled Option should appear in our workbook, this is where we create our dates.

• create the date using the formulas shown below

Note: While creating our previous Year’s date, you must notice that we created it in such a way that we can run the full period for last year, and select a range for the current year. E.g. we are in April for the current year, the way the formula is created allows us to run for Jan-April current year, and get the whole of last year.

“Date (D15,1,1) & Date(D15,12,31)“ This simply means get the value in cell 'D15', which is the year, then “1,1,” tells is it 1st of January while “12,31,” says 31st of December.

So, you can always select Jan-to-current month for the current year from the pop-out and still get the whole of the previous year

• we now show the date range on our report page for easy access

• 
  

This can simply be done by “= (then navigate to the options page and select the date)“. Do this for the period start & end of both years.

• Also for the title (year “2023“), we use the formula to reference the year in cell E4. We also want the title to change along with the year we run the report.

• we use the same formula for (the year”2022”) but in this case, Cell F4.

Now, our G/L account will be referencing that date at the top of the report.

Step 2(a) Create a template.

The first thing to do is to create a template of what you want your report to cover, and how you want your report to display.

• Assets

• Liabilities

• Equity

Step 2(b) Adding G/L Account Number to your report.

Once your template is ready the company G/L Account Number needs to be mapped to the template as well as the date range you want the result to display on. just as shown below.

Step 3 Building our Jet function.

Now that we have our G/L Account no mapped, we can proceed to create our Jet function. we do this by clicking on the function we want to use, the GL Function.

When you click on the G/L function, it brings up our function wizard, then we can fill in all that we want.

We fill in as shown below. in this report, we want to show the balance, with the account number selected. we lock the column of account no by pressing the “fn function key with F4“. we select our start date and end date and lock down the row. if we want an additional filter, we can add then in the space. when you scroll down, you see a space where we have company. you can select the name of your company there.

Once you’ve filled in the requirements, click on okay to close the wizard.

Step 4 Building the jet function for other cells

Since we’ve locked the necessary columns and rows, we can easily complete the other functions by simply copying and pasting or by just dragging as shown below.

You can calculate other functions you have there using normal Excel formulas. e.g. Calculating Total Revenue, margin %, etc. An example is shown below.

Step 5 Running your report

When you run your report, it should come out clean as shown Below.

Note: you can include the date range as a report option, this allows Jet users (viewers) to be able to select the date range without changing the report.