In: Accounting
Revenue Recognition - Percentage of Completion - Project Instructions, Spring 2018
One of your clients is a large regional construction company. The company has many long-term projects in the works. The spreadsheet you are given contains some information about these jobs. Some of the jobs began last year and continue into the current year, so they exist on both tabs. The projects are in various phases of construction; some of which were completed during the current year.
You've been asked by your firm to analyze the data and prepare the following:
1) Complete the revenue recognition process by adding the necessary calculations into the blank columns F-J and L-M on the Current Year and Prior Year tabs and populate the data for all rows. It is expected that you look back at the text for definitions and to remind yourself of the percentage of completion method prior to asking questions.
Current year:
Job | Description | Final Est Contract Amount | Costs To Date | Total Est Cost @ Completion | Est to Complete | Est GP @ Completion | Cur % Comp | GP TO DATE | Earned Rev To Date | Billings To Date | Costs and profit in Excess of Billings | Billings in Excess of Costs and Profits | Phase | Revenue to Book |
1959 | MISC | 7,465,878 | 7,247,171 | 7,247,523 | 7,465,878 | |||||||||
1960 | MISC | 7,959,254 | 7,666,799 | 7,706,890 | 7,959,254 | |||||||||
1968 | MISC | 46,201,877 | 44,283,436 | 45,810,921 | 44,435,743 | |||||||||
1029 | MISC | 6,770,156 | 7,187,389 | 7,169,543 | 6,693,717 | |||||||||
1972 | MISC | 13,157,854 | 9,938,521 | 12,926,801 | 10,169,759 | |||||||||
1974 | MISC | 16,811,634 | 16,636,718 | 16,614,718 | 16,811,634 | |||||||||
1978 | MISC | 3,240,516 | 3,180,526 | 3,180,526 | 3,290,995 | |||||||||
1980 | MISC | 7,098,168 | 6,696,897 | 6,825,642 | 6,989,363 | |||||||||
1981 | MISC | 22,464,145 | 22,067,900 | 22,067,900 | 22,462,520 | |||||||||
1982 | MISC | 26,910,817 | 26,266,450 | 26,384,521 | 26,910,817 | |||||||||
1987 | MISC | 21,527,597 | 20,758,005 | 20,758,244 | 21,527,597 | |||||||||
1037 | MISC | 67,479 | 65,707 | 62,708 | 67,480 | |||||||||
1038 | MISC | 966,844 | 949,107 | 938,107 | 966,845 | |||||||||
1040 | MISC | 166,942 | 143,084 | 143,084 | 166,942 | |||||||||
1041 | MISC | 700,689 | 598,903 | 598,804 | 700,689 | |||||||||
1042 | MISC | 346,721 | 251,917 | 292,629 | 346,721 | |||||||||
1043 | MISC | 1,079,159 | 1,079,159 | 1,079,159 | 1,079,190 | |||||||||
1044 | MISC | 510,939 | 393,910 | 411,390 | 503,188 | |||||||||
1991 | MISC | 21,145,050 | 21,511,616 | 21,511,616 | 21,045,050 | |||||||||
1992 | MISC | 3,564,527 | 3,519,267 | 3,519,270 | 3,572,179 | |||||||||
1994 | MISC | 7,789,575 | 7,516,192 | 7,552,450 | 7,789,575 | |||||||||
1995 | MISC | 13,320,841 | 12,531,906 | 12,784,219 | 13,006,125 | |||||||||
1996 | MISC | 13,535,310 | 12,331,088 | 13,178,122 | 13,029,481 | |||||||||
1997 | MISC | 2,729,944 | 2,657,501 | 2,661,842 | 2,729,944 | |||||||||
1998 | MISC | 7,341,782 | 5,274,107 | 7,161,359 | 5,440,960 | |||||||||
1999 | MISC | 13,327,661 | 12,510,677 | 13,078,905 | 12,745,737 | |||||||||
2000 | MISC | 3,479,901 | 3,391,393 | 3,392,850 | 3,479,901 | |||||||||
2001 | MISC | 8,880,861 | 4,967,713 | 8,671,818 | 4,897,156 | |||||||||
2002 | MISC | 5,407,348 | 5,097,975 | 5,232,080 | 5,278,519 | |||||||||
2003 | MISC | 6,057,682 | 4,689,023 | 5,835,790 | 5,088,472 | |||||||||
2004 | MISC | 3,388,921 | 3,327,035 | 3,371,364 | 3,388,921 | |||||||||
2005.01 | MISC | 1,541,229 | 651,078 | 1,488,653 | 677,958 | |||||||||
2005.02 | MISC | 1,382,138 | 494,118 | 1,382,138 | 494,118 | |||||||||
2005.03 | MISC | 109,951 | 109,951 | 109,951 | 109,727 | |||||||||
2006 | MISC | 6,243,535 | 4,063,446 | 6,138,535 | 4,160,662 | |||||||||
2007 | MISC | 12,234,190 | 9,906,338 | 11,918,995 | 10,139,456 | |||||||||
1045 | MISC | 1,273,869 | 473,014 | 1,178,757 | 558,982 | |||||||||
1047 | MISC | 2,000,000 | 1,532,818 | 1,934,051 | 1,498,479 | |||||||||
1048 | MISC | 289,132 | 280,188 | 277,088 | 289,132 | |||||||||
1050 | MISC | 337,399 | 330,095 | 337,399 | 287,285 | |||||||||
1051 | MISC | 103,714 | 91,533 | 93,701 | 95,000 | |||||||||
1052 | MISC | 1,627,500 | 898,738 | 1,532,500 | 867,785 | |||||||||
1053 | MISC | 587,936 | 119,971 | 534,936 | 59,133 | |||||||||
1054 | MISC | 272,187 | 93,423 | 232,095 | 139,406 | |||||||||
1055 | MISC | 1,368 | 1,189 | 1,189 | - | |||||||||
1056 | MISC | 6,500 | 3,241 | 5,800 | 6,500 | |||||||||
1057 | MISC | 160,254 | 3,979 | 151,254 | 53,418 | |||||||||
2008 | MISC | 12,027,982 | 11,540,290 | 11,792,749 | 11,624,921 | |||||||||
2010 | MISC | 3,564,023 | 3,442,746 | 3,482,704 | 3,510,318 | |||||||||
2011 | MISC | 3,571,024 | 3,172,365 | 3,471,558 | 3,263,051 | |||||||||
2012 | MISC | 8,863,236 | 3,382,784 | 8,483,236 | 3,484,037 | |||||||||
2013 | MISC | 17,562,203 | 3,043,921 | 17,237,203 | 3,175,529 | |||||||||
2014 | MISC | 3,469,196 | 1,713,032 | 3,379,313 | 2,240,054 | |||||||||
2015 | MISC | 6,270,919 | 1,759,495 | 6,111,394 | 2,468,135 | |||||||||
2017 | MISC | 906,972 | 870,069 | 870,070 | 907,612 | |||||||||
2018 | MISC | 16,981,831 | 20,841 | 16,646,831 | 123,250 | |||||||||
2019 | MISC | 12,514,813 | 734,163 | 12,188,248 | 825,637 | |||||||||
2020 | MISC | 12,215,549 | 35,797 | 11,860,853 | - | |||||||||
2021 | MISC | 5,475,364 | 82,553 | 5,575,364 | 226,051 | |||||||||
2022 | MISC | 14,031,862 | 90,859 | 13,669,617 | - | |||||||||
2023 | MISC | - | 831 | - | - | |||||||||
2024 | MISC | - | - | - | - | |||||||||
2025 | MISC | 5,399,500 | - | 5,243,500 | - | |||||||||
2026 | MISC | 1,030,000 | 8,317 | 1,000,000 | - | |||||||||
2027 | MISC | 5,700,000 | - | 5,586,000 | - | |||||||||
1941 | MISC | 11,700,949 | 11,306,077 | 11,306,077 | 11,700,949 | |||||||||
1951 | MISC | 430,558 | 429,853 | 429,853 | 430,558 | |||||||||
1958 | MISC | 5,113,424 | 5,012,706 | 5,012,706 | 5,113,424 | |||||||||
1966 | MISC | 2,746,125 | 2,749,328 | 2,749,328 | 2,746,125 | |||||||||
1027 | MISC | 297,482 | 290,349 | 290,349 | 297,482 | |||||||||
1028 | MISC | 1,462,324 | 1,478,580 | 1,478,580 | 1,462,324 | |||||||||
1030 | MISC | 819,108 | 709,272 | 709,272 | 819,108 | |||||||||
1971 | MISC | 5,439,533 | 5,205,826 | 5,205,826 | 5,439,533 | |||||||||
1973 | MISC | 6,997,612 | 7,190,985 | 7,190,985 | 6,997,612 | |||||||||
1975 | MISC | 3,541,081 | 3,412,625 | 3,412,625 | 3,541,081 | |||||||||
1977 | MISC | 2,111,870 | 2,060,093 | 2,060,093 | 2,111,870 | |||||||||
1979 | MISC | 3,883,384 | 4,095,004 | 4,095,004 | 3,883,384 | |||||||||
1983 | MISC | 8,416,157 | 8,228,116 | 8,228,116 | 8,416,157 | |||||||||
1984 | MISC | 10,454,724 | 10,201,631 | 10,201,631 | 10,454,724 | |||||||||
1986 | MISC | 7,120,629 | 6,862,156 | 6,862,156 | 7,120,629 | |||||||||
1988 | MISC | 244,433 | 190,944 | 190,944 | 244,433 | |||||||||
1989 | MISC | 6,573,752 | 6,410,702 | 6,410,702 | 6,573,752 | |||||||||
1031 | MISC | 356,254 | 331,883 | 331,883 | 356,254 | |||||||||
1032 | MISC | 1,262,853 | 1,261,237 | 1,261,237 | 1,262,853 | |||||||||
1033 | MISC | 2,184,271 | 1,668,446 | 1,668,446 | 2,184,271 | |||||||||
1034 | MISC | 276,190 | 276,190 | 276,190 | 276,190 | |||||||||
1035 | MISC | 221,826 | 206,835 | 206,835 | 221,826 | |||||||||
1036 | MISC | 112,637 | 112,629 | 112,629 | 112,637 | |||||||||
1039 | MISC | 73,237 | 56,173 | 56,173 | 73,237 | |||||||||
1990 | MISC | 4,753,810 | 4,682,621 | 4,682,621 | 4,753,810 | |||||||||
1993 | MISC | 2,419,387 | 2,381,836 | 2,381,836 | 2,419,387 | |||||||||
1046 | MISC | 167,393 | 156,353 | 156,353 | 167,393 | |||||||||
1049 | MISC | 204,076 | 177,714 | 177,714 | 204,076 | |||||||||
540,556,527 | 410,834,439 | 529,232,641 | - | - | - | - | - | 420,711,067 | - | - |
Prior Yr:
Job | Description | Final Est Contract Amount | Costs To Date | Total Est Cost @ Completion | Est to Complete | Est GP @ Completion | Cur % Comp | GP TO DATE | Earned Rev To Date | Billings To Date | Costs and profit in Excess of Billings | Billings in Excess of Costs and Profits |
1977 | MISC | 2,090,204 | 2,058,488 | 2,060,000 | 2,090,204 | |||||||
1966 | MISC | 2,740,196 | 2,747,956 | 2,750,000 | 2,740,196 | |||||||
1951 | MISC | 429,853 | 429,853 | 429,853 | 430,558 | |||||||
1988 | MISC | 238,471 | 190,944 | 190,944 | 236,471 | |||||||
1027 | MISC | 297,482 | 290,349 | 290,349 | 277,482 | |||||||
1028 | MISC | 1,459,971 | 1,468,711 | 1,468,711 | 1,449,843 | |||||||
1030 | MISC | 819,108 | 707,310 | 707,310 | 805,483 | |||||||
1979 | MISC | 3,883,384 | 4,094,330 | 4,095,000 | 3,883,384 | |||||||
1036 | MISC | 112,637 | 112,618 | 112,637 | 112,637 | |||||||
1973 | MISC | 6,997,612 | 7,189,463 | 7,190,800 | 6,997,612 | |||||||
1974 | MISC | 16,396,834 | 16,098,923 | 16,107,664 | 15,945,665 | |||||||
1958 | MISC | 5,113,424 | 5,008,868 | 5,012,893 | 5,111,033 | |||||||
1984 | MISC | 10,458,619 | 10,191,522 | 10,204,882 | 10,453,214 | |||||||
1975 | MISC | 3,541,081 | 3,410,943 | 3,415,946 | 3,544,656 | |||||||
1941 | MISC | 11,700,949 | 11,277,526 | 11,305,949 | 11,700,949 | |||||||
1971 | MISC | 5,439,533 | 5,205,826 | 5,223,433 | 5,439,533 | |||||||
1960 | MISC | 7,959,254 | 7,633,497 | 7,706,890 | 7,959,254 | |||||||
1983 | MISC | 8,363,611 | 8,084,939 | 8,164,134 | 8,286,417 | |||||||
1978 | MISC | 3,240,516 | 3,146,456 | 3,180,516 | 3,185,418 | |||||||
1993 | MISC | 2,419,988 | 2,357,037 | 2,384,988 | 2,419,387 | |||||||
1035 | MISC | 220,950 | 205,377 | 208,000 | 217,330 | |||||||
1034 | MISC | 278,849 | 274,429 | 278,849 | 253,479 | |||||||
1980 | MISC | 7,098,168 | 6,710,276 | 6,825,642 | 6,945,454 | |||||||
1990 | MISC | 4,711,151 | 4,598,024 | 4,678,950 | 4,626,775 | |||||||
1989 | MISC | 6,661,286 | 6,383,190 | 6,497,850 | 6,556,992 | |||||||
1986 | MISC | 7,162,422 | 6,712,121 | 6,920,598 | 6,884,933 | |||||||
1037 | MISC | 68,879 | 56,897 | 61,400 | 67,480 | |||||||
1031 | MISC | 378,974 | 330,684 | 360,200 | 356,254 | |||||||
1981 | MISC | 22,282,269 | 20,016,392 | 22,051,402 | 20,470,769 | |||||||
1992 | MISC | 3,461,496 | 2,945,587 | 3,404,937 | 2,955,664 | |||||||
1991 | MISC | 20,138,168 | 16,559,725 | 19,452,425 | 17,234,825 | |||||||
1033 | MISC | 2,104,258 | 1,417,904 | 1,710,084 | 1,754,574 | |||||||
1029 | MISC | 6,674,301 | 5,588,592 | 6,912,342 | 5,447,628 | |||||||
1994 | MISC | 6,194,488 | 4,644,358 | 6,008,863 | 4,857,414 | |||||||
1032 | MISC | 1,327,931 | 1,025,867 | 1,327,931 | 987,505 | |||||||
1959 | MISC | 7,510,509 | 5,122,011 | 7,291,759 | 5,241,333 | |||||||
1987 | MISC | 21,977,597 | 14,703,934 | 21,524,620 | 15,315,793 | |||||||
1997 | MISC | 2,601,318 | 1,489,455 | 2,543,216 | 1,709,841 | |||||||
1039 | MISC | 72,622 | 36,578 | 62,622 | 47,226 | |||||||
1972 | MISC | 9,300,000 | 4,670,647 | 9,097,073 | 5,175,762 | |||||||
1982 | MISC | 28,064,434 | 13,169,455 | 27,648,138 | 14,477,824 | |||||||
1968 | MISC | 44,263,879 | 16,221,792 | 43,722,923 | 16,494,611 | |||||||
1996 | MISC | 8,000,000 | 2,586,688 | 7,825,000 | 2,743,271 | |||||||
1041 | MISC | 701,835 | 54,041 | 633,948 | 112,435 | |||||||
1038 | MISC | 835,000 | 63,697 | 755,000 | 75,493 | |||||||
1040 | MISC | 166,121 | 11,069 | 143,721 | 35,000 | |||||||
2000 | MISC | 3,460,051 | 248,325 | 3,375,675 | 206,325 | |||||||
1999 | MISC | 11,950,980 | 547,127 | 11,651,589 | 586,828 | |||||||
1995 | MISC | 13,371,080 | 368,910 | 12,831,080 | 383,667 | |||||||
2002 | MISC | 3,500,000 | 66,826 | 3,411,000 | 62,565 | |||||||
2003 | MISC | 4,624,038 | 54,191 | 4,461,051 | 0 | |||||||
2001 | MISC | 8,437,049 | 30,923 | 8,235,633 | 289,215 | |||||||
1043 | MISC | 628,920 | 1,801 | 628,920 | 1,801 | |||||||
2006 | MISC | 6,000,000 | 1,739 | 5,820,000 | 0 | |||||||
2004 | MISC | 3,440,871 | 828 | 3,330,871 | 0 | |||||||
1998 | MISC | 5,500,000 | 875 | 5,375,000 | 0 | |||||||
2007 | MISC | 12,100,481 | 0 | 11,789,465 | 0 | |||||||
2005 | MISC | 0 | 0 | 0 | 0 | |||||||
1042 | MISC | 302,473 | 0 | 268,381 | 0 | |||||||
1044 | MISC | 0 | 0 | 0 | 0 | |||||||
1925 | MISC | 12,598,919 | 11,943,651 | 11,943,651 | 12,598,919 | |||||||
1932.01 | MISC | 38,775,649 | 37,277,096 | 37,277,096 | 38,775,649 | |||||||
1932.02 | MISC | 15,209,443 | 14,612,438 | 14,612,438 | 15,209,443 | |||||||
1933 | MISC | 17,332,336 | 16,836,298 | 16,836,298 | 17,332,336 | |||||||
1940 | MISC | 2,908,333 | 2,577,844 | 2,577,844 | 2,908,333 | |||||||
1023 | MISC | 110,178 | 110,174 | 110,174 | 110,178 | |||||||
1944 | MISC | 6,010,630 | 5,769,964 | 5,769,964 | 6,010,630 | |||||||
1945 | MISC | 2,982,172 | 2,872,203 | 2,872,203 | 2,982,172 | |||||||
1949 | MISC | 3,552,127 | 3,314,044 | 3,314,044 | 3,552,127 | |||||||
1954 | MISC | 3,011,044 | 2,906,830 | 2,906,830 | 3,011,044 | |||||||
1961 | MISC | 4,403,166 | 4,250,089 | 4,250,089 | 4,403,166 | |||||||
1965 | MISC | 3,649,431 | 3,600,724 | 3,600,724 | 3,649,431 | |||||||
1967 | MISC | 3,256,442 | 3,158,722 | 3,158,722 | 3,256,442 | |||||||
1970 | MISC | 771,601 | 688,644 | 688,644 | 771,601 | |||||||
1026 | MISC | 185,392 | 156,420 | 156,420 | 185,392 | |||||||
1976 | MISC | 7,990,442 | 7,796,645 | 7,796,645 | 7,990,442 | |||||||
1985 | MISC | 4,962,349 | 4,826,861 | 4,826,861 | 4,962,349 | |||||||
506,985,229 | 351,324,541 | 493,827,704 | 0 | 0 | 0 | 0 | 363,355,116 | 0 | 0 |
Under percentage of completion method total estimated revenue and total estimated costs of a project are considered.
Where the cost incurred till the last date of the accounting period is compared with the total estimated cost and a percentage is arrived at.
The arrived percentage of the estimated revenue is to be accrued as revenue for the said accounting period.
In the next period, where the project has further executed, the new percentage of completion is arrived with the total cost incurred till date for the project and the total cost estimated of the project.
The percentage is applied to get the proportionate revenue to be recognised.
The revenue so recognized after reducing the revenue recognized in previous period is considered to be the revenue to be accrued for the current period.
Based on the above principle, the excel sheet with the formulae displayed on the top are the workings for the projects revenue to be recognized.