excel best practices v0 04
DESCRIPTION
'TRANSCRIPT
Business Cases
Nr Topic
Report Development
" "
" "
" "
" "
Data Analysis
RD1
RD2
RD3
RD9
RD10
DA1
Exercises
Clear the contents of the unlocked cells in a protected sheet
Copy a formula to the unlocked cells of a protected sheet
Define empty cells as ‘Input Cells’ by changing their background color and unlocking them
Freeze panes in multiple sheets
Protect All Worksheets
Data Analysis using the "Anchor Mode"
Clear User Input Cells
Task
- Clear the data in the unlocked input cells without modifying the white formula cells
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
Proposed Solution
1) Protect the sheet with any password (if not already protected)
2) Select the table (or the whole worksheet) <CTRL-A>
3) MTools: Protected Worksheet Tools => Clear Contents
FYI: In a protected sheet, excel cannot selectively delete the content of unlocked cells if the selected range contains any locked cell.
Profit by Country January 2011
in Mio USD Prior Year Actual Year Delta to Prior Year
Absolute %
Asia 251,750.9 205,808.2 -45,942.7 -18.2%
Japan 71,150.9 93,233.1 22,082.2 31.0%
Hong Kong 94,141.7 63,102.4 -31,039.3 -33.0%
China 86,458.3 49,472.7 -36,985.6 -42.8%
Europe 119,021.0 286,501.5 167,480.6 140.7%
Switzerland 27,930.5 92,783.2 64,852.6 232.2%
Germany 27,052.4 44,917.8 17,865.4 66.0%
Austria 2,473.8 59,045.5 56,571.6 2286.8%
UK 16,790.7 6,606.7 -10,184.0 -60.7%
Spain 22,604.7 5,768.9 -16,835.8 -74.5%
Portugal 22,168.8 77,379.5 55,210.7 249.0%
Middle East 125,804.7 131,774.0 5,969.4 4.7%
Turkey 40,643.9 64,784.2 24,140.3 59.4%
Morocco 85,160.7 66,989.8 -18,170.9 -21.3%
The yellow cells in the table below represent the "Input Cells" that the Countries have to fill out, whereas the white cells represent formulas that calculate sums and ratios (Formula Cells). To prevent the users from destroying the formulas in the "Formula Cells", the sheet is sent our protected whereas the Input Cells will be unlocked and the formula cells locked.
Total 496,576.5 624,083.7 127,507.3 25.7%
- Clear the data in the unlocked input cells without modifying the white formula cells
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
1) Protect the sheet with any password (if not already protected)
2) Select the table (or the whole worksheet) <CTRL-A>
3) MTools: Protected Worksheet Tools => Clear Contents
FYI: In a protected sheet, excel cannot selectively delete the content of unlocked cells if the selected range contains any locked cell.
January 2011 February 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
116,586.8 89,221.3 76.5% 117,996.7 215,554.8 97,558.2 82.7% 142,375.0
28,092.8 65,140.2 231.9% 1,901.2 72,907.2 71,006.0 3734.7% 46,622.7
20,898.7 42,203.6 201.9% 93,300.2 42,916.1 -50,384.2 -54.0% 2,624.2
67,595.2 -18,122.5 -26.8% 22,795.2 99,731.6 76,936.4 337.5% 93,128.2
298,257.9 -11,756.4 -3.9% 437,108.5 447,063.1 9,954.6 2.3% 274,436.5
74,050.3 18,732.9 25.3% 92,604.5 84,897.5 -7,706.9 -8.3% 32,830.9
69,300.7 -24,382.9 -35.2% 89,535.8 99,604.9 10,069.1 11.2% 53,012.4
23,417.7 35,627.8 152.1% 52,250.3 40,715.3 -11,535.0 -22.1% 56,201.9
5,876.4 730.3 12.4% 71,544.4 86,093.8 14,549.4 20.3% 80,224.6
33,639.2 -27,870.3 -82.9% 59,123.4 48,291.0 -10,832.4 -18.3% 6,488.6
91,973.6 -14,594.1 -15.9% 72,050.1 87,460.6 15,410.4 21.4% 45,678.2
167,366.1 -35,592.1 -21.3% 114,075.9 80,522.7 -33,553.2 -29.4% 122,702.0
93,702.5 -28,918.3 -30.9% 25,350.5 71,356.1 46,005.5 181.5% 26,349.2
73,663.6 -6,673.8 -9.1% 88,725.4 9,166.6 -79,558.8 -89.7% 96,352.8
The yellow cells in the table below represent the "Input Cells" that the Countries have to fill out, whereas the white cells represent formulas that calculate sums and ratios (Formula Cells). To prevent the users from destroying the formulas in the "Formula
582,210.9 41,872.9 7.2% 669,181.1 743,140.7 73,959.5 11.1% 539,513.5
February 2011 March 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
73,179.8 51.4% 190,941.9 137,968.4 -52,973.5 -27.7% 105,853.3 32,115.2
26,284.5 56.4% 28,589.6 35,655.7 7,066.1 24.7% 16,931.5 18,724.2
40,291.9 1535.4% 99,311.9 95,620.2 -3,691.7 -3.7% 5,077.2 90,543.0
6,603.4 7.1% 63,040.5 6,692.6 -56,347.9 -89.4% 83,844.6 -77,152.1
172,626.6 62.9% 234,485.2 270,557.9 36,072.7 15.4% 254,575.9 15,982.0
52,066.7 158.6% 41,773.6 49,002.0 7,228.4 17.3% 59,342.7 -10,340.7
46,592.5 87.9% 79,343.5 69,629.1 -9,714.4 -12.2% 25,789.0 43,840.1
-15,486.6 -27.6% 53,710.9 48,784.4 -4,926.5 -9.2% 94,696.7 -45,912.3
5,869.2 7.3% 15,753.8 32,271.7 16,518.0 104.9% 37,042.9 -4,771.1
41,802.4 644.2% 38,915.0 61,937.5 23,022.5 59.2% 19,305.3 42,632.2
41,782.4 91.5% 4,988.6 8,933.3 3,944.7 79.1% 18,399.4 -9,466.2
-42,179.3 -34.4% 65,561.8 63,454.2 -2,107.6 -3.2% 84,487.2 -21,033.0
45,006.9 170.8% 13,708.5 54,657.1 40,948.7 298.7% 21,865.5 32,791.6
-87,186.2 -90.5% 51,853.4 8,797.0 -43,056.3 -83.0% 62,621.7 -53,824.6
203,627.1 37.7% 490,989.0 471,980.5 -19,008.5 -3.9% 444,916.4 27,064.1
March 2011 April 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
30.3% 168,626.4 118,666.2 -49,960.2 -29.6% 190,927.9 -72,261.7 -37.8%
110.6% 91,695.2 13,118.5 -78,576.8 -85.7% 83,708.9 -70,590.4 -84.3%
1783.3% 60,156.4 57,838.0 -2,318.4 -3.9% 26,469.8 31,368.2 118.5%
-92.0% 16,774.8 47,709.7 30,935.0 184.4% 80,749.2 -33,039.5 -40.9%
6.3% 406,448.2 297,700.0 -108,748.2 -26.8% 330,957.3 -33,257.3 -10.0%
-17.4% 66,272.4 78,537.5 12,265.1 18.5% 59,095.3 19,442.2 32.9%
170.0% 82,524.2 38,144.8 -44,379.5 -53.8% 95,982.9 -57,838.1 -60.3%
-48.5% 91,727.5 40,896.8 -50,830.7 -55.4% 69,115.4 -28,218.6 -40.8%
-12.9% 76,868.6 68,221.1 -8,647.5 -11.2% 3,370.2 64,850.9 1924.2%
220.8% 47,319.1 20,418.5 -26,900.5 -56.8% 20,085.8 332.8 1.7%
-51.4% 41,736.4 51,481.4 9,744.9 23.3% 83,307.7 -31,826.4 -38.2%
-24.9% 93,694.4 97,863.7 4,169.3 4.4% 132,348.6 -34,484.8 -26.1%
150.0% 14,464.7 19,616.9 5,152.1 35.6% 86,419.4 -66,802.5 -77.3%
-86.0% 79,229.7 78,246.9 -982.8 -1.2% 45,929.2 32,317.7 70.4%
6.1% 668,769.0 514,230.0 -154,539.0 -23.1% 654,233.8 -140,003.8 -21.4%
May 2011 June 2011
Prior Year Actual Year Delta to Prior Year Plan Delta to Plan Prior Year
Absolute % Absolute %
112,211.1 91,245.2 -20,966.0 -18.7% 177,514.7 -86,269.5 -48.6% 112,674.8
11,107.3 28,532.1 17,424.8 156.9% 77,718.7 -49,186.6 -63.3% 69,268.9
5,699.4 35,017.5 29,318.0 514.4% 96,974.0 -61,956.5 -63.9% 11,028.5
95,404.4 27,695.6 -67,708.8 -71.0% 2,821.9 24,873.6 881.4% 32,377.4
311,070.8 233,204.3 -77,866.5 -25.0% 262,002.8 -28,798.5 -11.0% 317,770.0
87,919.1 19,439.0 -68,480.1 -77.9% 35,371.5 -15,932.5 -45.0% 99,673.1
74,654.9 31,396.3 -43,258.6 -57.9% 4,825.6 26,570.7 550.6% 10,390.9
6,764.4 63,823.5 57,059.1 843.5% 13,193.6 50,629.9 383.7% 70,503.3
54,603.2 31,799.3 -22,803.8 -41.8% 95,438.4 -63,639.1 -66.7% 34,094.3
78,890.9 22,283.2 -56,607.7 -71.8% 21,430.7 852.5 4.0% 61,146.6
8,238.4 64,463.0 56,224.6 682.5% 91,743.0 -27,280.0 -29.7% 41,961.8
122,774.0 106,142.9 -16,631.1 -13.5% 96,653.7 9,489.2 9.8% 47,541.6
30,401.3 12,409.4 -17,991.9 -59.2% 29,492.5 -17,083.1 -57.9% 27,889.1
92,372.7 93,733.5 1,360.8 1.5% 67,161.2 26,572.3 39.6% 19,652.5
546,056.0 430,592.3 -115,463.6 -21.1% 536,171.2 -105,578.8 -19.7% 477,986.4
June 2011 July 2011
Actual Year Delta to Prior Year Plan Delta to Plan Prior Year Actual Year
Absolute % Absolute %
79,334.3 -33,340.6 -29.6% 127,139.1 -47,804.9 -37.6% 194,272.1 153,658.8
5,724.2 -63,544.7 -91.7% 23,907.3 -18,183.1 -76.1% 56,376.6 49,843.4
38,775.5 27,746.9 251.6% 14,984.9 23,790.5 158.8% 95,746.0 32,720.7
34,834.6 2,457.2 7.6% 88,246.9 -53,412.3 -60.5% 42,149.5 71,094.7
254,494.0 -63,276.0 -19.9% 373,892.2 -119,398.2 -31.9% 316,681.3 301,218.5
31,762.7 -67,910.3 -68.1% 93,639.8 -61,877.0 -66.1% 71,840.7 42,292.7
63,043.8 52,652.9 506.7% 57,218.1 5,825.8 10.2% 68,542.4 77,804.0
74,645.8 4,142.5 5.9% 59,782.8 14,863.0 24.9% 76,508.4 34,377.1
17,261.7 -16,832.6 -49.4% 47,780.0 -30,518.3 -63.9% 10,381.8 38,847.0
48,263.8 -12,882.8 -21.1% 68,956.9 -20,693.1 -30.0% 80,489.5 37,811.7
19,516.2 -22,445.6 -53.5% 46,514.7 -26,998.5 -58.0% 8,918.4 70,085.9
129,846.5 82,304.9 173.1% 60,039.2 69,807.2 116.3% 97,714.3 89,147.9
89,493.9 61,604.8 220.9% 42,011.7 47,482.2 113.0% 8,814.6 35,244.1
40,352.6 20,700.1 105.3% 18,027.5 22,325.1 123.8% 88,899.8 53,903.8
463,674.7 -14,311.7 -3.0% 561,070.6 -97,395.8 -17.4% 608,667.7 544,025.2
July 2011 August 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
Absolute % Absolute % Absolute
-40,613.3 -20.9% 58,805.6 94,853.1 161.3% 97,835.4 226,426.1 128,590.7
-6,533.2 -11.6% 10,143.2 39,700.2 391.4% 25,701.2 92,470.1 66,768.9
-63,025.3 -65.8% 37,174.5 -4,453.8 -12.0% 32,615.7 70,150.0 37,534.3
28,945.2 68.7% 11,488.0 59,606.7 518.9% 39,518.5 63,806.0 24,287.5
-15,462.7 -4.9% 329,603.8 -28,385.2 -8.6% 407,221.2 295,164.3 -112,056.8
-29,548.0 -41.1% 44,765.6 -2,472.9 -5.5% 32,763.2 59,853.7 27,090.6
9,261.6 13.5% 64,027.4 13,776.6 21.5% 88,133.1 44,513.1 -43,619.9
-42,131.3 -55.1% 34,665.9 -288.8 -0.8% 75,944.7 84,738.3 8,793.6
28,465.3 274.2% 74,688.7 -35,841.7 -48.0% 73,885.4 35,971.8 -37,913.6
-42,677.8 -53.0% 38,840.4 -1,028.7 -2.6% 49,319.4 229.0 -49,090.5
61,167.5 685.9% 72,615.8 -2,529.8 -3.5% 87,175.5 69,858.4 -17,317.1
-8,566.4 -8.8% 102,019.7 -12,871.8 -12.6% 99,962.9 76,718.4 -23,244.5
26,429.6 299.8% 80,663.9 -45,419.8 -56.3% 71,490.4 504.5 -70,985.9
-34,996.0 -39.4% 21,355.8 32,548.0 152.4% 28,472.5 76,213.9 47,741.3
-64,642.5 -10.6% 490,429.1 53,596.1 10.9% 605,019.5 598,308.8 -6,710.7
August 2011 September 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
% Absolute % Absolute %
131.4% 90,290.2 136,135.9 150.8% 111,974.6 76,288.4 -35,686.2 -31.9%
259.8% 17,543.5 74,926.6 427.1% 58,067.9 49,394.6 -8,673.4 -14.9%
115.1% 15,725.1 54,424.9 346.1% 6,295.3 25,056.6 18,761.2 298.0%
61.5% 57,021.6 6,784.4 11.9% 47,611.3 1,837.3 -45,774.1 -96.1%
-27.5% 238,148.1 57,016.2 23.9% 276,134.9 425,437.2 149,302.3 54.1%
82.7% 30,599.1 29,254.6 95.6% 12,100.2 65,048.9 52,948.7 437.6%
-49.5% 35,294.5 9,218.7 26.1% 57,057.1 65,994.7 8,937.5 15.7%
11.6% 48,245.4 36,492.9 75.6% 79,273.7 54,672.7 -24,601.0 -31.0%
-51.3% 39,634.0 -3,662.2 -9.2% 68,524.6 61,956.8 -6,567.8 -9.6%
-99.5% 67,039.6 -66,810.7 -99.7% 1,541.2 98,865.5 97,324.2 6314.7%
-19.9% 17,335.4 52,523.0 303.0% 57,638.0 78,898.7 21,260.7 36.9%
-23.3% 154,471.6 -77,753.2 -50.3% 146,788.8 137,510.9 -9,277.9 -6.3%
-99.3% 72,167.9 -71,663.4 -99.3% 84,069.0 85,564.7 1,495.7 1.8%
167.7% 82,303.7 -6,089.8 -7.4% 62,719.8 51,946.2 -10,773.6 -17.2%
-1.1% 482,909.9 115,398.9 23.9% 534,898.4 639,236.5 104,338.2 19.5%
September 2011 October 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
215,790.4 -139,502.0 -64.6% 99,361.6 188,387.9 89,026.3 89.6% 149,945.4
84,544.6 -35,150.0 -41.6% 30,445.5 84,716.5 54,271.0 178.3% 61,621.0
33,578.7 -8,522.1 -25.4% 26,842.6 22,356.2 -4,486.4 -16.7% 709.3
97,667.1 -95,829.8 -98.1% 42,073.5 81,315.2 39,241.6 93.3% 87,615.1
267,089.9 158,347.4 59.3% 192,675.2 227,600.9 34,925.7 18.1% 305,481.8
9,467.0 55,581.8 587.1% 25,006.8 17,247.5 -7,759.3 -31.0% 88,633.5
96,776.0 -30,781.3 -31.8% 12,720.9 8,737.6 -3,983.3 -31.3% 90,356.3
57,989.2 -3,316.5 -5.7% 53,058.5 33,290.9 -19,767.6 -37.3% 9,981.4
33,279.3 28,677.5 86.2% 8,573.4 71,282.2 62,708.8 731.4% 48,701.4
57,060.5 41,804.9 73.3% 20,807.6 83,850.1 63,042.5 303.0% 17,333.2
12,517.8 66,380.9 530.3% 72,508.0 13,192.5 -59,315.5 -81.8% 50,476.1
91,115.8 46,395.1 50.9% 53,267.3 96,001.5 42,734.2 80.2% 66,664.1
58,793.5 26,771.2 45.5% 22,412.1 84,684.9 62,272.7 277.9% 46,695.7
32,322.3 19,623.9 60.7% 30,855.1 11,316.6 -19,538.5 -63.3% 19,968.4
573,996.0 65,240.5 11.4% 345,304.1 511,990.3 166,686.1 48.3% 522,091.3
October 2011 November 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
38,442.5 25.6% 154,404.4 143,111.2 -11,293.2 -7.3% 96,737.2 46,374.0
23,095.6 37.5% 47,310.5 29,818.4 -17,492.1 -37.0% 44,202.9 -14,384.5
21,646.8 3051.7% 58,031.6 67,775.6 9,744.1 16.8% 37,582.1 30,193.5
-6,299.9 -7.2% 49,062.2 45,517.2 -3,545.1 -7.2% 14,952.2 30,564.9
-77,880.9 -25.5% 365,957.7 401,039.5 35,081.8 9.6% 237,290.9 163,748.6
-71,386.0 -80.5% 61,429.9 45,172.8 -16,257.1 -26.5% 67,090.3 -21,917.5
-81,618.6 -90.3% 23,952.8 62,992.3 39,039.5 163.0% 14,903.0 48,089.3
23,309.5 233.5% 62,052.2 68,887.2 6,835.0 11.0% 83,628.8 -14,741.6
22,580.8 46.4% 35,931.0 62,696.4 26,765.4 74.5% 39,660.7 23,035.7
66,516.9 383.8% 99,405.6 62,669.5 -36,736.0 -37.0% 29,068.5 33,601.0
-37,283.6 -73.9% 83,186.2 98,621.2 15,435.0 18.6% 2,939.5 95,681.6
29,337.4 44.0% 110,768.8 104,330.2 -6,438.6 -5.8% 181,956.7 -77,626.5
37,989.1 81.4% 92,861.9 79,584.9 -13,277.0 -14.3% 90,110.0 -10,525.1
-8,651.7 -43.3% 17,906.9 24,745.2 6,838.4 38.2% 91,846.7 -67,101.4
-10,101.0 -1.9% 631,130.8 648,480.8 17,350.0 2.7% 515,984.8 132,496.1
November 2011 December 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
47.9% 201,139.5 256,949.6 55,810.0 27.7% 136,122.6 120,826.9 88.8%
-32.5% 40,764.6 96,410.3 55,645.7 136.5% 77,485.3 18,925.0 24.4%
80.3% 70,061.0 66,212.5 -3,848.5 -5.5% 4,045.8 62,166.8 1536.6%
204.4% 90,314.0 94,326.8 4,012.9 4.4% 54,591.6 39,735.2 72.8%
69.0% 315,176.7 362,068.3 46,891.7 14.9% 249,231.2 112,837.2 45.3%
-32.7% 94,461.1 4,790.4 -89,670.7 -94.9% 65,218.2 -60,427.8 -92.7%
322.7% 23,464.0 67,596.2 44,132.2 188.1% 29,407.5 38,188.6 129.9%
-17.6% 60,725.8 83,010.0 22,284.2 36.7% 32,325.2 50,684.8 156.8%
58.1% 9,206.7 62,696.3 53,489.5 581.0% 87,148.1 -24,451.8 -28.1%
115.6% 40,379.4 61,120.2 20,740.9 51.4% 2,394.0 58,726.2 2453.0%
3255.0% 86,939.7 82,855.3 -4,084.5 -4.7% 32,738.1 50,117.1 153.1%
-42.7% 60,054.1 118,186.6 58,132.5 96.8% 94,690.0 23,496.6 24.8%
-11.7% 56,184.0 42,689.6 -13,494.4 -24.0% 42,208.7 480.9 1.1%
-73.1% 3,870.1 75,497.0 71,626.9 1850.8% 52,481.4 23,015.7 43.9%
25.7% 576,370.3 737,204.5 160,834.2 27.9% 480,043.9 257,160.7 53.6%
Copy the Lookup Formula in all Input Cells
Task
- Copy the formula in the cell D35 in all Input cells (yellow cells) to Lookup the BW Data in sheet "Data_MIS"
(Try it first in your own way and then try the proposed Solution using MTools)
Proposed Solution
1) Protect the sheet with any password (if not already protected)
2) Select the green Cell "D35"
3) MTools: Protected Worksheet Tools => Copy To...
Column Lookup January 2011Pri January 2011Act
Row Lookup January 2011
in Mio USD Prior Year Actual Year Delta to Prior Year
Absolute
Asia 61,989.5 - -61,989.5
JAP Japan 61,989.5 -61,989.5
HK Hong Kong -
CHIN China -
Europe - - -
CH Switzerland -
DE Germany -
AT Austria -
UK UK -
A central team has to fill out the report below based on the data in the Business Warehouse. A download of that data has been saved in the sheet "Data_MIS". The green cell "D35" contains the template of a Lookup Formula that needs to be copied in all yellow cells without destroying the content of the white cells.
SP Spain -
PT Portugal -
Middle East - - -
TK Turkey -
MO Morocco -
Total 61,989.5 - -61,989.5
- Copy the formula in the cell D35 in all Input cells (yellow cells) to Lookup the BW Data in sheet "Data_MIS"
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
1) Protect the sheet with any password (if not already protected)
2) Select the green Cell "D35"
3) MTools: Protected Worksheet Tools => Copy To...
January 2011Pl February 2011PrFebruary 2011Ac
January 2011 February 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
% Absolute % Absolute %
-100.0% - - #DIV/0! - - - #DIV/0!
-100.0% - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
A central team has to fill out the report below based on the data in the Business Warehouse. A download of that data has been saved in the sheet "Data_MIS". The green cell "D35" contains the template of a Lookup Formula that needs to be copied in all yellow cells without destroying the content of the white cells.
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
-100.0% - - #DIV/0! - - - #DIV/0!
- Copy the formula in the cell D35 in all Input cells (yellow cells) to Lookup the BW Data in sheet "Data_MIS"
(Try it first in your own way and then try the proposed Solution using MTools)
1) Protect the sheet with any password (if not already protected)
2) Select the green Cell "D35"
3) MTools: Protected Worksheet Tools => Copy To...
February 2011P March 2011PriorMarch 2011Actu March 2011Plan
February 2011 March 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
April 2011Prior April 2011Actual April 2011Plan
March 2011 April 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
May 2011Prior YMay 2011Actual May 2011Plan
April 2011 May 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
June 2011Prior June 2011Actual June 2011Plan July 2011Prior Y
June 2011 July 2011
Prior Year Actual Year Delta to Prior Year Plan Delta to Plan Prior Year
Absolute % Absolute %
- - - #DIV/0! - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - - #DIV/0! - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - - #DIV/0! - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - - #DIV/0! - - #DIV/0! -
July 2011Actual July 2011Plan August 2011PrioAugust 2011Actu
July 2011 August 2011
Actual Year Delta to Prior Year Plan Delta to Plan Prior Year Actual Year
Absolute % Absolute %
- - #DIV/0! - - #DIV/0! - -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - #DIV/0! - -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - #DIV/0! - -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - #DIV/0! - -
August 2011Pla September 2011PSeptember 2011
August 2011 September 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
Absolute % Absolute % Absolute
- #DIV/0! - - #DIV/0! - - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - #DIV/0! - - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - #DIV/0! - - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - #DIV/0! - - -
September 2011 October 2011Pri October 2011Act
September 2011 October 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
% Absolute % Absolute %
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
October 2011Pl November 2011PNovember 2011A November 2011
October 2011 November 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
December 2011PDecember 2011A December 2011
November 2011 December 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
December 2011
Delta to Plan
%
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Define Empty Cells as Input Cells
Task
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
Proposed Solution
2) Select the table (C34:CI48)
3) MTools: Report Tools => Lock/Unlock Cells...
4) MTools: Report Tools => Mark Unlocked Cells... => Select Yellow as background color for all unlocked cells in the selected range
Profit by Country January 2011
in Mio USD Prior Year Actual Year Delta to Prior Year
Absolute %
Asia - - - #DIV/0!
Japan - #DIV/0!
Hong Kong - #DIV/0!
China - #DIV/0!
Europe - - - #DIV/0!
Switzerland - #DIV/0!
Germany - #DIV/0!
Austria - #DIV/0!
UK - #DIV/0!
Spain - #DIV/0!
Portugal - #DIV/0!
Middle East - - - #DIV/0!
Turkey - #DIV/0!
Morocco - #DIV/0!
The report below needs to be improved. The empty cells in the report below (e.g. D35) are the Input Cells that the User has to fill out later. Therefore these cells should be e.g. yellow to make it more obvious for the user what he has to fill out. Furthermore these input cells should be unlocked so that the user can fill them out after we protected the sheet.
- Make all empty cells in the report yellow and unlock them (Currently, all cells are locked)
1) Make sure that the sheet is unprotected and all cells in the report locked (this is already the case)
Total - - - #DIV/0!
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
2) Select the table (C34:CI48)
3) MTools: Report Tools => Lock/Unlock Cells...
4) MTools: Report Tools => Mark Unlocked Cells... => Select Yellow as background color for all unlocked cells in the selected range
January 2011 February 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
The report below needs to be improved. The empty cells in the report below (e.g. D35) are the Input Cells that the User has to fill out later. Therefore these cells should be e.g. yellow to make it more obvious for the user what he has to fill out. Furthermore
- - #DIV/0! - - - #DIV/0! -
February 2011 March 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
March 2011 April 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
May 2011 June 2011
Prior Year Actual Year Delta to Prior Year Plan Delta to Plan Prior Year
Absolute % Absolute %
- - - #DIV/0! - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - - #DIV/0! - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - - #DIV/0! - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - - #DIV/0! - - #DIV/0! -
June 2011 July 2011
Actual Year Delta to Prior Year Plan Delta to Plan Prior Year Actual Year
Absolute % Absolute %
- - #DIV/0! - - #DIV/0! - -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - #DIV/0! - -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - #DIV/0! - -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - #DIV/0! - -
July 2011 August 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
Absolute % Absolute % Absolute
- #DIV/0! - - #DIV/0! - - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - #DIV/0! - - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - #DIV/0! - - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - #DIV/0! - - -
August 2011 September 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
% Absolute % Absolute %
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - #DIV/0! - - - #DIV/0!
September 2011 October 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
- #DIV/0! - #DIV/0!
- #DIV/0! - #DIV/0!
- - #DIV/0! - - - #DIV/0! -
October 2011 November 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
- #DIV/0! - #DIV/0! -
- #DIV/0! - #DIV/0! -
- #DIV/0! - - - #DIV/0! - -
November 2011 December 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - #DIV/0! - #DIV/0!
#DIV/0! - - - #DIV/0! - - #DIV/0!
Freeze Pane in multiple sheets at once
The Reports in the Sheets "RD1" - "RD9" show different line items of the Income statement. Make them more user friendly by using the function "Freeze Panes" in cell D34.
Task
- Freeze panes in cell D34 in the sheets "RD1" - "RD9"
(Try it first in your own way and then try the proposed Solution using MTools)
Proposed Solution
1) Select the Sheets "RD1" - "RD3" (<CTRL> => Click on RD1 => Click on RD3)
2) MTools: Readability Improvements => Freeze Panes
FYI: The appropriate Excel function works only on a single sheet. However, the corresponding MTools function freezes the Panes in all selected sheets.
Profit by Country January 2011
in Mio USD Prior Year Actual Year Delta to Prior Year
Absolute %
Asia 251,750.9 205,808.2 -45,942.7 -18.2%
Japan 71,150.9 93,233.1 22,082.2 31.0%
Hong Kong 94,141.7 63,102.4 -31,039.3 -33.0%
China 86,458.3 49,472.7 -36,985.6 -42.8%
Europe 119,021.0 286,501.5 167,480.6 140.7%
Switzerland 27,930.5 92,783.2 64,852.6 232.2%
Germany 27,052.4 44,917.8 17,865.4 66.0%
Austria 2,473.8 59,045.5 56,571.6 2286.8%
UK 16,790.7 6,606.7 -10,184.0 -60.7%
Spain 22,604.7 5,768.9 -16,835.8 -74.5%
Portugal 22,168.8 77,379.5 55,210.7 249.0%
Middle East 125,804.7 131,774.0 5,969.4 4.7%
Turkey 40,643.9 64,784.2 24,140.3 59.4%
Morocco 85,160.7 66,989.8 -18,170.9 -21.3%
Total 496,576.5 624,083.7 127,507.3 25.7%
The Reports in the Sheets "RD1" - "RD9" show different line items of the Income statement. Make them more user friendly by using the function "Freeze Panes" in cell D34.
- Freeze panes in cell D34 in the sheets "RD1" - "RD9"
(Try it first in your own way and then try the proposed Solution using MTools)
1) Select the Sheets "RD1" - "RD3" (<CTRL> => Click on RD1 => Click on RD3)
2) MTools: Readability Improvements => Freeze Panes
FYI: The appropriate Excel function works only on a single sheet. However, the corresponding MTools function freezes the Panes in all selected sheets.
January 2011 February 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
116,586.8 89,221.3 76.5% 117,996.7 215,554.8 97,558.2 82.7% 142,375.0
28,092.8 65,140.2 231.9% 1,901.2 72,907.2 71,006.0 3734.7% 46,622.7
20,898.7 42,203.6 201.9% 93,300.2 42,916.1 -50,384.2 -54.0% 2,624.2
67,595.2 -18,122.5 -26.8% 22,795.2 99,731.6 76,936.4 337.5% 93,128.2
298,257.9 -11,756.4 -3.9% 437,108.5 447,063.1 9,954.6 2.3% 274,436.5
74,050.3 18,732.9 25.3% 92,604.5 84,897.5 -7,706.9 -8.3% 32,830.9
69,300.7 -24,382.9 -35.2% 89,535.8 99,604.9 10,069.1 11.2% 53,012.4
23,417.7 35,627.8 152.1% 52,250.3 40,715.3 -11,535.0 -22.1% 56,201.9
5,876.4 730.3 12.4% 71,544.4 86,093.8 14,549.4 20.3% 80,224.6
33,639.2 -27,870.3 -82.9% 59,123.4 48,291.0 -10,832.4 -18.3% 6,488.6
91,973.6 -14,594.1 -15.9% 72,050.1 87,460.6 15,410.4 21.4% 45,678.2
167,366.1 -35,592.1 -21.3% 114,075.9 80,522.7 -33,553.2 -29.4% 122,702.0
93,702.5 -28,918.3 -30.9% 25,350.5 71,356.1 46,005.5 181.5% 26,349.2
73,663.6 -6,673.8 -9.1% 88,725.4 9,166.6 -79,558.8 -89.7% 96,352.8
582,210.9 41,872.9 7.2% 669,181.1 743,140.7 73,959.5 11.1% 539,513.5
February 2011 March 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
73,179.8 51.4% 190,941.9 137,968.4 -52,973.5 -27.7% 105,853.3 32,115.2
26,284.5 56.4% 28,589.6 35,655.7 7,066.1 24.7% 16,931.5 18,724.2
40,291.9 1535.4% 99,311.9 95,620.2 -3,691.7 -3.7% 5,077.2 90,543.0
6,603.4 7.1% 63,040.5 6,692.6 -56,347.9 -89.4% 83,844.6 -77,152.1
172,626.6 62.9% 234,485.2 270,557.9 36,072.7 15.4% 254,575.9 15,982.0
52,066.7 158.6% 41,773.6 49,002.0 7,228.4 17.3% 59,342.7 -10,340.7
46,592.5 87.9% 79,343.5 69,629.1 -9,714.4 -12.2% 25,789.0 43,840.1
-15,486.6 -27.6% 53,710.9 48,784.4 -4,926.5 -9.2% 94,696.7 -45,912.3
5,869.2 7.3% 15,753.8 32,271.7 16,518.0 104.9% 37,042.9 -4,771.1
41,802.4 644.2% 38,915.0 61,937.5 23,022.5 59.2% 19,305.3 42,632.2
41,782.4 91.5% 4,988.6 8,933.3 3,944.7 79.1% 18,399.4 -9,466.2
-42,179.3 -34.4% 65,561.8 63,454.2 -2,107.6 -3.2% 84,487.2 -21,033.0
45,006.9 170.8% 13,708.5 54,657.1 40,948.7 298.7% 21,865.5 32,791.6
-87,186.2 -90.5% 51,853.4 8,797.0 -43,056.3 -83.0% 62,621.7 -53,824.6
203,627.1 37.7% 490,989.0 471,980.5 -19,008.5 -3.9% 444,916.4 27,064.1
March 2011 April 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
30.3% 168,626.4 118,666.2 -49,960.2 -29.6% 190,927.9 -72,261.7 -37.8%
110.6% 91,695.2 13,118.5 -78,576.8 -85.7% 83,708.9 -70,590.4 -84.3%
1783.3% 60,156.4 57,838.0 -2,318.4 -3.9% 26,469.8 31,368.2 118.5%
-92.0% 16,774.8 47,709.7 30,935.0 184.4% 80,749.2 -33,039.5 -40.9%
6.3% 406,448.2 297,700.0 -108,748.2 -26.8% 330,957.3 -33,257.3 -10.0%
-17.4% 66,272.4 78,537.5 12,265.1 18.5% 59,095.3 19,442.2 32.9%
170.0% 82,524.2 38,144.8 -44,379.5 -53.8% 95,982.9 -57,838.1 -60.3%
-48.5% 91,727.5 40,896.8 -50,830.7 -55.4% 69,115.4 -28,218.6 -40.8%
-12.9% 76,868.6 68,221.1 -8,647.5 -11.2% 3,370.2 64,850.9 1924.2%
220.8% 47,319.1 20,418.5 -26,900.5 -56.8% 20,085.8 332.8 1.7%
-51.4% 41,736.4 51,481.4 9,744.9 23.3% 83,307.7 -31,826.4 -38.2%
-24.9% 93,694.4 97,863.7 4,169.3 4.4% 132,348.6 -34,484.8 -26.1%
150.0% 14,464.7 19,616.9 5,152.1 35.6% 86,419.4 -66,802.5 -77.3%
-86.0% 79,229.7 78,246.9 -982.8 -1.2% 45,929.2 32,317.7 70.4%
6.1% 668,769.0 514,230.0 -154,539.0 -23.1% 654,233.8 -140,003.8 -21.4%
May 2011 June 2011
Prior Year Actual Year Delta to Prior Year Plan Delta to Plan Prior Year
Absolute % Absolute %
112,211.1 91,245.2 -20,966.0 -18.7% 177,514.7 -86,269.5 -48.6% 112,674.8
11,107.3 28,532.1 17,424.8 156.9% 77,718.7 -49,186.6 -63.3% 69,268.9
5,699.4 35,017.5 29,318.0 514.4% 96,974.0 -61,956.5 -63.9% 11,028.5
95,404.4 27,695.6 -67,708.8 -71.0% 2,821.9 24,873.6 881.4% 32,377.4
311,070.8 233,204.3 -77,866.5 -25.0% 262,002.8 -28,798.5 -11.0% 317,770.0
87,919.1 19,439.0 -68,480.1 -77.9% 35,371.5 -15,932.5 -45.0% 99,673.1
74,654.9 31,396.3 -43,258.6 -57.9% 4,825.6 26,570.7 550.6% 10,390.9
6,764.4 63,823.5 57,059.1 843.5% 13,193.6 50,629.9 383.7% 70,503.3
54,603.2 31,799.3 -22,803.8 -41.8% 95,438.4 -63,639.1 -66.7% 34,094.3
78,890.9 22,283.2 -56,607.7 -71.8% 21,430.7 852.5 4.0% 61,146.6
8,238.4 64,463.0 56,224.6 682.5% 91,743.0 -27,280.0 -29.7% 41,961.8
122,774.0 106,142.9 -16,631.1 -13.5% 96,653.7 9,489.2 9.8% 47,541.6
30,401.3 12,409.4 -17,991.9 -59.2% 29,492.5 -17,083.1 -57.9% 27,889.1
92,372.7 93,733.5 1,360.8 1.5% 67,161.2 26,572.3 39.6% 19,652.5
546,056.0 430,592.3 -115,463.6 -21.1% 536,171.2 -105,578.8 -19.7% 477,986.4
June 2011 July 2011
Actual Year Delta to Prior Year Plan Delta to Plan Prior Year Actual Year
Absolute % Absolute %
79,334.3 -33,340.6 -29.6% 127,139.1 -47,804.9 -37.6% 194,272.1 153,658.8
5,724.2 -63,544.7 -91.7% 23,907.3 -18,183.1 -76.1% 56,376.6 49,843.4
38,775.5 27,746.9 251.6% 14,984.9 23,790.5 158.8% 95,746.0 32,720.7
34,834.6 2,457.2 7.6% 88,246.9 -53,412.3 -60.5% 42,149.5 71,094.7
254,494.0 -63,276.0 -19.9% 373,892.2 -119,398.2 -31.9% 316,681.3 301,218.5
31,762.7 -67,910.3 -68.1% 93,639.8 -61,877.0 -66.1% 71,840.7 42,292.7
63,043.8 52,652.9 506.7% 57,218.1 5,825.8 10.2% 68,542.4 77,804.0
74,645.8 4,142.5 5.9% 59,782.8 14,863.0 24.9% 76,508.4 34,377.1
17,261.7 -16,832.6 -49.4% 47,780.0 -30,518.3 -63.9% 10,381.8 38,847.0
48,263.8 -12,882.8 -21.1% 68,956.9 -20,693.1 -30.0% 80,489.5 37,811.7
19,516.2 -22,445.6 -53.5% 46,514.7 -26,998.5 -58.0% 8,918.4 70,085.9
129,846.5 82,304.9 173.1% 60,039.2 69,807.2 116.3% 97,714.3 89,147.9
89,493.9 61,604.8 220.9% 42,011.7 47,482.2 113.0% 8,814.6 35,244.1
40,352.6 20,700.1 105.3% 18,027.5 22,325.1 123.8% 88,899.8 53,903.8
463,674.7 -14,311.7 -3.0% 561,070.6 -97,395.8 -17.4% 608,667.7 544,025.2
July 2011 August 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
Absolute % Absolute % Absolute
-40,613.3 -20.9% 58,805.6 94,853.1 161.3% 97,835.4 226,426.1 128,590.7
-6,533.2 -11.6% 10,143.2 39,700.2 391.4% 25,701.2 92,470.1 66,768.9
-63,025.3 -65.8% 37,174.5 -4,453.8 -12.0% 32,615.7 70,150.0 37,534.3
28,945.2 68.7% 11,488.0 59,606.7 518.9% 39,518.5 63,806.0 24,287.5
-15,462.7 -4.9% 329,603.8 -28,385.2 -8.6% 407,221.2 295,164.3 -112,056.8
-29,548.0 -41.1% 44,765.6 -2,472.9 -5.5% 32,763.2 59,853.7 27,090.6
9,261.6 13.5% 64,027.4 13,776.6 21.5% 88,133.1 44,513.1 -43,619.9
-42,131.3 -55.1% 34,665.9 -288.8 -0.8% 75,944.7 84,738.3 8,793.6
28,465.3 274.2% 74,688.7 -35,841.7 -48.0% 73,885.4 35,971.8 -37,913.6
-42,677.8 -53.0% 38,840.4 -1,028.7 -2.6% 49,319.4 229.0 -49,090.5
61,167.5 685.9% 72,615.8 -2,529.8 -3.5% 87,175.5 69,858.4 -17,317.1
-8,566.4 -8.8% 102,019.7 -12,871.8 -12.6% 99,962.9 76,718.4 -23,244.5
26,429.6 299.8% 80,663.9 -45,419.8 -56.3% 71,490.4 504.5 -70,985.9
-34,996.0 -39.4% 21,355.8 32,548.0 152.4% 28,472.5 76,213.9 47,741.3
-64,642.5 -10.6% 490,429.1 53,596.1 10.9% 605,019.5 598,308.8 -6,710.7
August 2011 September 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
% Absolute % Absolute %
131.4% 90,290.2 136,135.9 150.8% 111,974.6 76,288.4 -35,686.2 -31.9%
259.8% 17,543.5 74,926.6 427.1% 58,067.9 49,394.6 -8,673.4 -14.9%
115.1% 15,725.1 54,424.9 346.1% 6,295.3 25,056.6 18,761.2 298.0%
61.5% 57,021.6 6,784.4 11.9% 47,611.3 1,837.3 -45,774.1 -96.1%
-27.5% 238,148.1 57,016.2 23.9% 276,134.9 425,437.2 149,302.3 54.1%
82.7% 30,599.1 29,254.6 95.6% 12,100.2 65,048.9 52,948.7 437.6%
-49.5% 35,294.5 9,218.7 26.1% 57,057.1 65,994.7 8,937.5 15.7%
11.6% 48,245.4 36,492.9 75.6% 79,273.7 54,672.7 -24,601.0 -31.0%
-51.3% 39,634.0 -3,662.2 -9.2% 68,524.6 61,956.8 -6,567.8 -9.6%
-99.5% 67,039.6 -66,810.7 -99.7% 1,541.2 98,865.5 97,324.2 6314.7%
-19.9% 17,335.4 52,523.0 303.0% 57,638.0 78,898.7 21,260.7 36.9%
-23.3% 154,471.6 -77,753.2 -50.3% 146,788.8 137,510.9 -9,277.9 -6.3%
-99.3% 72,167.9 -71,663.4 -99.3% 84,069.0 85,564.7 1,495.7 1.8%
167.7% 82,303.7 -6,089.8 -7.4% 62,719.8 51,946.2 -10,773.6 -17.2%
-1.1% 482,909.9 115,398.9 23.9% 534,898.4 639,236.5 104,338.2 19.5%
September 2011 October 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
215,790.4 -139,502.0 -64.6% 99,361.6 188,387.9 89,026.3 89.6% 149,945.4
84,544.6 -35,150.0 -41.6% 30,445.5 84,716.5 54,271.0 178.3% 61,621.0
33,578.7 -8,522.1 -25.4% 26,842.6 22,356.2 -4,486.4 -16.7% 709.3
97,667.1 -95,829.8 -98.1% 42,073.5 81,315.2 39,241.6 93.3% 87,615.1
267,089.9 158,347.4 59.3% 192,675.2 227,600.9 34,925.7 18.1% 305,481.8
9,467.0 55,581.8 587.1% 25,006.8 17,247.5 -7,759.3 -31.0% 88,633.5
96,776.0 -30,781.3 -31.8% 12,720.9 8,737.6 -3,983.3 -31.3% 90,356.3
57,989.2 -3,316.5 -5.7% 53,058.5 33,290.9 -19,767.6 -37.3% 9,981.4
33,279.3 28,677.5 86.2% 8,573.4 71,282.2 62,708.8 731.4% 48,701.4
57,060.5 41,804.9 73.3% 20,807.6 83,850.1 63,042.5 303.0% 17,333.2
12,517.8 66,380.9 530.3% 72,508.0 13,192.5 -59,315.5 -81.8% 50,476.1
91,115.8 46,395.1 50.9% 53,267.3 96,001.5 42,734.2 80.2% 66,664.1
58,793.5 26,771.2 45.5% 22,412.1 84,684.9 62,272.7 277.9% 46,695.7
32,322.3 19,623.9 60.7% 30,855.1 11,316.6 -19,538.5 -63.3% 19,968.4
573,996.0 65,240.5 11.4% 345,304.1 511,990.3 166,686.1 48.3% 522,091.3
October 2011 November 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
38,442.5 25.6% 154,404.4 143,111.2 -11,293.2 -7.3% 96,737.2 46,374.0
23,095.6 37.5% 47,310.5 29,818.4 -17,492.1 -37.0% 44,202.9 -14,384.5
21,646.8 3051.7% 58,031.6 67,775.6 9,744.1 16.8% 37,582.1 30,193.5
-6,299.9 -7.2% 49,062.2 45,517.2 -3,545.1 -7.2% 14,952.2 30,564.9
-77,880.9 -25.5% 365,957.7 401,039.5 35,081.8 9.6% 237,290.9 163,748.6
-71,386.0 -80.5% 61,429.9 45,172.8 -16,257.1 -26.5% 67,090.3 -21,917.5
-81,618.6 -90.3% 23,952.8 62,992.3 39,039.5 163.0% 14,903.0 48,089.3
23,309.5 233.5% 62,052.2 68,887.2 6,835.0 11.0% 83,628.8 -14,741.6
22,580.8 46.4% 35,931.0 62,696.4 26,765.4 74.5% 39,660.7 23,035.7
66,516.9 383.8% 99,405.6 62,669.5 -36,736.0 -37.0% 29,068.5 33,601.0
-37,283.6 -73.9% 83,186.2 98,621.2 15,435.0 18.6% 2,939.5 95,681.6
29,337.4 44.0% 110,768.8 104,330.2 -6,438.6 -5.8% 181,956.7 -77,626.5
37,989.1 81.4% 92,861.9 79,584.9 -13,277.0 -14.3% 90,110.0 -10,525.1
-8,651.7 -43.3% 17,906.9 24,745.2 6,838.4 38.2% 91,846.7 -67,101.4
-10,101.0 -1.9% 631,130.8 648,480.8 17,350.0 2.7% 515,984.8 132,496.1
November 2011 December 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
47.9% 201,139.5 256,949.6 55,810.0 27.7% 136,122.6 120,826.9 88.8%
-32.5% 40,764.6 96,410.3 55,645.7 136.5% 77,485.3 18,925.0 24.4%
80.3% 70,061.0 66,212.5 -3,848.5 -5.5% 4,045.8 62,166.8 1536.6%
204.4% 90,314.0 94,326.8 4,012.9 4.4% 54,591.6 39,735.2 72.8%
69.0% 315,176.7 362,068.3 46,891.7 14.9% 249,231.2 112,837.2 45.3%
-32.7% 94,461.1 4,790.4 -89,670.7 -94.9% 65,218.2 -60,427.8 -92.7%
322.7% 23,464.0 67,596.2 44,132.2 188.1% 29,407.5 38,188.6 129.9%
-17.6% 60,725.8 83,010.0 22,284.2 36.7% 32,325.2 50,684.8 156.8%
58.1% 9,206.7 62,696.3 53,489.5 581.0% 87,148.1 -24,451.8 -28.1%
115.6% 40,379.4 61,120.2 20,740.9 51.4% 2,394.0 58,726.2 2453.0%
3255.0% 86,939.7 82,855.3 -4,084.5 -4.7% 32,738.1 50,117.1 153.1%
-42.7% 60,054.1 118,186.6 58,132.5 96.8% 94,690.0 23,496.6 24.8%
-11.7% 56,184.0 42,689.6 -13,494.4 -24.0% 42,208.7 480.9 1.1%
-73.1% 3,870.1 75,497.0 71,626.9 1850.8% 52,481.4 23,015.7 43.9%
25.7% 576,370.3 737,204.5 160,834.2 27.9% 480,043.9 257,160.7 53.6%
Protect All Worksheets
Before sending the report to the users, you have to protect all sheets to make sure that they can only access the unlocked input cells.
Task
- Protect All Sheets in this workbook
(Try it first in your own way and then try the proposed Solution using MTools)
Proposed Solution
=> You will get an error message because the sheets are already protected...
=> Unprotect them and try it again
FYI: The appropriate Excel function can only protect one sheet at once and not all sheets at the same time.
You have also the alternative to only protect the SELECTED sheets: MTools: => Protect or Hide => Protect Selected Worksheets
1) MTools: Protect or Hide => Protect All Worksheets
Before sending the report to the users, you have to protect all sheets to make sure that they can only access the unlocked input cells.
- Protect All Sheets in this workbook
(Try it first in your own way and then try the proposed Solution using MTools)
=> You will get an error message because the sheets are already protected...
=> Unprotect them and try it again
FYI: The appropriate Excel function can only protect one sheet at once and not all sheets at the same time.
You have also the alternative to only protect the SELECTED sheets: MTools: => Protect or Hide => Protect Selected Worksheets
Data Analysis
Task
- Activate the Anchor mode, select the cell H42 and switsch beteween the sheets RD1 to RD9
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
Proposed Solution
1) MTools: Readability Improvements => Enable Anchor Mode
2) Select the cell H42
3) Switch through the sheets RD1 to RD9
4) MTools: Readability Improvements => Disable Anchor Mode
Profit by Country January 2011
in Mio USD Prior Year Actual Year Delta to Prior Year
Absolute %
Asia 251,750.9 205,808.2 -45,942.7 -18.2%
Japan 71,150.9 93,233.1 22,082.2 31.0%
Hong Kong 94,141.7 63,102.4 -31,039.3 -33.0%
China 86,458.3 49,472.7 -36,985.6 -42.8%
Europe 119,021.0 286,501.5 167,480.6 140.7%
Switzerland 27,930.5 92,783.2 64,852.6 232.2%
Germany 27,052.4 44,917.8 17,865.4 66.0%
Austria 2,473.8 59,045.5 56,571.6 2286.8%
UK 16,790.7 6,606.7 -10,184.0 -60.7%
Spain 22,604.7 5,768.9 -16,835.8 -74.5%
Portugal 22,168.8 77,379.5 55,210.7 249.0%
Middle East 125,804.7 131,774.0 5,969.4 4.7%
Turkey 40,643.9 64,784.2 24,140.3 59.4%
Morocco 85,160.7 66,989.8 -18,170.9 -21.3%
Total 496,576.5 624,083.7 127,507.3 25.7%
Many Excel reports consist of multiple worksheets showing the e.g. the same balance, but in each sheet for a different country. If you would like to quicky compare a specific balance sheet item country by country, then you can select that cell and activate the MTools "Anchor mode". In that mode, the following will happen: If you select e.g. cell H42 and change the sheet, then Excel will jump in the new sheet to the same cell, center it and highlight it orange (in unprotected sheets).
- Activate the Anchor mode, select the cell H42 and switsch beteween the sheets RD1 to RD9
(Try it first in your own way and then try the proposed Solution using MTools)
: Input Cells
: Formula Cells
1) MTools: Readability Improvements => Enable Anchor Mode
2) Select the cell H42
3) Switch through the sheets RD1 to RD9
4) MTools: Readability Improvements => Disable Anchor Mode
January 2011 February 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
116,586.8 89,221.3 76.5% 117,996.7 215,554.8 97,558.2 82.7% 142,375.0
28,092.8 65,140.2 231.9% 1,901.2 72,907.2 71,006.0 3734.7% 46,622.7
20,898.7 42,203.6 201.9% 93,300.2 42,916.1 -50,384.2 -54.0% 2,624.2
67,595.2 -18,122.5 -26.8% 22,795.2 99,731.6 76,936.4 337.5% 93,128.2
298,257.9 -11,756.4 -3.9% 437,108.5 447,063.1 9,954.6 2.3% 274,436.5
74,050.3 18,732.9 25.3% 92,604.5 84,897.5 -7,706.9 -8.3% 32,830.9
69,300.7 -24,382.9 -35.2% 89,535.8 99,604.9 10,069.1 11.2% 53,012.4
23,417.7 35,627.8 152.1% 52,250.3 40,715.3 -11,535.0 -22.1% 56,201.9
5,876.4 730.3 12.4% 71,544.4 86,093.8 14,549.4 20.3% 80,224.6
33,639.2 -27,870.3 -82.9% 59,123.4 48,291.0 -10,832.4 -18.3% 6,488.6
91,973.6 -14,594.1 -15.9% 72,050.1 87,460.6 15,410.4 21.4% 45,678.2
167,366.1 -35,592.1 -21.3% 114,075.9 80,522.7 -33,553.2 -29.4% 122,702.0
93,702.5 -28,918.3 -30.9% 25,350.5 71,356.1 46,005.5 181.5% 26,349.2
73,663.6 -6,673.8 -9.1% 88,725.4 9,166.6 -79,558.8 -89.7% 96,352.8
582,210.9 41,872.9 7.2% 669,181.1 743,140.7 73,959.5 11.1% 539,513.5
Many Excel reports consist of multiple worksheets showing the e.g. the same balance, but in each sheet for a different country. If you would like to quicky compare a specific balance sheet item country by country, then you can select that cell and activate the MTools "Anchor mode". In that mode, the following will happen: If you select e.g. cell H42 and change the sheet, then Excel will jump in the new sheet to the same cell, center it and highlight it orange (in unprotected sheets).
February 2011 March 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
73,179.8 51.4% 190,941.9 137,968.4 -52,973.5 -27.7% 105,853.3 32,115.2
26,284.5 56.4% 28,589.6 35,655.7 7,066.1 24.7% 16,931.5 18,724.2
40,291.9 1535.4% 99,311.9 95,620.2 -3,691.7 -3.7% 5,077.2 90,543.0
6,603.4 7.1% 63,040.5 6,692.6 -56,347.9 -89.4% 83,844.6 -77,152.1
172,626.6 62.9% 234,485.2 270,557.9 36,072.7 15.4% 254,575.9 15,982.0
52,066.7 158.6% 41,773.6 49,002.0 7,228.4 17.3% 59,342.7 -10,340.7
46,592.5 87.9% 79,343.5 69,629.1 -9,714.4 -12.2% 25,789.0 43,840.1
-15,486.6 -27.6% 53,710.9 48,784.4 -4,926.5 -9.2% 94,696.7 -45,912.3
5,869.2 7.3% 15,753.8 32,271.7 16,518.0 104.9% 37,042.9 -4,771.1
41,802.4 644.2% 38,915.0 61,937.5 23,022.5 59.2% 19,305.3 42,632.2
41,782.4 91.5% 4,988.6 8,933.3 3,944.7 79.1% 18,399.4 -9,466.2
-42,179.3 -34.4% 65,561.8 63,454.2 -2,107.6 -3.2% 84,487.2 -21,033.0
45,006.9 170.8% 13,708.5 54,657.1 40,948.7 298.7% 21,865.5 32,791.6
-87,186.2 -90.5% 51,853.4 8,797.0 -43,056.3 -83.0% 62,621.7 -53,824.6
203,627.1 37.7% 490,989.0 471,980.5 -19,008.5 -3.9% 444,916.4 27,064.1
March 2011 April 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
30.3% 168,626.4 118,666.2 -49,960.2 -29.6% 190,927.9 -72,261.7 -37.8%
110.6% 91,695.2 13,118.5 -78,576.8 -85.7% 83,708.9 -70,590.4 -84.3%
1783.3% 60,156.4 57,838.0 -2,318.4 -3.9% 26,469.8 31,368.2 118.5%
-92.0% 16,774.8 47,709.7 30,935.0 184.4% 80,749.2 -33,039.5 -40.9%
6.3% 406,448.2 297,700.0 -108,748.2 -26.8% 330,957.3 -33,257.3 -10.0%
-17.4% 66,272.4 78,537.5 12,265.1 18.5% 59,095.3 19,442.2 32.9%
170.0% 82,524.2 38,144.8 -44,379.5 -53.8% 95,982.9 -57,838.1 -60.3%
-48.5% 91,727.5 40,896.8 -50,830.7 -55.4% 69,115.4 -28,218.6 -40.8%
-12.9% 76,868.6 68,221.1 -8,647.5 -11.2% 3,370.2 64,850.9 1924.2%
220.8% 47,319.1 20,418.5 -26,900.5 -56.8% 20,085.8 332.8 1.7%
-51.4% 41,736.4 51,481.4 9,744.9 23.3% 83,307.7 -31,826.4 -38.2%
-24.9% 93,694.4 97,863.7 4,169.3 4.4% 132,348.6 -34,484.8 -26.1%
150.0% 14,464.7 19,616.9 5,152.1 35.6% 86,419.4 -66,802.5 -77.3%
-86.0% 79,229.7 78,246.9 -982.8 -1.2% 45,929.2 32,317.7 70.4%
6.1% 668,769.0 514,230.0 -154,539.0 -23.1% 654,233.8 -140,003.8 -21.4%
May 2011 June 2011
Prior Year Actual Year Delta to Prior Year Plan Delta to Plan Prior Year
Absolute % Absolute %
112,211.1 91,245.2 -20,966.0 -18.7% 177,514.7 -86,269.5 -48.6% 112,674.8
11,107.3 28,532.1 17,424.8 156.9% 77,718.7 -49,186.6 -63.3% 69,268.9
5,699.4 35,017.5 29,318.0 514.4% 96,974.0 -61,956.5 -63.9% 11,028.5
95,404.4 27,695.6 -67,708.8 -71.0% 2,821.9 24,873.6 881.4% 32,377.4
311,070.8 233,204.3 -77,866.5 -25.0% 262,002.8 -28,798.5 -11.0% 317,770.0
87,919.1 19,439.0 -68,480.1 -77.9% 35,371.5 -15,932.5 -45.0% 99,673.1
74,654.9 31,396.3 -43,258.6 -57.9% 4,825.6 26,570.7 550.6% 10,390.9
6,764.4 63,823.5 57,059.1 843.5% 13,193.6 50,629.9 383.7% 70,503.3
54,603.2 31,799.3 -22,803.8 -41.8% 95,438.4 -63,639.1 -66.7% 34,094.3
78,890.9 22,283.2 -56,607.7 -71.8% 21,430.7 852.5 4.0% 61,146.6
8,238.4 64,463.0 56,224.6 682.5% 91,743.0 -27,280.0 -29.7% 41,961.8
122,774.0 106,142.9 -16,631.1 -13.5% 96,653.7 9,489.2 9.8% 47,541.6
30,401.3 12,409.4 -17,991.9 -59.2% 29,492.5 -17,083.1 -57.9% 27,889.1
92,372.7 93,733.5 1,360.8 1.5% 67,161.2 26,572.3 39.6% 19,652.5
546,056.0 430,592.3 -115,463.6 -21.1% 536,171.2 -105,578.8 -19.7% 477,986.4
June 2011 July 2011
Actual Year Delta to Prior Year Plan Delta to Plan Prior Year Actual Year
Absolute % Absolute %
79,334.3 -33,340.6 -29.6% 127,139.1 -47,804.9 -37.6% 194,272.1 153,658.8
5,724.2 -63,544.7 -91.7% 23,907.3 -18,183.1 -76.1% 56,376.6 49,843.4
38,775.5 27,746.9 251.6% 14,984.9 23,790.5 158.8% 95,746.0 32,720.7
34,834.6 2,457.2 7.6% 88,246.9 -53,412.3 -60.5% 42,149.5 71,094.7
254,494.0 -63,276.0 -19.9% 373,892.2 -119,398.2 -31.9% 316,681.3 301,218.5
31,762.7 -67,910.3 -68.1% 93,639.8 -61,877.0 -66.1% 71,840.7 42,292.7
63,043.8 52,652.9 506.7% 57,218.1 5,825.8 10.2% 68,542.4 77,804.0
74,645.8 4,142.5 5.9% 59,782.8 14,863.0 24.9% 76,508.4 34,377.1
17,261.7 -16,832.6 -49.4% 47,780.0 -30,518.3 -63.9% 10,381.8 38,847.0
48,263.8 -12,882.8 -21.1% 68,956.9 -20,693.1 -30.0% 80,489.5 37,811.7
19,516.2 -22,445.6 -53.5% 46,514.7 -26,998.5 -58.0% 8,918.4 70,085.9
129,846.5 82,304.9 173.1% 60,039.2 69,807.2 116.3% 97,714.3 89,147.9
89,493.9 61,604.8 220.9% 42,011.7 47,482.2 113.0% 8,814.6 35,244.1
40,352.6 20,700.1 105.3% 18,027.5 22,325.1 123.8% 88,899.8 53,903.8
463,674.7 -14,311.7 -3.0% 561,070.6 -97,395.8 -17.4% 608,667.7 544,025.2
July 2011 August 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
Absolute % Absolute % Absolute
-40,613.3 -20.9% 58,805.6 94,853.1 161.3% 97,835.4 226,426.1 128,590.7
-6,533.2 -11.6% 10,143.2 39,700.2 391.4% 25,701.2 92,470.1 66,768.9
-63,025.3 -65.8% 37,174.5 -4,453.8 -12.0% 32,615.7 70,150.0 37,534.3
28,945.2 68.7% 11,488.0 59,606.7 518.9% 39,518.5 63,806.0 24,287.5
-15,462.7 -4.9% 329,603.8 -28,385.2 -8.6% 407,221.2 295,164.3 -112,056.8
-29,548.0 -41.1% 44,765.6 -2,472.9 -5.5% 32,763.2 59,853.7 27,090.6
9,261.6 13.5% 64,027.4 13,776.6 21.5% 88,133.1 44,513.1 -43,619.9
-42,131.3 -55.1% 34,665.9 -288.8 -0.8% 75,944.7 84,738.3 8,793.6
28,465.3 274.2% 74,688.7 -35,841.7 -48.0% 73,885.4 35,971.8 -37,913.6
-42,677.8 -53.0% 38,840.4 -1,028.7 -2.6% 49,319.4 229.0 -49,090.5
61,167.5 685.9% 72,615.8 -2,529.8 -3.5% 87,175.5 69,858.4 -17,317.1
-8,566.4 -8.8% 102,019.7 -12,871.8 -12.6% 99,962.9 76,718.4 -23,244.5
26,429.6 299.8% 80,663.9 -45,419.8 -56.3% 71,490.4 504.5 -70,985.9
-34,996.0 -39.4% 21,355.8 32,548.0 152.4% 28,472.5 76,213.9 47,741.3
-64,642.5 -10.6% 490,429.1 53,596.1 10.9% 605,019.5 598,308.8 -6,710.7
August 2011 September 2011
Delta to Prior Year Plan Delta to Plan Prior Year Actual Year Delta to Prior Year
% Absolute % Absolute %
131.4% 90,290.2 136,135.9 150.8% 111,974.6 76,288.4 -35,686.2 -31.9%
259.8% 17,543.5 74,926.6 427.1% 58,067.9 49,394.6 -8,673.4 -14.9%
115.1% 15,725.1 54,424.9 346.1% 6,295.3 25,056.6 18,761.2 298.0%
61.5% 57,021.6 6,784.4 11.9% 47,611.3 1,837.3 -45,774.1 -96.1%
-27.5% 238,148.1 57,016.2 23.9% 276,134.9 425,437.2 149,302.3 54.1%
82.7% 30,599.1 29,254.6 95.6% 12,100.2 65,048.9 52,948.7 437.6%
-49.5% 35,294.5 9,218.7 26.1% 57,057.1 65,994.7 8,937.5 15.7%
11.6% 48,245.4 36,492.9 75.6% 79,273.7 54,672.7 -24,601.0 -31.0%
-51.3% 39,634.0 -3,662.2 -9.2% 68,524.6 61,956.8 -6,567.8 -9.6%
-99.5% 67,039.6 -66,810.7 -99.7% 1,541.2 98,865.5 97,324.2 6314.7%
-19.9% 17,335.4 52,523.0 303.0% 57,638.0 78,898.7 21,260.7 36.9%
-23.3% 154,471.6 -77,753.2 -50.3% 146,788.8 137,510.9 -9,277.9 -6.3%
-99.3% 72,167.9 -71,663.4 -99.3% 84,069.0 85,564.7 1,495.7 1.8%
167.7% 82,303.7 -6,089.8 -7.4% 62,719.8 51,946.2 -10,773.6 -17.2%
-1.1% 482,909.9 115,398.9 23.9% 534,898.4 639,236.5 104,338.2 19.5%
September 2011 October 2011
Plan Delta to Plan Prior Year Actual Year Delta to Prior Year Plan
Absolute % Absolute %
215,790.4 -139,502.0 -64.6% 99,361.6 188,387.9 89,026.3 89.6% 149,945.4
84,544.6 -35,150.0 -41.6% 30,445.5 84,716.5 54,271.0 178.3% 61,621.0
33,578.7 -8,522.1 -25.4% 26,842.6 22,356.2 -4,486.4 -16.7% 709.3
97,667.1 -95,829.8 -98.1% 42,073.5 81,315.2 39,241.6 93.3% 87,615.1
267,089.9 158,347.4 59.3% 192,675.2 227,600.9 34,925.7 18.1% 305,481.8
9,467.0 55,581.8 587.1% 25,006.8 17,247.5 -7,759.3 -31.0% 88,633.5
96,776.0 -30,781.3 -31.8% 12,720.9 8,737.6 -3,983.3 -31.3% 90,356.3
57,989.2 -3,316.5 -5.7% 53,058.5 33,290.9 -19,767.6 -37.3% 9,981.4
33,279.3 28,677.5 86.2% 8,573.4 71,282.2 62,708.8 731.4% 48,701.4
57,060.5 41,804.9 73.3% 20,807.6 83,850.1 63,042.5 303.0% 17,333.2
12,517.8 66,380.9 530.3% 72,508.0 13,192.5 -59,315.5 -81.8% 50,476.1
91,115.8 46,395.1 50.9% 53,267.3 96,001.5 42,734.2 80.2% 66,664.1
58,793.5 26,771.2 45.5% 22,412.1 84,684.9 62,272.7 277.9% 46,695.7
32,322.3 19,623.9 60.7% 30,855.1 11,316.6 -19,538.5 -63.3% 19,968.4
573,996.0 65,240.5 11.4% 345,304.1 511,990.3 166,686.1 48.3% 522,091.3
October 2011 November 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
Absolute % Absolute % Absolute
38,442.5 25.6% 154,404.4 143,111.2 -11,293.2 -7.3% 96,737.2 46,374.0
23,095.6 37.5% 47,310.5 29,818.4 -17,492.1 -37.0% 44,202.9 -14,384.5
21,646.8 3051.7% 58,031.6 67,775.6 9,744.1 16.8% 37,582.1 30,193.5
-6,299.9 -7.2% 49,062.2 45,517.2 -3,545.1 -7.2% 14,952.2 30,564.9
-77,880.9 -25.5% 365,957.7 401,039.5 35,081.8 9.6% 237,290.9 163,748.6
-71,386.0 -80.5% 61,429.9 45,172.8 -16,257.1 -26.5% 67,090.3 -21,917.5
-81,618.6 -90.3% 23,952.8 62,992.3 39,039.5 163.0% 14,903.0 48,089.3
23,309.5 233.5% 62,052.2 68,887.2 6,835.0 11.0% 83,628.8 -14,741.6
22,580.8 46.4% 35,931.0 62,696.4 26,765.4 74.5% 39,660.7 23,035.7
66,516.9 383.8% 99,405.6 62,669.5 -36,736.0 -37.0% 29,068.5 33,601.0
-37,283.6 -73.9% 83,186.2 98,621.2 15,435.0 18.6% 2,939.5 95,681.6
29,337.4 44.0% 110,768.8 104,330.2 -6,438.6 -5.8% 181,956.7 -77,626.5
37,989.1 81.4% 92,861.9 79,584.9 -13,277.0 -14.3% 90,110.0 -10,525.1
-8,651.7 -43.3% 17,906.9 24,745.2 6,838.4 38.2% 91,846.7 -67,101.4
-10,101.0 -1.9% 631,130.8 648,480.8 17,350.0 2.7% 515,984.8 132,496.1
November 2011 December 2011
Delta to Plan Prior Year Actual Year Delta to Prior Year Plan Delta to Plan
% Absolute % Absolute %
47.9% 201,139.5 256,949.6 55,810.0 27.7% 136,122.6 120,826.9 88.8%
-32.5% 40,764.6 96,410.3 55,645.7 136.5% 77,485.3 18,925.0 24.4%
80.3% 70,061.0 66,212.5 -3,848.5 -5.5% 4,045.8 62,166.8 1536.6%
204.4% 90,314.0 94,326.8 4,012.9 4.4% 54,591.6 39,735.2 72.8%
69.0% 315,176.7 362,068.3 46,891.7 14.9% 249,231.2 112,837.2 45.3%
-32.7% 94,461.1 4,790.4 -89,670.7 -94.9% 65,218.2 -60,427.8 -92.7%
322.7% 23,464.0 67,596.2 44,132.2 188.1% 29,407.5 38,188.6 129.9%
-17.6% 60,725.8 83,010.0 22,284.2 36.7% 32,325.2 50,684.8 156.8%
58.1% 9,206.7 62,696.3 53,489.5 581.0% 87,148.1 -24,451.8 -28.1%
115.6% 40,379.4 61,120.2 20,740.9 51.4% 2,394.0 58,726.2 2453.0%
3255.0% 86,939.7 82,855.3 -4,084.5 -4.7% 32,738.1 50,117.1 153.1%
-42.7% 60,054.1 118,186.6 58,132.5 96.8% 94,690.0 23,496.6 24.8%
-11.7% 56,184.0 42,689.6 -13,494.4 -24.0% 42,208.7 480.9 1.1%
-73.1% 3,870.1 75,497.0 71,626.9 1850.8% 52,481.4 23,015.7 43.9%
25.7% 576,370.3 737,204.5 160,834.2 27.9% 480,043.9 257,160.7 53.6%
Data Business Warehouse
Lookup Table January 2011Prior Year January 2011Actual Year January 2011Plan
Column Index 5 6 7
January 2011 January 2011 January 2011Lookup Tabl Row Index Prior Year Actual Year PlanJAP 5 JAP 61989.5 30113.1 25283.5HK 6 HK 30831.9 30242.3 35150.1CHIN 7 CHIN 20045.1 49353.3 65597.0CH 8 CH 19391.7 77283.2 57260.6DE 9 DE 60679.0 39256.6 77448.1AT 10 AT 68665.2 19259.5 41095.4UK 11 UK 93138.6 96708.3 95995.9SP 12 SP 53653.4 67776.5 73408.7PT 13 PT 43860.5 32056.1 86499.4TK 14 TK 51525.2 33675.8 14623.0MO 15 MO 90728.4 74673.6 13496.9
February 2011Prior Year February 2011Actual YeaFebruary 2011Plan March 2011Prior Year March 2011Actual Year
8 9 10 11 12
February 2011 February 2011 February 2011 March 2011 March 2011Prior Year Actual Year Plan Prior Year Actual Year
52652.9 5410.0 36196.3 94116.2 28477.074342.1 2500.1 27785.5 65299.5 51699.860762.1 67919.7 91971.0 29260.9 70533.419156.5 50870.4 78467.5 30104.6 90836.953330.2 39276.3 21975.2 63639.2 47484.218757.8 34650.0 54700.5 44693.4 33003.196545.7 14089.7 24182.2 24335.9 87486.3
1614.1 73127.0 87786.6 69510.9 53838.592207.0 31949.0 44925.2 54263.7 50110.351368.9 40528.5 76949.3 17417.4 64341.3
2239.3 13173.9 14722.7 99775.3 96668.2
March 2011Plan April 2011Prior Year April 2011Actual Year April 2011Plan May 2011Prior Year
13 14 15 16 17
March 2011 April 2011 April 2011 April 2011 May 2011Plan Prior Year Actual Year Plan Prior Year
14836.3 62935.9 94251.3 78237.8 626.999514.4 22378.4 85184.9 16206.5 58466.040744.3 24531.1 24223.9 70573.6 94323.370132.3 39155.8 41871.3 79505.2 10330.866044.8 49490.4 22434.4 73546.6 74682.196783.6 51417.9 17598.0 12890.4 84328.722988.1 99419.8 2319.3 51326.9 3312.391175.3 57109.3 5094.8 91027.8 34932.332922.2 73731.0 71230.3 29808.6 56054.214167.9 3115.1 78656.1 20726.8 12320.565102.9 54357.4 21549.1 20171.1 95234.6
May 2011Actual Year May 2011Plan June 2011Prior Year June 2011Actual Year June 2011Plan
18 19 20 21 22
May 2011 May 2011 June 2011 June 2011 June 2011Actual Year Plan Prior Year Actual Year Plan
56336.4 55985.7 35285.1 96091.5 12053.288935.7 8590.6 82139.0 74194.2 47872.237091.3 90511.8 7715.0 42937.2 31282.193950.6 89030.9 74032.0 54602.7 14228.0
708.0 71222.8 40805.4 90838.0 94590.146366.3 72154.1 83499.7 89691.7 22143.242906.2 85940.5 60255.0 85657.2 10942.327625.9 21505.6 96385.3 92320.1 31898.192010.5 47609.3 99992.5 81435.6 91890.955512.8 90411.8 30482.6 84320.3 86290.974348.2 26122.9 54536.7 98863.0 35258.9
July 2011Prior Year July 2011Actual Year July 2011Plan August 2011Prior Year August 2011Actual Year
23 24 25 26 27
July 2011 July 2011 July 2011 August 2011 August 2011Prior Year Actual Year Plan Prior Year Actual Year
37327.2 12597.1 68352.7 7560.0 77037.622831.4 64718.6 74640.4 12438.0 40911.432675.9 57082.9 33320.0 36402.8 1783.553578.4 46689.0 6651.6 30565.7 39844.278696.0 11206.3 72069.0 54836.7 44163.952184.2 92101.7 62057.9 27343.5 62845.6
5446.5 34567.2 51238.5 94993.4 41856.696376.2 8173.8 96404.3 19832.5 18032.797135.8 27072.2 27950.9 9062.9 87881.927916.2 83342.5 74438.7 71963.5 73785.822616.2 60279.1 14086.1 47428.3 15347.3
August 2011Plan September 2011Prior YeaSeptember 2011Actual Y September 2011Plan October 2011Prior Year
28 29 30 31 32
August 2011 September 2011 September 2011 September 2011 October 2011Plan Prior Year Actual Year Plan Prior Year
53432.0 65098.5 2305.7 8632.6 35147.970056.0 6969.6 39414.1 63203.2 10492.095782.6 77565.9 73083.8 77533.0 6135.5
6762.9 24751.6 14398.1 15730.9 35443.939255.0 81083.2 45602.3 60128.8 62013.977715.3 96421.6 35167.0 23756.6 75257.4
3698.3 79694.8 37334.1 41209.3 43093.439904.0 34153.2 67611.5 24045.1 94267.748230.0 54854.6 63062.2 86584.0 52272.261210.9 31742.2 52887.8 67185.8 18302.275948.9 53157.0 86959.8 58809.3 52025.0
October 2011Actual Year October 2011Plan November 2011Prior Yea November 2011Actual YeNovember 2011Plan
33 34 35 36 37
October 2011 October 2011 November 2011 November 2011 November 2011Actual Year Plan Prior Year Actual Year Plan
32971.6 56278.6 39470.0 48174.2 31659.49855.3 73036.2 4841.9 56489.5 44357.5
89001.6 8325.8 61397.7 71351.2 56180.728664.5 3574.7 37448.6 40618.7 45119.657635.8 81502.5 99232.3 93470.9 1046.0
6206.5 57446.8 3251.8 23274.8 75114.869913.7 42067.9 86744.8 52455.5 30590.788440.4 65906.5 77858.1 62117.6 76890.465108.6 7488.4 38599.6 1478.2 24669.691813.2 20880.8 46563.5 73589.8 7285.713341.9 64442.7 47015.9 58072.1 7905.2
December 2011Prior Yea December 2011Actual YeDecember 2011Plan
38 39 40
December 2011 December 2011 December 2011Prior Year Actual Year Plan
85542.3 11756.9 21554.948958.7 42208.3 83985.462573.6 20389.1 67009.430732.5 54262.3 50028.862293.0 31243.4 81927.517641.2 1679.7 35686.823196.2 49282.4 51189.180313.2 61394.9 75454.758680.7 84323.1 50082.889042.5 8997.9 64778.338518.2 99533.4 99539.6