Lab - Financial Statements

Financial Statements This lab will give you a real-world example of how accountants can use databases and Excel PivotTables to generate summary financial reports for managers. The first part will take you through the process of importing data from an ERP system and creating queries to transform data elements into a traditional debits and credits. The second will have you work with a PivotTable to find account balances and generate a Profit & Loss (Income) Statement. Algorithm If we were to set this up as an algorithm to convert sales transactions to financial statements, the general steps would be: 1. Calculate line item totals for sales and returns, and cost of goods sold. 2. Convert line items to daily journal entries – debits and credits. 3. Combine all the journal entries into a single de- normalized table. 4. Pull the table into an Excel PivotTable. 5. Create a data pipeline – match database account names to financial statement account names. 6. Pull data from the PivotTable into the Financial Statement. IMPORTANT SQLite DB Browser only works with a database file saved on your local computer. Before you leave class, be sure to save your database to Teams or a flash drive or YOU WILL LOSE YOUR WORK. Tips and Tricks:  Be very careful with your spelling, including spaces, hyphens, etc.  In Excel, if your numbers in the P&L do not match the given totals, first check the spelling of your account names in Column B. Remember that they must exactly match the name from the Chart of Accounts so that Excel can find it in the PivotTable. Be especially careful about extra spaces after names or around hyphens; they are very difficult to see.  You must have only one of the applications (Excel) open at one time when using your linked files. This is because the ODBC link places a lock on the data to ensure data integrity and will not allow the data to be edited in multiple places.  After making changes to your database queries, you must complete the following steps to refresh the data.  Rerun the FinancialData query to update the output file.  Open Excel. From the Data tab, click Refresh All. This refreshes the data from your data.  Click in the PivotTable. From the Analyze tab, click Refresh. This refreshes the PivotTable.  The following record counts will help verify the accuracy of data imports and queries:  FinancialData Query: 2811 1

Part 1: Create a shadow database in SQLite and use queries to transform the data Download the shadow database. In the real world, we would ask the IT administrator to extract data from BlueGold Industries’ ERP system and send us the raw CSV files. 1. Download the BluGoldData.zip file and extract the following CSV files: a. ChartOfAccounts.csv b. MonthEndJE.csv c. Returns.csv d. Sales.csv 2. Open DB Browser for SQLite. 3. Click New Database . 4. Name the database “BluGold-YourName.db” and click Save . 5. If the Edit table definition window appears, click Cancel . 6. Import your four tables: a. Go to File > Import > Table from CSV file… b. Navigate to your csv file and click Open . c. Check the box Column Names in First Line . d. Preview your data. Double check that the dates are in YYYY-MM-DD format. e. Click OK , twice. f. Repeat this step for the remaining tables. 7. When you’re finished, your window should look like this: 8. Click Write Changes or press Ctrl+S to save your database. Use queries to create a data map. Once you’ve imported the data, you need to make the data more report-friendly. For example, sales transactions are recorded as single line items with each item price and cost. Also, ERP tables only include unit prices and quantities sold, so you’ll have to create queries to find the total price on each invoice. For the financial statements, it would be useful to create line items that mimic the traditional debit and credit journal entry format. Algorithm Step 1: Calculate line item totals for sales and returns, and cost of goods sold. 9. Create a query called InvoiceHeader that calculates Sales items subtotal selling price and cost: a. Click the Execute SQL tab b. Enter the basic query to select all data from Sales and click Run or press Ctrl+R: SELECT * FROM Sales ; c. Now calculate the subtotals for each line. Edit your query to include the green text below and run your query again. Note: the AS function will rename the calculated columns . SELECT *, UnitPrice*Qty AS Price, UnitCost*Qty AS Cost FROM Sales; d. Click the Save button and Save as View menu item. Name this InvoiceHeader . 10. Click the Open Tab button to create a new query . 2

SELECT "" AS JournalEntry, " XX- XXXX-XX" AS AccountNumber, Function(Field) AS Debit, Function(Field) AS Credit, TranDate AS TransactionDate FROM TableName GROUP BY TranDate; 3. Click the Browse Data tab and select one of the queries (any query is fine) from the Table drop- down list. TAKE A SCREENSHOT (1) . Tips:  You need to create 6 separate queries using the following SQL template.  Save the query view as Query View Name.  If you get an error, check that the fields and values match exactly and remove any spaces after the final;  In this case, use "" to show a blank value in the query.  Use " XX-XXXX-XX" to add an account number (e.g. Sales Revenue or COGS) to the new journal entries.  Use Function(Field) to calculate an aggregate function (e.g. SUM).  Use the AS command to rename the columns so they match those in MonthEndJE.  For example, the first query should be formatted like this: Query View: ProductSales SELECT "" AS JournalEntry, "10- 4000-00" AS AccountNumber, "" AS Debit, SUM(Price) AS Credit, TranDate AS TransactionDate FROM InvoiceHeader GROUP BY TranDate; 4 Query View Name Purpose Account Number Debit Credit Table ProductSales To find daily sales revenue 10-4000- 00 "" SUM(Price) InvoiceHeader ProductCost To calculate daily cost of goods sold 10-5000- 00 SUM(Cost) "" InvoiceHeader ShippingCost To calculate daily shipping expenses 10-5020- 00 SUM(ShippingCost ) "" InvoiceHeader ProductSalesReturns To calculate daily returns revenue (negative) 10-4003- 00 "" SUM(Price) ReturnsHeader ProductCostReturns To calculate daily returns cost of goods sold (negative) 10-5050- 00 SUM(Cost) "" ReturnsHeader ShippingReturns To calculate daily cost of return shipping expense 10-5015- 00 SUM(ShippingCost ) "" ReturnsHeader

Algorithm Step 3: Combine all the journal entries into a single de- normalized table. 1. Next we need to combine all the journal entries into one master query using the UNION ALL command. Create a new query called UnionAll : Note: there should be 7 SELECT statements in this query and the last line does NOT include UNION ALL, but needs a semi-colon at the end. SELECT * FROM MonthEndJE UNION ALL SELECT * FROM ProductSales UNION ALL SELECT * FROM ProductCost UNION ALL SELECT * FROM ShippingCost UNION ALL SELECT * FROM ProductSalesReturns UNION ALL SELECT * FROM ProductCostReturns UNION ALL SELECT * FROM ShippingReturns; 2. Now that we have journal entries, we should pull in the account names from the chart of accounts, so we can find the balances that will appear on the financial statements in Excel. This will be our final query. Create a new query called FinancialData : SELECT ChartOfAccounts.AccountNumber, strftime('%Y', TransactionDate) AS Year , strftime('%m', TransactionDate) AS Month , SUM( ABS(Credit) ) AS Credit, SUM( ABS(Debit) ) AS Debit, AccountState, AccountCounty, AccountCity FROM UnionAll INNER JOIN ChartofAccounts ON UnionAll.AccountNumber = ChartofAccounts.AccountNumber GROUP BY ChartofAccounts.AccountNumber, Year, Month, AccountState, AccountCounty, AccountCity; Note: The strftime() functions will pull a year and month number from any date field. In MSSQL you could use a YEAR() or MONTH() function. This helps us show the balances for a given period in Excel. The ABS() function will only pull in numbers and ignore any text in the field. We use INNER JOIN to match values from our journal entries to their corresponding accounts using primary/foreign keys. 3. Run the FinancialData query (it will take about 30 seconds to run), and TAKE A SCREENSHOT (2) of the query screen with the code and datasheet.  FYI, your FinancialData query should show 2811 records 4. Export your table as a CSV file. Click Save View > Export to CSV. 5. Click Save and name the file FinancialData- YourName.csv 6. Finally, click Write Changes or press Ctrl+S to save your database. 7. Exit SQLite Browser and upload your database and FinancialData.csv file to your OneDrive. a. HINT: You can locate your file by going to Start > Computer, then navigating to Hard Drive (C:) > Users > (your username) > Downloads or Documents or Desktop 5

10. Check the boxes next to Account State, County, and City fields to add them to Rows, in descending hierarchical order (state being the highest). Note: you may have to resize the lower right portion of the screen to have enough space for all the fields. 11. Create a new field called “Value” within the PivotTable: a. From PivotTable Tools select Analyze > Fields, Items & Sets > Calculated Field . b. Enter “Value” in the Name field. c. Enter “ABS(Credit-Debit)” in the Formula field. Note: This will provide the absolute value so that it is always positive. We will apply the appropriate field sign as needed in later calculations. d. Click Add. 12. Click OK. 13. Your sheet should be filled with numbers and the PivotTable pane should look like this: 14. Take a SCREENSHOT (3) of the FinancialData PivotTable sheet and paste it into your lab document. Be sure to include your name and e- mail from the title bar. 7

Algorithm Step 5: Create a data pipeline – match database account names to financial statement account names. Create a “data pipeline” to connect the report-friendly account names to the account names used in the database. 1. Return to the P&L worksheet. 2. Highlight Columns A-D, right-click the column and choose Unhide. Note: To save time, I've already filled in these values for you, shown on the next page. These columns will be hidden in the finished product. Think about why we’re adding titles that are different from those that appear on the financial statements. Hint: Which one is more useful for a reader of the financial statement? 8