You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Layout of Spreadsheet

Advertisement

Hi Nathan,

I'll pay your more than $5 to look at the spreadsheet and tell me if there's an easy, cleaner way to get what I want. If there is, I'll come back and pay you more to explain it if that is appropriate.

For part of the work I do, I am creating an excel template to help me enter a company's detailed income statement data for 3-7 (usually 5) historical years, make appropriate normalizing adjustments to line items, calculate the resulted adjusted income statement numbers, and create a common-size income statement for all years.

The attached sample spreadsheet with made-up numbers shows only three years of this data so that you can view it more easily. I usually work with at least five years of historical IS data.

What I want to achieve:

1. Do the steps I described above. The attached sample sheet does that.

2. Be able to print out four separate pages of this data. These are the four to print out for each company:

a) Historical ISs - Actual Data for all 5 years (Sample attached)

b) Historical ISs - Adjustments for all 5 years

c) Historical ISs - Adjusted for all 5 years

d) Historical ISs - Common-Size for all 5 years

3. Carry forward to one or more other spreadsheets for analysis all items I've highlighted in green. Most but not all of these are totals.

I can accomplish the above by putting all the data on one spreadsheet and using a separate Custom View to print each of the four print-outs.

My question: Is there is an easy way to accomplish this with a template that has a separate spreadsheet for each of the four printouts I described above? Or is putting all of this data in one spreadsheet as I have and using Custom Views a very reasonable way to do it?

Complicating factors for creating multiple spreadsheets seem to include: (1) Each company's spreadsheets can be for a different number of years. (2) They will have a different number of line items in sales, cost of sales, and operating expenses. (3) Some line items (see the ones I highlighted in yellow) may not appear in the initial year(s).

Thanks for your basic thought about this, Nathan.

Ray:

I would be happy to help you with this project.

#2 - I imagine setting up a summary tab in your workbook with a drop-down box at the top. In this box you will select a, b, c, or d and then the sheet will display the appropriate income statement using vlookups or index/match.

#3 - Also a separate tab where I can pull forward the green items using vlookups or index/match.

You mention using custom views and defined print ranges; however, I think I prefer having a summary page to display the financial statements. In this way, you can actually view first what you want to print quickly and easily.

Complicating factors:

1) Index/match can handle this as long as each column is labeled appropriately (which it is now).

2) Also not a problem for Index/match or vlookup.

3) Also not a problem. What I will do is just display zero whenever the spreadsheet can't match up a category. Thus, your summary tab will show all possible categories... and then just show zero when it needs to.

If you want to proceed further please email me your sample spreadsheet to Lotus@swbell.net (the items you uploaded get automatically converted to pictures so I cannot use those unless I retype everything). You are welcome to email me actual data - I will keep it confidential.

Please note I do work regular hours at a regular job so I may only reply in the evenings when working on more elaborate projects such as this one.

As for the cost, I'm a satisfaction guaranteed kind of guy. You can pay me what you think is fair based on what you receive.

- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thanks for the quick and thorough answer, Nathan. I see why you're rated so highly. I'm adding to that! |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (

I have been using spreadsheets since Lotus 1-2-3 was released. **Education/Credentials**

Certified Public Accountant (CPA)