Financial Model Challenge & Assistance

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135
Alright, here's my challenge:

I am attempting to streamline our data entry and reduce the redundancy of input. Currently we have a software program that we input audited financial statements of various companys to monitor their performance. The sole purpose of this software is essentially data storage. It kicks out a few basic calculations but other than that it is worthless and serves no point. Regardless, we have it for now. It does however, provide the ability to to spit out stored financial statements in .csv, which inherently is raw data and is useful to me, but not currently used. As it stands this piece of software (POS), prints the financial statements for multiple periods which we can then perform both vertical and horizontal "eyeball" analysis. If anything beyond that is required, we are at the mercy of punching in the data again, and using arcane methods of extrapolating seemingly meaningful data that an underling without a financial background decided to include in his/her analysis. Hopefully you see where this is going. My goal is to use this information in a more productive and efficient manner by creating a financial model that will analyze this information for Senior Management using dashboard reporting through integrating a standardized template in which existing data (POS) can provide the input (.csv format) which can then be "put through the ringer" and sensitized for reporting purposes. I have been reading several books about financial modeling studying various posts on this message board have produced a fianancial model that should suit our needs. It is still a work in process, features still need to be added, but my model will require inputs regardless so now is as good of a time as any to seek your help.

Above is an overview, and the .csv output file looks like this:

My financial model contains different accounts for simplicity and modeling purposes. Main acounts such as Accounts Payable, Acounts Receiveable, Inventory, Fixed Assets, Accumulated Depreciation, Retained Earnings, Current Maturities, Long Term Debt, Intangible Assets, etc. will *usually* be included on every financial statement. The POS has the ability to customize individual line items or set them to default line descriptions. I can make it a policy to default any custom lines before exporting to a .CSV if that is required and helps to narrow the range of possibilities. The goal in this step is to take the .CSV raw data and input as much data into my financial model so that people do not have to input the data twice (1st time into POS, 2nd time into my model). My suggestion for the accounts that don't fit my models accounts is to dump them into a "Other" line in the appropriate section of the Balance Sheet (B/S) or Income Statement(I/S) For example: 'Other Current Assets', 'Other Current Liabilities', 'Other Long Term Assets,' Other Long Term Liabilities,'.

My model looks like this:

Much thanks in advance to anyone up for an ongoing challenge. In know this will not be an easy task. I didn't mention it, but I would also like this to be an automated feature, meaning that once the .CSV has been created, the user opens the financial model template and clicks an 'Import .CSV' file to upload the accounts. Please keep in mind that although I am familiar with excel, to the point that I understand pivot tables and regression analysis, I do not, nor have I ever used VBA. I have recently purchased a book that I am learning, so things will need to be spelled out for me concerning that topic (maybe others). Thanks.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Welcome to the board.

As a starting point, it would help to understand what the data looks like. If you don't want to use the POS (clever acronym, by the way), what does the raw data you enter to the POS look like (how many fields, how many companies/records, etc.)? If you can post a sample (see the link to "Download Colo's HTML Maker..." at the bottom of this page) of either the raw data or the .csv output from the POS if you have to keep that for whatever reason, that would help.

Excel may or may not be an appropriate tool for what you're trying to do. We'll be better suited to offer advice once we understand/see what you're working with.
 

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135
CSV<SCRIPT language=JavaScript src=""></SCRIPT>
Sample CSV File from BH.xls
ABCD
1CompanyXYZUnqual.Unqual.Unqual.
2October10,2006Dec.31Dec.31Dec.31
3StatementinThousands$200220032004
4UserUserUser
5AccountantAccountantAccountant
6------------------------------------------------------------------------------------------------------
7ASSETSCommonSized
8------------------------------------------------------------------------------------------------------
9Cash6127102
10
11TradeAccountsReceivable416146625359
12ReserveforBadDebts(-)-453-217-132
13---------------------------------------------------------------
14NetAccountsReceivable370844455227
15
16TradeNotesReceivable15517990
17
18Inventory201851812818140
19---------------------------------------------------------------
20TotalCurrentAssets241092277923559
21
22NotesReceivable-Noncurrent233900
Sample CSV File from BH
 

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135
Financial Model<SCRIPT language=JavaScript src=""></SCRIPT>
Financial Model V.8.xls
ABCDEF
28BalanceSheetforXYZCompany
29ThousandsofDollars
30Historical
312002(05)2003(05)2004
32Assets
33CashandMarketableSecurities$262.6%$232.4%$32
34AccountsReceivable$9910.2%$10310.6%$107
35Inventories$11011.3%$10811.1%$115
36OtherCurrentAssets$9710.0%$919.4%$104
37TotalCurrentAssets$33134.1%$32533.5%$358
38
39Property,PlantandEquipment,Gross$68170.2%$73475.7%$821
40AccumulatedDepreciation$24525.2%$29730.6%$353
41Property,PlantandEquipment,Net$43644.9%$43845.1%$468
42
43OtherNon-CurrentAssets$20320.9%$20521.1%$407
44TotalNon-CurrentAssets$63965.9%$64366.2%$875
45
46TotalAssets$971100.0%$968100.0%$1,233
Model
 

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412

ADVERTISEMENT

One way to do it!

MrGu,

If your cvs data have all the times (absolutely always) the same format and cells allocation, you may import it in an external excel workbook not related to your current financial model. Now, save it with an unique name, ie, RawDatafromCVS.xls. Then, you link your model to all cells in RawDatafromCVS.xls as needed.

Once you get a new CVS file, you follow the same procedure to import the data again, and overwrite the original RawDatafromCVS.xls, now you have your financial model linked to a newer data any time you need it. This assumes the raw data in the file ALWAYS have the same location.

Did I understand well what you need? I hope this helps!

Thanks!
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Fair enough.

Is there a way to get a master list of accounts (cash, trade accounts receivable, etc.) from the POS?

I'd suggest setting up (one time) a master mapping of POS account to your balance sheet account. Once that is set up, it should be a piece of cake to use some VLOOKUP and SUMIF formulas to get you what you want.
 

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135

ADVERTISEMENT

Good suggestion.

Gentlemen, truly appreciated. It is a good suggestion. Could you please provide a link or breif description on how to map the cells? Thanks again. I'll look into the master list of acounts suggestion.

A final thought, does the master list ALWAYS need to include the accounts in the same cell reference? For example: Accounts Payable for Company XYZ may be cell A26, however, in Company ABC it may be A38. I'm assuming this wont matter becasue of the VLookUp function. Is this correct? If so, then in the Vlookup function, is there a way to tally up all those that were not selected and place them into an Other Account?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Nothing fancy about creating the mapping, just need POS account in one column and your corresponding balance sheet account in another (as below):

For your other question, the placement doesn't matter. You'll be using VLOOKUP (or equivalent) to get the balance sheet equivalent for your accounts from the POS. Then, use SUMIF (or equivalent) to get the totals of the balance sheet accounts.
Book20
ABCD
1POS accountBalance Sheet Account
2CashCash & Marketable Securities
3Cash & Cash EquivalentsCash & Marketable Securities
4Accounts ReceivableAccounts Receivable
5Trade Accounts ReceivableAccounts Receivable
6Trade Notes ReceivableAccounts Receivable
7InventoryInventory
8InventoriesInventory
Sheet1
 

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135
BakerHillSampleMasterAccountList.xls
ABCD
1TestCoOther
2October10,2006Dec.31
3StatementinThousands$2005
4
5
6----------------------------------------------------------------
7ASSETSCommonSized
8----------------------------------------------------------------
9Cash22.0
10CertificateofDeposit22.0
11MarketableSecurities22.0
12
13TradeAccountsReceivable22.0
14Accts.Rec.fromAffiliates22.0
15Accts.Rec.fromRelatedCo.22.0
16OtherAccountsReceivable22.0
17OtherAccountsReceivable22.0
18OtherAccountsReceivable22.0
19ReserveforBadDebts(-)(2)(2.0)
20--------------------
21NetAccountsReceivable109.8
22
23TradeNotesReceivable22.0
24OtherNotesRec.-Current22.0
25FederalIncomeTaxReceivable22.0
26OtherReceivables22.0
27OtherReceivables22.0
28
29RawMaterials22.0
30WorkinProgress22.0
BakerHillSampleMasterAccountLis
 

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135
Great job guys, thanks for the suggestions! I'll let you know how it works out.
 

Forum statistics

Threads
1,136,878
Messages
5,678,326
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top