Build better financial reports in Excel

Excel is a powerful tool. Almost every small company uses Excel to track budgets, forecasts and long range plans. But, Excel files can quickly get out of hand if not planned appropriately. I've heard that time spent in Excel should 80% planning and 20% execution, especially if the file will be used frequently in the future. Below, I discuss a few tips on how to maximize efficiency while avoiding complexity in your Excel spreadsheets.

A clean dataset, like the intheBlk LRP model export example above, will make your data analysis easier.

1. Store raw data in one tab - the ability to sort and filter in one place instead of tracking data in several sheets or workbooks will save you time in the long run. If multiple users will be reviewing the raw data, it makes it easier for other users to see the complete set of data without getting lost.
2. Perform no manual manipulation on your source data - keeping your raw data untouched will avoid performing repetitive tasks if your source data changes. The more manipulation of your raw data, the more time spent each time the data changes. If possible, use formulas in a separate tab to manipulate the source data instead of doing it manually.
3. Don't store in one cell what could be stored in several cells - similar to tip #2, avoid storing two pieces of information in one cell. 99% of the time, it is easier to combine two separate pieces of data into one cell instead of splitting two pieces of data from one cell.  
4. Keep inputs, calculations and outputs in separate tabs - organizing your inputs, calculations and outputs in separate tabs will help users know what belongs where and avoid manual errors related to missed cells being updated. Users then know what to focus on in each section of your spreadsheet.
5. Use smaller, intermediate formulas instead of large, single-cell formulas - adding this practice into your spreadsheets will make your formulas easier to follow and review.
6. Use the named range feature - the named range feature will help keep your formulas easier to follow and reduce the error of mis-referenced cells. I recommend using consistent nomenclature when using named ranges. For instance, a data tab might be named data_programs, data_department, data_year1_total, etc. This way, you ensure that your formulas are pulling the right data for what you are trying to evaluate.

At intheBlk, we are experienced working with Excel. That is why our export functionality is built to maximize Excel functionality. We also offer our customers a standard Excel template that includes some of the tips referenced above. That way, you can focus on what matters most - getting the underlying assumptions and overall story right for your key stakeholders. That is how we are empowering you to tell your drug's financial story.