财务报表构建数据库 [英] Structuring Databases for Financial Statements

问题描述

我正在寻找构建我的数据库的最佳方式。我对1997年至2012年的1000家公司有季度财务报表。每家公司都有三种不同的陈述，一种是损益表，一张资产负债表和一张现金流量表。

I am looking for the best way to structure my database. I have quarterly financial statements for 1000’s of companies from 1997-2012. Each company has three different statements, an income statement, a balance sheet, and a cash flow statement.

我想能够对数据进行计算，例如每个季度的加起来，以获得每个报表上每个行项目的年度总计。

I want to be able to perform calculations on the data such as adding up each quarter to get a yearly total for each line item on each statement.

我已经尝试了两种方法：

I have tried two ways so far:

1）将每个语句的每个行项目存储在自己的表中，即销售将一个表，只有我正在跟踪的所有公司的销售数据，公司作为主要关键，每个季度数据作为单独的列。这似乎是最简单的处理数据的方法，但每个季度更新数据是耗时的，因为有数百个表。

1) Storing each line item for each statement in it’s own table i.e. Sales would be one table and have only sales data for all companies I am tracking, with company as the primary key, and each quarters data as a separate column. This seems like the easiest way to work with the data, but updating the data each quarter is time consuming as there are hundreds of tables.

Sales Table
Company q32012  q22012  q12012
ABC Co.  500    100 202
XYZ Co. 230 302 202

2）另一个更容易更新，但更难处理数据的选项是为每个语句的每个公司分别设置一个表。例如，皇家银行的利润表将具有自己的表格，主列是订单项。

2) The other option which is a little easier to update but harder to work with the data is to have a separate table for each company for each statement. For example, the income statement for Royal Bank would have it’s own table, with the primary column being the line item.

Income Statement for Royal Bank
Line_Item   q32012  q22012  q12012
Sales            
Net Profit           

这里的问题是当我尝试年度化这些数据，我得到一个非常丑的输出由于该组由

The problem here is when I try to annualize this data, I get a really ugly output due to the group by

SELECT    
(CASE WHEN Line_Item = 'Sales' THEN SUM(q4 + q3 + q2 + q1) ELSE '0' END) AS Sales2012, 
(CASE WHEN Line_Item = 'NetProfit' THEN SUM(q4 + q3 + q2 + q1) 
      ELSE '0' END) AS Inventories2012
FROM            dbo.[RoyalBankIncomeStatement]
GROUP BY Line_Item

任何帮助将不胜感激。

Any help would be appreciated.

推荐答案

每当我不得不在财政季度，月份或年份或任何地方建立财务报告数据库时，我们发现从Star模式设计和数据仓库借鉴一个概念是方便的，即使我没有真正构建DW。

Whenever I've had to build a database for fiscal reports by fiscal quarter, month, or year or whatever, I've found it convenient to borrow a concept from star schema design and data warehousing, even if I'm not really building a DW.

借用的概念是拥有一个表我们称之为ALMANAC，每个日期有一行，按日期键入。在这种情况下，自然键可以很好地工作。这里的依赖属性可以是日期所属的会计年度和季度，无论日期是企业开业的时间（TRUE还是FALSE），以及公司日历中的其他怪癖。

The borrowed concept is to have a table, let's call it ALMANAC, that has one row for each date, keyed by the date. In this case a natural key works out well. Dependent attributes in here can be what fiscal month and quarter the date belongs to, whether the date was one where the enterprise was open for business (TRUE or FALSE), and whatever other quirks are in the company calendar.

然后，您需要一个刚刚生成此表的计算机程序。公司日历的所有奇怪的规则都嵌入在这个程序中。 ALMANAC可以覆盖十几年，超过3650行。这是一个小桌子。

Then, you need a computer program that just generates this table out of nothing. All the strange rules for the company calendar are embedded in this one program. The ALMANAC can cover a ten year period in a little over 3,650 rows. That's a tiny table.

现在操作数据中的每个日期都可以像ALMANAC表中的外键一样使用，前提是您一直使用Date数据类型进行日期。例如，每个销售都有销售日期。然后在财政季度或财政年度或任何您喜欢的情况下进行汇总只是与ALMANAC一起加入运营数据，并使用GROUP BY和SUM（）来获取所需的总计。

Now every date in the operational data can be used like a foreign key into the ALMANAC table, provided you consistently use the Date datatype for dates. For example, each sale has a date of sale. Then aggregating by fiscal quarter, or fiscal year, or whatever you like is just a matter of joining operational data with the ALMANAC, and using GROUP BY and SUM() to get the aggregate you want.

很简单，它使得整个时间段的报告变得轻而易举。

It's simple, and it makes generating a whole raft of time period reports a breeze.

这篇关于财务报表构建数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！