Actual vs Budget in Pivot Table Format

[#1]

Hi, I am the first time QV user and I encounter problem to create a simple Pivot Chart/Table to compare Actual, Budget and Variance (Actual-Budget) results. Can someone help me asap ?.

Many Thanks, Steven

Below are data source - Excel files for Actual and Budget

ABC PTE LTD
	Jan-08	Feb-08	Mar-08	Apr-08	May-08	Jun-08	Jul-08	Aug-08	Sep-08	Oct-08	Nov-08	Dec-08	Jan-09	Feb-09	Mar-09	Apr-09	May-09	Jun-09	Jul-09	Aug-09	Sep-09	Oct-09	Nov-09	Dec-09
	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT	ACT
Sales Volume (Kgs)	3,600	2,250	2,680	2,880	3,800	3,800	3,800	2,450	2,880	3,080	4,000	4,000	3,900	2,550	2,980	3,180	4,100	4,100	4,100	2,750	3,180	3,380	4,300	4,300
Net Revenue	192,000	116,000	136,400	146,400	204,000	204,000	192,200	116,200	136,600	146,600	204,200	204,200	192,300	116,300	136,700	146,700	204,300	204,300	192,500	116,500	136,900	146,900	204,500	204,500
Gross Profit	66,854	5,728	23,963	46,980	68,069	62,998	67,054	5,928	24,163	47,180	68,269	63,198	67,154	6,028	24,263	47,280	68,369	63,298	67,354	6,228	24,463	47,480	68,569	63,498
Operating Profit	42,546	(33,458)	(12,289)	14,813	32,849	25,387	42,746	(33,258)	(12,089)	15,013	33,049	25,587	42,846	(33,158)	(11,989)	15,113	33,149	25,687	43,046	(32,958)	(11,789)	15,313	33,349	25,887
EBITDA	41,296	(35,291)	(14,705)	12,397	30,433	22,971	41,496	(35,091)	(14,505)	12,597	30,633	23,171	41,596	(34,991)	(14,405)	12,697	30,733	23,271	41,796	(34,791)	(14,205)	12,897	30,933	23,471

ABC PTE LTD
	Jan-08	Feb-08	Mar-08	Apr-08	May-08	Jun-08	Jul-08	Aug-08	Sep-08	Oct-08	Nov-08	Dec-08	Jan-09	Feb-09	Mar-09	Apr-09	May-09	Jun-09	Jul-09	Aug-09	Sep-09	Oct-09	Nov-09	Dec-09
	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD	BUD
Sales Volume (Kgs)	2,100	2,500	3,000	3,700	4,500	2,900	2,200	2,600	3,100	3,800	4,600	3,000	2,300	2,700	3,200	3,900	4,700	3,100	2,400	2,800	3,300	4,000	4,800	3,200
Average Price ($/Kg)	$45.24	$45.50	$45.00	$45.00	$45.00	$45.00	145	146	145	145	145	145	245	246	245	245	245	245	345	346	345	345	345	345
Net Revenue	95,000	91,000	99,000	99,000	99,000	99,000	95,100	91,100	99,100	99,100	99,100	99,100	95,200	91,200	99,200	99,200	99,200	99,200	95,300	91,300	99,300	99,300	99,300	99,300
Gross Profit	45,928	41,095	36,166	42,385	43,644	43,835	46,028	41,195	36,266	42,485	43,744	43,935	46,128	41,295	36,366	42,585	43,844	44,035	46,228	41,395	36,466	42,685	43,944	44,135
Operating Profit	(16,022)	(17,655)	(24,584)	(16,765)	(15,506)	(15,315)	(15,922)	(17,555)	(24,484)	(16,665)	(15,406)	(15,215)	(15,822)	(17,455)	(24,384)	(16,565)	(15,306)	(15,115)	(15,722)	(17,355)	(24,284)	(16,465)	(15,206)	(15,015)
EBITDA	(14,772)	(15,155)	(20,626)	(11,348)	(8,631)	(6,982)	(14,672)	(15,055)	(20,526)	(11,248)	(8,531)	(6,882)	(14,572)	(14,955)	(20,426)	(11,148)	(8,431)	(6,782)	(14,472)	(14,855)	(20,326)	(11,048)	(8,331)	(6,682)

[spsrk_84]

Hi,

Can u send me the sampledata files of your actual and Budget

[#3]

Hi ajay143

I attach the excel file containing both Actual & Budget data per your request. Hope you can help to solve my problem.

Many Thanks & Best Rdgs,

Steven

[#4]

Hi ajay143

Kindly advise what is the status now and can it be done ?.

Thanks & Best Rdgs,

Steven

[d_pranskus · Accepted solution]

Hi

Find attached application

Cheers

DArius

[#6]

Hi Darius

Thanks for your quick response.

Your suggestion works fine but I can't change individual data item in Measure to different data format. For example, Selling Price = $45.45 ($ sign & 2 decimals), Sales Volume = 2,500 (no decimal) and the rest with 2 decimals.

Kindly advise is there a way to improve on it ?.

Many Thanks & Best Rdgs,

Steven