EXCEL ASSIGNMENT (Percentage of Completion)

Required:

1- Using the data provided below you are to input formulas in the area designated below to calculate: % complete, revenue to be recognized in each year, and gross profit to be recognized in each year. (10 points)

Hint: I suggest you use formulas with an IF function regarding the gross profit section of your speadsheet because your spreadsheet should be able to calculate correct answers whether a contract generates a profit or loss.

2- Using the data given and the solutions in your spreadsheet generated, prepare all 4 journal entries for all 3 years. Whenever possible, the amounts for your journal entries should be formulas that reference the appropriate cells in the calcuations below.

(10 points)

**Hint: You have to figure out the actual cost, billings, and collections for each respective year. The information presented is "to date" not "Costs expended this year" as in the handouts and some of your assigned exercises/problems

Use format provided below to input formulas for each respective year (2022, 2023, 2024). Provide formulas, with numbers, for proof.

Cost incurred in current year

Cumulated Costs incurred to date

Estimated total costs % complete

Contract price

Gross Profit:

Estimated total gross profit % complete

Gross profit recognized to date

Gross profit recognized prior

Gross profit recognized current

Revenues % complete

Revenue recognized to date

Revenue recognized prior

Revenue recognized current

Excel Assignment (Percentage-of-completion) Required: 1- Using the data provided below you are to input formulas in the area designated below to calculate: % complete, revenue to be recognized in each year, and gross profit to be recognized in each year. (10 points) Hint: I suggest you use formulas with an IF function regarding the gross profit section of your speadsheet because your spreadsheet should be able to caleulate correct answers whether a contract generates a profit or loss. 2- Using the data given and the solutions in your spreadsheet generated, prepare all 4 journal entries for all 3 years. Whenever possible, the amounts for your journal entries should be formulas that reference the appropriate cells in the calcuations below. (10 points) 3-Once you have completed the spreadsheet save your file with your last name(s) and first name(s) and upload it under Assignments by the assignment due date. \begin{tabular}{|c|c|c|c|} \hline Data: & & & - \\ \hline & & & \\ \hline \multirow[t]{3}{*}{ Contract price } & 1,200,000 & & \\ \hline & & & \\ \hline & 2022 & 2023 & 2024 \\ \hline Cumulated Costs incurred to date** & $280.000 & $600.000 & $785,000 \\ \hline Estimated costs yet to be incurred & 520,000 & 200,000 & 0 \\ \hline Customer billings to date** & 250.000 & 500,000 & 1200,000 \\ \hline Collections of billings to date & 120,000 & 320,000 & 1.040,000 \\ \hline \multicolumn{4}{|c|}{\begin{tabular}{l} "-Hint: You have to figure out the actual cost, billings, and collections for each respective year. The information presented is "to date" not "Costs \\ expended this year" as in the handouts and some of your assigned exercises/problems. \end{tabular}} \\ \hline & & & \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Use the format provided below to input formulas for each respective year. } \\ \hline & & & \multirow{2}{*}{2024} \\ \hline & 2022 & 2023 & \\ \hline \multicolumn{4}{|l|}{ Cost incurred in current year } \\ \hline Cumulated Costs incurred to date. & m & & \\ \hline Estimated total costs & & & + \\ \hline \multirow[t]{2}{*}{ \% complete } & & + & \\ \hline & & T. & + \\ \hline \multicolumn{4}{|l|}{ Contract price } \\ \hline i & & & \\ \hline \multicolumn{4}{|l|}{ Gross Profit: } \\ \hline \multicolumn{4}{|l|}{ Estimated total gross profit } \\ \hline \multicolumn{4}{|l|}{ \% complete } \\ \hline \multicolumn{4}{|l|}{ Gross profit recognized to date } \\ \hline \multicolumn{4}{|l|}{ Gross peofit recognized prior } \\ \hline \multirow[t]{2}{*}{ Gross profit recognized current } & + & + & \\ \hline & & & \\ \hline \multicolumn{4}{|l|}{ Revenue: } \\ \hline \% complete & & & % \\ \hline \multicolumn{4}{|l|}{ Revenue recognized to date } \\ \hline \multicolumn{4}{|l|}{ Revenue recognizod prior } \\ \hline Revenue recognized current & a & & 1 \\ \hline \end{tabular} Excel Assignment (Percentage-of-completion) Required: 1- Using the data provided below you are to input formulas in the area designated below to calculate: % complete, revenue to be recognized in each year, and gross profit to be recognized in each year. (10 points) Hint: I suggest you use formulas with an IF function regarding the gross profit section of your speadsheet because your spreadsheet should be able to caleulate correct answers whether a contract generates a profit or loss. 2- Using the data given and the solutions in your spreadsheet generated, prepare all 4 journal entries for all 3 years. Whenever possible, the amounts for your journal entries should be formulas that reference the appropriate cells in the calcuations below. (10 points) 3-Once you have completed the spreadsheet save your file with your last name(s) and first name(s) and upload it under Assignments by the assignment due date. \begin{tabular}{|c|c|c|c|} \hline Data: & & & - \\ \hline & & & \\ \hline \multirow[t]{3}{*}{ Contract price } & 1,200,000 & & \\ \hline & & & \\ \hline & 2022 & 2023 & 2024 \\ \hline Cumulated Costs incurred to date** & $280.000 & $600.000 & $785,000 \\ \hline Estimated costs yet to be incurred & 520,000 & 200,000 & 0 \\ \hline Customer billings to date** & 250.000 & 500,000 & 1200,000 \\ \hline Collections of billings to date & 120,000 & 320,000 & 1.040,000 \\ \hline \multicolumn{4}{|c|}{\begin{tabular}{l} "-Hint: You have to figure out the actual cost, billings, and collections for each respective year. The information presented is "to date" not "Costs \\ expended this year" as in the handouts and some of your assigned exercises/problems. \end{tabular}} \\ \hline & & & \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Use the format provided below to input formulas for each respective year. } \\ \hline & & & \multirow{2}{*}{2024} \\ \hline & 2022 & 2023 & \\ \hline \multicolumn{4}{|l|}{ Cost incurred in current year } \\ \hline Cumulated Costs incurred to date. & m & & \\ \hline Estimated total costs & & & + \\ \hline \multirow[t]{2}{*}{ \% complete } & & + & \\ \hline & & T. & + \\ \hline \multicolumn{4}{|l|}{ Contract price } \\ \hline i & & & \\ \hline \multicolumn{4}{|l|}{ Gross Profit: } \\ \hline \multicolumn{4}{|l|}{ Estimated total gross profit } \\ \hline \multicolumn{4}{|l|}{ \% complete } \\ \hline \multicolumn{4}{|l|}{ Gross profit recognized to date } \\ \hline \multicolumn{4}{|l|}{ Gross peofit recognized prior } \\ \hline \multirow[t]{2}{*}{ Gross profit recognized current } & + & + & \\ \hline & & & \\ \hline \multicolumn{4}{|l|}{ Revenue: } \\ \hline \% complete & & & % \\ \hline \multicolumn{4}{|l|}{ Revenue recognized to date } \\ \hline \multicolumn{4}{|l|}{ Revenue recognizod prior } \\ \hline Revenue recognized current & a & & 1 \\ \hline \end{tabular}