Fiscal Quarter Percentage difference

[Walker_Ice]

Hi Everyone,

I have a table that calculates the revenue and expenses per quarter of the selected fiscal years. For the selected fiscal year, its easy to get what the profit percentage is for the quarters. But I want to be able to calculate the profit percentage difference is for the selected year and the previous year.
For example, If select "2023" as the year. I want be able to calculate the profit percentage is for the 2023 quarter (AMJ) and what the profit percentage is for the previous year quarter ('2022 AMJ" - in this example). I want to know what the percentage difference is between these two percentages. Is the business up during the quarter or is it down and by how much?

Any help would be appreciated. Thank you in advance.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Income Services						Expense_Dates	Expense_TotalSpent
2	Service	Date	Payment	Tips	Total		2/4/21	$858.00
3	MDL	4-Feb-21	$900.00	$0.00	$900.00		3/13/23	$750.00
4	LDL	13-Mar-23	$300.00	$25.00	$325.00		6/17/22	$75,000.00		FISCAL YEAR QUARTERS:	2023
5	POL	17-Jun-22	$200.00	$25.00	$225.00		6/7/23	$7,000.00			Revenue	Total Cost	Profits	Current Quarter profit %	Quarter difference profit %
6	POW	7-Jun-23	$900.00	$0.00	$900.00		7/10/23	$85,000.00		JFM	$917,101.00	($126,560.00)	$790,541.00	86.2%
7	MAN	10-Jul-23	$1,900.00	$0.00	$1,900.00		3/31/24	$851,002.00		AMJ	$2,700.00	($13,844.00)	($11,144.00)	-412.7%
8	INO	31-Mar-24	$23.00	$10.00	$33.00		5/26/23	$6,520.00		JAS	$1,900.00	($85,000.00)	($83,100.00)	-4373.7%
9	YOU	26-May-23	$875.00	$10.00	$885.00		2/18/23	$1,000.00		OND	$0.00	($77.00)	($77.00)	#DIV/0!
10	TBW	18-Feb-23	$750.00	$10.00	$760.00		6/1/23	$324.00		Year Total:	$921,701.00	($225,481.00)	$696,220.00	75.5%
11	DPW	1-Jun-23	$600.00	$10.00	$610.00		2/18/24	$4,243.00
12	MDL	18-Feb-24	$600.00	$5.00	$605.00		3/18/23	$4,537.00
13	RDL	18-Mar-23	$850,999.00	$5.00	$851,004.00		5/28/22	$8,789.00
14	NAM	28-May-22	$85,000.00	$0.00	$85,000.00		2/8/23	$75.00
15	PPP	8-Feb-23	$10,000.00	$10.00	$10,010.00		3/3/23	$433.00
16	PPE	3-Mar-23	$10,090.00	$10.00	$10,100.00		1/4/21	$2,224.00
17	PPE	4-Jan-21	$100.00	$0.00	$100.00		2/14/23	$42,235.00
18	INO	14-Feb-23	$23,998.00	$0.00	$23,998.00		11/1/23	$77.00
19	LDL	1-Apr-23	$300.00	$5.00	$305.00		1/3/22	$78.00
20	HDL	3-Jan-22	$8,500.00	$0.00	$8,500.00		2/25/23	$987.00
21	AND	25-Feb-23	$20,000.00	$0.00	$20,000.00		6/26/25	$4,567.00
22	WWW	26-Jun-25	$79,000.00	$5.00	$79,005.00		3/10/23	$76,543.00
23	WDL	10-Mar-23	$899.00	$5.00	$904.00		2/2/25	$9,357.00
Sheet5

Cell Formulas
Range	Formula
K6:K9	K6	=IFERROR( SUMPRODUCT( Income_TotalPay * IF($K$4="All", 1, (YEAR(Income_Dates) = $K$4)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
L6:L9	L6	=IFERROR( -SUMPRODUCT( Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
M6:M10,E3:E23	M6	=SUM(K6:L6)
N6:N10	N6	=M6/K6
K10:L10	K10	=SUM(K6:K9)

Named Ranges
Name	Refers To	Cells
Expense_Dates	=Sheet5!$G$2:$G$22	L6:L9
Expense_TotalSpent	=Sheet5!$H$2:$H$22	L6:L9
Income_Dates	=Sheet5!$B$3:$B$23	K6:K9
Income_TotalPay	=Sheet5!$E$3:$E$23	K6:K9

Cells with Data Validation
Cell	Allow	Criteria
K4	List	All,2021,2022,2023,2024,2025

 

[Walker_Ice]

I think, I see what your point is. Since we already have the profits % available. The y/y of the total cost % would add the most value, that way we have the profits % available and the total cost %.

Your approach is better than mine and it does show a % that is meaningful. But i do not have enough space to have Q1 2022 & Q2 2023. Thats why I needed to include it all into 1 singular formula.

 

[StephenCrump]

Sorry, not clear whether you still need help? If you do, please let us know what results you're looking to calculate?

 

[Walker_Ice]

Y/Y results usually measure $/$ as a %.

Example: Revenue Year 1 = $1 million, Revenue Yr 2 = $1.2 m, therefore Revenue is up 20% on a Y/Y basis.

As I said in Post #3, your approach compares two percentages:

	Income	Cost	Profit %	Profit $
Q1 2022	100	60	40%	40
Q1 2023	150	100	33%	50
			-7%	+25%

It makes sense to report income is up 50%. Or that profit is up 25%.

Your approach calculates -7%, which is not a meaningful number.

Also, if you're looking for Y/Y, I'm not sure why your formula are calculating quarterly?

Perhaps you could post some simplified data and the results you're expecting to see?

Yes, I still need help. I agree with what you said here and I would like to accomplish this in a singular formula because I don't have enough space to add a previous year quarter and current year quarter.
The way you are calculating % is more meaningful than how I was doing it. Please help me accomplish this in a singular formula.

 

[Walker_Ice]

Hi Stephen,

Do you think you will be able to help me accomplish this?

Thank you in advance.

 

[Walker_Ice]

Hi,

Is anyone able to help me with calculating the Y/Y profits for the Fiscal quarters? Similar to how companies that are publicly traded post their quarterly financials. Can someone help me accomplish this?
Thank you in advance.

 

[Walker_Ice]

How do I get help with this problem without reposting this and getting a warning again? Can someone help, please?

Thank you

 

[Walker_Ice]

Hi everyone,

If anyone is out there and can help me resolve this problem. I would greatly appreciate your support.

 

[StephenCrump]

Sorry, I didn't see any of your December messages.

If you're measuring Y/Y over quarterly periods, the Revenue change for the Sept 2023 quarter, say, is simply Sep 2023 Revenue/Sep 2022 Revenue - 1.

So, based on your screenshot, I'd expect the Sep 2022 results to have looked like this:

	A	B	C	D
1		Sep 22	Sep 23	Y/Y
2	Revenue ($B)	21.45	23.35	+8.84%
3	Net Income ($B)	3.29	1.85	-43.71%
4	Net profit margin	15.3%	7.9%	-48.28%
Sheet1

Cell Formulas
Range	Formula
B2	B2	=C2/1.0884
B3	B3	=C3/(1-43.71%)
D2:D4	D2	=C2/B2-1
B4:C4	B4	=B3/B2

A table like this is very easy to read and to check.

The problem you were having is going straight to Y/Y results from the granular data, without the intermediate quarterly summary results. It can be done in one go (e.g. as in post #8) but the formulae will necessarily be more complicated.

It's still not clear what results you'd like to see - your original post was only about change in profit margin?

Can you please repost your data, using XL2BB, and show what results you'd like to see?

 

[Walker_Ice]

Hi Stephen,

Thank you for reaching out. Let me try to break down exactly what I'm trying to doing using some of the textbook definitions. I would like to see the Y/Y growth rate & Y/Y Profit change.

Growth rate percentage Example.
For example, assume a business made $200,000 in total revenue compared to making $125,000 the previous year.
To find their YoY, take $200,000 (current year’s revenue) and subtract $125,000 (previous year’s revenue).
This gives you a change in revenue of $75,000. ($200,000 − $125,000 = $75,000)
Next, divide the change in revenue by the previous year's total revenue.
This gives you 0.6. ($75,000 / $125,000 = 0.6)
Finally, multiply 0.6 by 100 to get the YoY growth rate, which is 60 percent. (100 × .6 = 60% YoY growth rate)

I would do the same same for the Profit %.
Profit % Change=((New Profit−Old Profit)/Old Profit)×100

The only difference is that I am trying to get the Y/Y for the four quarters of the year. Similar to what you might find a quarterly report of a public traded company.

The challenge is to make this possible within a singular formula, due to the space constraint. This is where the biggest part of the challenge is.
I hope all of this information helps and the formulas are clear.

 

[StephenCrump]

Yes, I understand what you're trying to do - getting the result in E15 (or perhaps E21?) without showing the intermediate calculations.

	A	B	C	D	E
1		CurrentYr	PreviousYr	Y/Y
2	Revenue
3	Q1	200,000	125,000	+60.0%
4	Q2
5	Q3
6	Q4
7
8	Expenses
9	Q1	170,000	100,000	+70.0%
10	Q2
11	Q3
12	Q4
13
14	Profit $
15	Q1	30,000	25,000	+20.0%	+20.0%
16	Q2
17	Q3
18	Q4
19
20	Profit %
21	Q1	15%	20%	-25.0%	-25.0%
22	Q2
23	Q3
24	Q4
Sheet1

Cell Formulas
Range	Formula
D3,D21,D15,D9	D3	=B3/C3-1
B15:C15	B15	=B3-B9
E15	E15	=(B3-B9)/(C3-C9)-1
B21:C21	B21	=B15/B3
E21	E21	=(B3-B9)/B3/((C3-C9)/C3)-1

This should involve just a simple substitution of formulae ...

So for example, instead of E15: =(B3-B9)/(C3-C9)-1

you'd have:

E15: =(YourFormulaForQ1CurrentYearRevenue-YourFormulaForQ1CurrentYearExpenses)/(YourFormulaForQ1PreviousYearRevenue-YourFormulaForQ1PreviousYearExpenses)-1

Without seeing your layout, it's not clear what's not working for you?