Question

In: Accounting

Excel Assignment (Percentage-of-completion) Required: 1- Using the data provided below you are to input formulas in...

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 your spreadsheet generated, prepare all journal entries for 2017. Whevenver 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.
Data:
Contract price 1,200,000
2016 2017 2018
Costs incurred to date** $280,000 $600,000 $785,000
Estimated costs yet to be incurred 520,000 200,000 0
Customer billings to date** 250,000 500,000 1,200,000
Collections of billings to date** 120,000 320,000 1,040,000
**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 the format provided below to input formulas for each respective year.
2016 2017 2018
Costs expended to date
Estimated total costs
% complete
Contract price
% complete
Revenue recognized to date
Revenue recognized prior
Revenue recognized current
Estimated total gross profit
% complete
Gross profit recognized to date
Gross profit recognized prior
Gross profit recognized current

Solutions

Expert Solution

1.

2016 2017 2018
Costs expended to date 280000 600000 785000
Estimated total costs 800000 800000 785000
% complete 35% 75% 100%
Contract Price 2000000 2000000 2000000
Percentage Complete 35% 75% 100%
Revenue recognised to date 700000 1500000 2000000
Revenue recognized prior 0 700000 1500000
Revenue recognized current 700000 800000 500000
Estimated Total gross profit 420000 1215000 1215000
Gross profit recognized to date 420000 900000 1215000
Gross profit recognized prior 0 420000 900000
Gross Profit Recognised current 420000 480000 315000

2.

Journal entries for 2017
1. For costs incurred
Accounts Title Debit Credit
Contract -in-progress 320000
Accounts Payable 320000
2. For revenue Recognition
Accounts Title Debit Credit
Contract Expenses (Current year expenses) 320000
Contract-in-progress (Curent year gross profit) 4800000
Contract Revenue (Current year revenue) 800000
3. For billings done during the year
Accounts Title Debit Credit
Accounts Receivable 250000
Contract Billings 250000
3. For cash collections during the year
Accounts Title Debit Credit
Cash 200000
Accounts Receivable 200000

Related Solutions

An Excel spreadsheet using formulas and cell referencing is required for this assignment. Careen owns a...
An Excel spreadsheet using formulas and cell referencing is required for this assignment. Careen owns a condominium near Hershey, PA that she rents out part of the year. This year, she incurs the following expenses in connection with her rental condo: Mortgage Interest $9,490 Property Taxes 6,570 Insurance 3,220 Repairs and maintenance 1,610 Utilities 3,703 Depreciation 11,270    During the year, Careen rented the condo for 112 days, receiving $17,600 of gross income. She personally used the condo for 49...
This project should be completed using Excel (with formulas and linked data). Below are the deliverables:...
This project should be completed using Excel (with formulas and linked data). Below are the deliverables: 1. Prepare a Multi-Step Income Statement for the year ended 2018. This statement should be flexibly designed (formulas in cells). To the right of your dollars in this statement, show common-sized percentages based on sales (vertical analysis). 2. Show journal entries, adjusting entries and closing entries for the below additional information…none of the journal entries for 2018 have been posted to the ledger. 3....
USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for...
USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for the problem in the first worksheet named LightbulbLife of the data table down below It gives the data on the lifetime in hours of a sample of 50 lightbulbs. The company manufacturing these bulbs wants to know whether it can claim that its lightbulbs typically last more than 1000 burning hours. So it did a study. Identify the null and the alternate hypotheses for...
3.Below is the regression input and output (from Excel) for the regression in which the percentage...
3.Below is the regression input and output (from Excel) for the regression in which the percentage change in the exchange rate is the dependent variable (Y) and the inflation differential (inflation rate home – inflation rate foreign) is the independent variable (X). According to the Purchasing Power Parity theory, the hypothesized coefficients (null hypothesis) in the regression are 0 (intercept coefficient) and 1 (inflation differential coefficient).   The directions for Problem Set 1 instruct you to first read sections of the...
*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you!* I have provided...
*PLEASE PROVIDE ALL SOLUTIONS USING MICROSOFT EXCEL WITH ANY RELEVANT FORMULAS, thank you!* I have provided the answers to the questions for reference, I just need to know how to get to them. Samantha is going to retire in 20 years. In order to live comfortably, she thinks she will need to withdraw $10,000 every month during retirement. These monthly withdrawals will be made at the end of each month during retirement. Samantha believes she will live for 35 years...
For this assignment, you will read the scenario and then use the provided Excel and Word...
For this assignment, you will read the scenario and then use the provided Excel and Word document templates to complete your assignment before uploading them to the assignment submission area. Scenario Larry and Beth are both married, working adults. They both plan for retirement and consider the $6,000 annual contribution a must. First, consider Beth's savings. She began working at age 20 and began making an annual contribution to her IRA of $6,000 each year until age 32 (12 contributions)....
In this assignment, you need to demonstrate your ability in using input, output, data types, and...
In this assignment, you need to demonstrate your ability in using input, output, data types, and if statement in C++ program. Assume that you need write a C++ program for a cash register. There are only four items in the store: Cereal, $3.99 Milk, $3.99 Egg, $0.25 Water, $ 1.50 Once a customer purchases items, you will ask her/his how many of them are bought. The quantity can be in the range of 0-10 (including 0 and 10). Then, calculate...
Using the formulas provided for the exam, and the balance sheet and income statement shown below,...
Using the formulas provided for the exam, and the balance sheet and income statement shown below, calculate the ratios (1 point each): CORRIGAN CORPORATION: BALANCE SHEET AS OF DECEMBER 31: Cash                                                         72,000 Accounts Receivable                              439,000 Inventory                                                 894,000             Total Current Assets               1,405,000 Land and Building                                  238,000 Machinery                                               132,000 Other Fixed Assets                                    61,000 Total Assets                                         1,836,000                                                           ========== Accounts Payable                                     80,000 Accrued Liabilities                                    45,010 Notes Payable                                         476,990             Total Current Liabilities            602,000 Long-term Debt                                       404,290              Total Liabilities                       1,006,290 Common Stock                                       ...
ZAKI LLP is a networking entity. Required Using the data provided below Draw an A-O-A diagram...
ZAKI LLP is a networking entity. Required Using the data provided below Draw an A-O-A diagram based on normal times and identify the critical path, calculate the normal cost for the project. Determine the minimum project completion time with the least cost and calculate the percentage increase in costs. Use the network diagram in (a) above to construct a simple Gantt chart. Note: Time is in Weeks and Cost in USD. Activity Normal time Crash time Normal cost Crash cost...
Chapter 6 Excel Assignment - Managerial Accouting NEED FORMULAS
 The Chapter 6 Form worksheet is to be used to create your own worksheet version of the Review Problem in the text.Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B26 enter the formula "= B17".After entering formulas in all of the cells that contained question marks, verify that the dollar amounts match the numbers in Review Problem 1.The LIFO inventory flow assumption is used throughout the problem.Check your worksheet by...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT