BMW Financial Statement

Jan 2017 CDK Global, LLC Printed in U.S.A 1 of 7

CDK Global is a trademark of CDK Global, LLC. Lightspeed is a trademark of CDK Global, LLC.

LightspeedNXT

Configure the BMW Financial Statement BMW dealers submit a BMW financial statement to Alpha Omega Solutions, Inc (AOS). Information from their Trial Balance Worksheet and other reports are used to do so.

To use the BMW Financial Statement you can configure a financial statement spreadsheet template and populate it with information you copy from your Lightspeed trial balance. A sample NXT BMW 2017 spreadsheet file is provided to get you started. You can download it from Service Connect by going to the BMW Financial Statement topic and clicking the link.

Instructions for completing the spreadsheet mapping are given below. This mapping is a one time event. After reordering the pages in the final step save the configured file in a safe place. Then use the Save As feature for each monthly update and rename the file to something unique, such as BMW FS Jan2017. Save a backup template in case you overwrite the original file.

Step 1: Generate your Trial Balance Worksheet from Lightspeed and save to xls file.

Go to the General Ledger – Reports – Trial Balance Worksheet menu.

Use Consolidated Company, select the appropriate Month to report. Click Next, Next and Finish to generate the report on screen.

LightspeedNXT

B00000 © Jan 2017 CDK Global LLC 2 of 7

Click Save to File and in the Save as Type field select Excel Spreadsheet to save the report to an Excel file.

Step 2 : Generate your Profit & Loss Statement from Lightspeed and save to xls file.

Go to the General Ledger – Reports – Profit and Loss Statement menu.

Use All, select the appropriate Month to report. Click Next, Next and Finish to generate the report on screen.

LightspeedNXT

B00000 © Jan 2017 CDK Global LLC 3 of 7

Click Save to File and in the Save as Type field select Excel Spreadsheet to save the report to an Excel file.

Step 3: From the new excel file you just created, you will copy and paste your Trial Balance (TB) onto the Map TB BS Section sheet in the NXT BMW FS workbook that you downloaded from Service Connect. Your copy will overwrite the example data that is provided on the mapping page. You can then create links from your data on the mapping page to populate the balance sheet page for reporting to BMW, as outlined below.

In the final step you will reorder the spreadsheet pages so that the map pages are moved to the end and the BMW financial pages are in order in the first five tabs. It is important to complete that reordering before submitting the spreadsheet.

Procedure

Copy your financial information to the pages on the NXT BMW spreadsheet template

Copy lines as needed from the Lightspeed trial balance excel spreadsheet you saved in step 1 above. BS accounts go on the Map TB BS Section tab, P&L Consolidated amounts on the Map P&L Consolidated tab.

Copy the appropriate lines from the Profit & Loss statement for each department to the respective tabs; New Units department will go on the Map New Unit Sales tab, Used Units Department will go on the Map Used Unit Sales tab. Continue with each mapping tab. Tabs may need to be adjusted based on your departments.

LightspeedNXT

B00000 © Jan 2017 CDK Global LLC 4 of 7

Change all fields to number fields and format

On each tab that starts with Map the fields need to be formatted to make the data easier to work with. Within the Map tab worksheets, highlight all number fields.

Click-and-drag to highlight fields.

When the number fields are highlighted a box with a yellow diamond displays. Click the drop down and select Convert to Number.

Once that is done, prior to clicking anywhere else, right click and select Format Cells. Under Category select Custom. Choose #,##0. This will format the fields appropriately.

LightspeedNXT

B00000 © Jan 2017 CDK Global LLC 5 of 7

Link the fields from the mapping pages to the BMW Financial Statement pages

Linking is a one-time setup. After creating all of the links in your initial spreadsheet you will reorder the pages so the numbered pages are together at the front and then save the completed template for future reports. For those reports only the data entries need to be made.

Warning: If you add an account into the GL you will have to adjust the Mapping worksheet(s) to include the new account(s).

When linking your fields together, be sure you are using the correct field on the mapping pages. The BS will use the END BALANCE field. The P&L will use the MTD and YTD fields. Remember to enter your current earnings onto the BS pg1 tab of the NXT BMW spreadsheet in the Current Earnings section.

Link fields from the Financial Statement to the Trial Balance. In some cases multiple accounts will link to a single field. We’ll start with the one-to-one example and show multiple accounts on the following page.

For example: On the worksheet called Map TB BS Section the field M2 needs to link to the worksheet BS field H10.

Trial Balance spreadsheet saved from Lightspeed

NXT BMW FS worksheet BS tab

Create the Links: To link the fields you would start in the field on the BMW FS worksheet BS tab that you wish to link to, in this example H10, and enter an = sign. Then go to the Map TB BS Section tab and click in the M2 field. The number will highlight in a green dotted box, press Enter. Once you hit Enter the starting sheet (BS) is shown and the dollars are displayed in the mapped field.

Link field M2…

…to field H10 on the BS tab.

LightspeedNXT

B00000 © Jan 2017 CDK Global LLC 6 of 7

For multiple accounts:

There may be several accounts that need to be added together to make up one account on the

financial statement. This can be done by typing the = sign in the field to map and then typing

the + in each field that must be summed. Press Enter in the final field to sum.

Example: These accounts must add together to make up the one field on Financial statement.

Follow these steps:

1. In the appropriate cell on the Financial Statement enter the = sign.

Then go to TB spreadsheet, click in the first field and type the + sign, then click the next

number and the + sign, and so on. Once you have all accounts accounted for press enter.

This will take you back to the financial statement and show the sum of the selected fields.

Check your total to ensure you made the proper selections.

The green box displays the formula created.

LightspeedNXT

B00000 © Jan 2017 CDK Global LLC 7 of 7

Continue to do the mapping for all other worksheets. When this is complete, reorder and save your worksheet (see below). You will want to save one to use as your template. Once the fields are linked, you can paste the next month’s data right into the template and not lose the link.

Each month you can copy and paste your data onto the worksheets and the links will update the

BMW FS (use Ctrl C to copy, Ctrl V to paste).

Warning: If you add an account into the GL you will have to adjust the Mapping worksheet(s) to include the new account(s).

Additional entries and reports are needed to complete the financial statement.

Enter personnel and other info as needed.

Enter service labor rates.

Run the Tech Stats reports 607 and 608 from Service – Reports – Technicians.

For RO counts run report 627 in Service – Reports – Service History.

For Unit counts run report 761 in Sales – Reports – Quotes & Deals.

In Accounts Receivable generate the AR Aging Report by G/L Account. Place the dollar

values in the appropriate fields.

Go to the Receivables – Reports – Aging Report menu.

Final Steps: Reorder the Spreadsheet Tabs

The five numbered pages of the spreadsheet must be sequential and in order when submitted.

After completing your mapping and entries, click the tab for each of the Map pages and drag it

to the right to move the page to the end of the spreadsheet. Make sure the numbered pages

are the first five tabs, in order, before submitting. After renumbering save a backup of the

template.

Each time you update, save the current Statement using Save As to name the file with the date

in the file name. For example BMWFS 122016 or BMWFS Dec2016. Be consistent in your

naming convention.