Revenue Recognition - Percentage of Completion - Project Instructions

You and your friend have recently accepted internships with a local CPA firm and have been given the following Percentage of Completion project by your manager.

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 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.

2) Using the Conditional Formatting feature, highlight those cells in the "Estimated GP @ Completion" column on the Current Year tab to bring attention to any projects that have suffered an overall loss.

3) In order to give a visual cue as to which projects are nearing and/or have exceeded their overall estimated cost, use the Conditional Formatting feature, to highlight the "Costs to Date" cells in the Current Year tab that are greater than 75% of their estimated costs, those that are greater than 85% of their estimated cost and those that are over their estimated cost. Please use green, yellow, and red as your color choices.

4) Using the VLOOKUP function, please complete the "Phase" column on the Current Year tab. The "Table" sheet has been provided to assist with populating this data according to the percentage completion for each job. Just like in the A/R project, please refrain from hard coding the column index value.

5) Using the PIVOT TABLE utility, please create the following reports in a new tab:

A. A summary count of jobs in the various phases (Phase 1 - Phase V) and include a subtotal of costs to date for each phase.

B. A summary report of all jobs allowing a filter by Phase. The report should contain job number, final contract amount, costs to date, estimated cost to completion, and the percentage the job represents of the total final contract amount of all jobs.

6) Populate the "Revenue to Book" on the Current Year tab. HINT: revenue that you are recognizing in the current year must take into account revenue recognized in previous periods. You may find it necessary to use functions that have been previously discussed in class such as VLOOKUP and conditional functions.

7) Provide the journal entry to book the revenue for Job ID 2006.