Modeling a company that doesn’t give 3 months ended cash flow for each q. They give 3 months ended q1, 6 months ended q2, 9 months ended q3… So if I’m entering one line item say change in inventory in cols a:d a | b | c | d | inv 2.5 5.5 8 11 is there anyway to copy the x - “c-b-a” part without copying over the number? So you can bang out the numbers the way the company reports and then still break it down by three months ended without having to manually subtract each line item from the previous total (which needs to be subtracted from the previous total…). hopefully someone understands what I’m trying to say… end of the day… not articulating clearly
I’m pretty good with Excel, but I really don’t know what you’re asking here : "is there anyway to copy the x - “c-b-a” part without copying over the number? So you can bang out the numbers the way the company reports and then still break it down by three months ended without having to manually subtract each line item from the previous total (which needs to be subtracted from the previous total…). "
Assuming for example your FY revenue is $100M, and sum of Q1/2/3 is $80M; and assume your FY Op Costs are $70M, and sum of Q1/2/3 is $60M: I think you’re saying that in your Revenue line, your formula in Q4 reads =(100-A1-B1-C1) and gives a result of $20 for Q4 3 months ended. Now however, you want to take this backed-in formula and fill it down, so that your Op Costs formula reads =(70-A2-B2-C2) and gives a result of $10 for Q4 3 months ended. However, you’d have to copy the formula and manually overwrite the original 100 with a 70. Assuming that’s what you’re asking, the best way to do it is to add a fifth column where you input the FY number, and replace the 100 in my first formula with a reference to this fifth column. That way you can copy down this backed-in formula, and rather than manually overwriting in each formula, you just have to enter your FY numbers in the fifth column. It’s still work but easier to write in blank cells than to overwrite a portion of a formula. Geez. That’s 5 mins I could’ve been working.
hey buckaroo thanks for taking the time. That was exactly what I am asking and I thought about your solution but I can’t easily add another column because it is tied to multiple sheets and col A on one needs to be the same quarter as col A on all of them. trying to figure out how to do it with the formulas
bump… still hoping someone knows how to do this with formulas buckaroo’s example is better than mine in the first post at explaining what I’m trying to do
Why can’t you add in a fifth column? It will not effect columns A-D if you place a FY in column E. No?
if it is just a column to reference a formula just put it anywhere and hide it. Maybe I am missing something.
because I can’t easily add a fifth column to multiple other sheets say cash flow is sheet 1 A is Q1 08, B is Q2 08 If I want to add a column to get the 3months ended data from 6 months ended C would be the extra column… but on my income statement, segment, balance sheet, ratios sheet… etc column C is Q3 08… and since multiple people can go in and out of the model you dont want them to not match up (makes it a nightmare for linking - remember each year would need 3 extra columns) or have hidden columns. I thought about doing it that way it just doesn’t work well for my particular issue - need to figure out the formula way to just concatenate a formula list to a number so I can enter the reported number then add the -a for 6 months ended -b-a for 9 months ended -c-b-a for 12 months ended sorry, I realize this is a bit jumbled I just don’t have time to make it any clearer right now
why cant you park the fifth column out to the far right where theres no data?
I’m not sure if this will work. But, try putting in the formulas part in place first (i.e. =-b-c-d). Copy and paste this all the way down your column. Then enter the data in a separate excel workbook, “as is” in the earnings release (or 10-Q). Then copy>paste special>“add”,“values” back in your original workbook. Then throw away the separate excel workbook.
you da man