Instructions: Attached below is a spreadsheet that the author of your text set up to organize, calculate, and manipulate the information for a strong and accurate cash flow projection for chapter examples 14-1 through 14-5. This is assignment is a group activity and is intended to be completed by groups of two. However, students who cannot find a groupmate can complete this asssignment individually. Using the author's spreadsheet as a benchmark, set up your own group spreadsheet to perform an accurate cash flow projection on the following company. Determine the minimum balance of the bank account for each month and provide an analysis statement determining if the company has sufficient funds for the next year. Assignment: Prepare a cash flow projection for Proper-Planning Construction. PPC has two contracts currently under contract for next year and anticipates picking up three more projects during the year. For the first project, Southland Terrace, the project owner is holding $24,200 in retention from this year's payments and will continue to hold 10% retention on all payments during the next year. The construction company is holding $9,400 of retention on its subcontractors from this year's payments. The retention for this project is expected to be released in June. The estimated bills to the project's owner and construction costs for the first project are shown below. PROJECT #1 - SOUTHLAND TERRACE Month Dec. Jan. Feb. March Total Bill to Owner ($) 126,800 52,900 88,000 25,700 293,400 Material ($) 25,500 9,900 16,600 5,000 57,000 Costs Labor ($) Sub. ($) 24,900 51,200 10,100 22,400 15,600 32,200 5,100 10,400 55,700 116,200 Other ($) 10,200 4,100 6,800 2,000 23,100 The second project, Winchester Night, started last December and the owner has yet to make a payment. The project's owner will hold 10% retention on this project and is expected to release the retention in August. PPC will withhold retention from the payments to its subcontractors. The estimated bills to the project's owner and construction costs for the second project are shown below. Project #2 - Winchester Night Costs Month Bill to Owner ($) Material ($) Labor ($) Sub. ($) Other ($) Dec. 11,900 3,400 3,700 1,400 1,300 Jan. 33,300 10,300 9,400 4,300 4,000 Feb. 42,200 11,100 10,700 4,900 4,400 March 44,600 14,700 14,500 6,300 5,400 April 70,000 21,500 19,100 9,500 7,600 May 32,200 8,900 8,800 3,600 3,100 June 45,300 12,800 12,800 5,400 5,000 July 15,200 4,600 4,100 1,900 1,700 Total 294,700 87,300 83,100 37,300 32,500 The third project, Waning Technology, is expected to start in April. The project's owner will hold 5% retention on the project and PPC will withhold retention from the payments to its subcontractors. The retention for this project is expected to be released September. The estimated bills to the project owner and construction costs for the third project are shown below. Project #3 - Waning Technology Costs Month Bill to Owner ($) Material ($) Labor ($) Sub. ($) Other ($) April 36,300 6,400 9,400 6,300 4,000 50,800 9,400 12,400 7,600 5,500 June 61,800 10,800 15,100 10,300 6,500 July 36,100 7,400 8,600 6,000 3,900 Total 185,000 34,000 45,500 30,200 19,900 May The fourth project, Gaining Ground Commons, is expected to start in August. The project's owner will hold 10% retention on the project and PPC will withhold retention from the payments to its subcontractors. The retention for this project will not be released this year. The estimated bills to the project's owner and construction cost are below. Project #4-Gaining Ground Commons Costs Month Bill to Owner ($) Material ($) Labor ($) Sub. ($) Other ($) Aug. 19,000 4,600 5,300 2,900 1,800 Sept. 53,200 16,500 16,200 8,700 5,000 Oct. 61,600 16,100 21,500 9,800 6,900 Nov. 73,700 19,900 21,300 11,800 7,600 Dec. 115,400 32,700 33,400 17,000 10,800 Total 322,900 89,800 97,700 50,200 32,100 The fifth project, Rolling Dough Hills, is expected to start in November. The project's owner will hold 5% retention on the project and PPC will withhold retention from the payments to its subcontractors. The retention for this project will not be released this year. The estimated bills to the project's owner and construction costs are below. Project #5-Rolling Dough Hills Costs Month Bill to Owner ($) Material (5) Labor (5) Sub. ($) Other ($) Nov. 36,000 3,650 11,200 11,100 0 Dec. 105,200 31,560 35,000 22,000 7,800 Total 141,200 36,210 46,200 33,100 7,800 The company's fiscal and tax years start in January and end in December. The company uses the percentage-of-completion method of accounting. Revenues are received before the end of the month after the month the company billed the client. Labor costs are paid weekly. Material bills are paid in full when the payment is received from the owner. Subcontractor bills are paid - less retention-when payment is received from the owner. The retention withheld from the subcontractor payments is based on the same retention rate that is held by the project's owner and will be paid to the subcontractor when the owner releases the retention. Other costs are paid at the end of the month that the costs are incurred. Include in the general overhead budget the following: The budget for advertising is to be 0.5% of revenues. The budget for promotions is to include $1,000 in July for a company picnic, $200 in December for Christmas cards and gifts, and $1.300 in December for a company Christmas party. The monthly fuel and maintenance cost for the company vehicle driven by the owner, Mr. PlanMore is estimated to be $225 per month. In April, the company plans on purchasing a new copler for $2.000. The new copier will be subject to the 200% declining balance depreciation method using the midyear convention and a five-year life. The company employs three workers: the owner, an estimator, and a secretary/bookke per. The owner is paid $5,000 per month. The estimator is paid $15.00 per hour and works an average of 2 hours per week. The secretary/bookkeeper is paid $13.50 per hour and works an average of 45 hours per week. All of the hourly employees are paid for 52 weeks of work per year. Time and-a-half must be paid to hourly employees for work over 40 hours per week. The company contributes $175 per month per employee-including the owner-for health insurance. They also deposit $0.50 in an employee's 401 (k) account for every dollar the employee deposits. The maximum the company would deposit is 3% of an employee's wages. The company's owner is included in this match. Historically, the employees have taken full advantage of this benefit. The current social security rate is 6.2% to $106,800 of wages per employee. The current Medicare rate is 1.45%. The company's FUTA rate is 0.8% on the first $7,000 of wages per employee and their SUTA rate is 2.5% on the first $9,000 of wages per employee. The company is charged 0.45% of revenues, 0.65% of wages for hourly office employees, and 1.5% of wages for salaried office employees for general liability insurance. In January the company pays $25 for a business license. It is anticipated that office supplies will cost $150 per month. Rent for the office space is $425 month and includes sewer and water. Office utilities are expected to run as follows: $150 per month for power in June, July, and August and $100 per month during the remaining months of the year, and $130 per month for natural gas during November, December, January, and February and $30 per month during the remaining months of the year. It is anticipated that the company will spend $50 per month for postage and $100 per month for janitorial services. The estimated telephone costs are $125 per month for telephone and long-distance service and $100 per month to provide mobile phone service for the owner. In December the company plans on making a $5,000 charitable contribution to a Seminole State College. In April, the company must pay $500 for its annual plan room membership. The company plans on spending $250 at the first of each quarter for accounting services to close the previous quarter's books and an additional $500 in April for tax services. The estimated cost of meals and entertainments is $50 per week. Bank fees are $50 per month. Allow $75 per month for miscellaneous expenses. Assume that all overhead costs-except labor-are paid at the end of the month they occur. Labor will be paid throughout the month that the costs occur. Also, assume that all of the months are the same length-four and one-third weeks. In addition to the above costs, the company depreciation from previous year's purchases of office equipment is $3,000 per year. The company has an outstanding loan with a payment of$575 per month. Of the monthly loan payments, $3,730 will be in the form of interest and the remaining will reduce the outstanding loan balance. The surplus cash from each month will be placed in a bank account earning a monthly interest rate of 0.45%. Negative cash flows will be covered by funds in this bank account. At the beginning of the year, the balance for the bank account will be $55,000. Inasmuch as the company is an Scorporation, the estimated income taxes for the year will be distributed to the company's owners at the end of the year. The disbursement will be based on a marginal tax rate of 25%. The underbillings (costs and profits in excess of billings) increased by $7,200 and the overbillings (billings in excess of cost and profits) increased by $4,500. Does the company have sufficient funds for the next year? If not, what changes can the company make to ensure that it has sufficient funds? Instructions: Attached below is a spreadsheet that the author of your text set up to organize, calculate, and manipulate the information for a strong and accurate cash flow projection for chapter examples 14-1 through 14-5. This is assignment is a group activity and is intended to be completed by groups of two. However, students who cannot find a groupmate can complete this asssignment individually. Using the author's spreadsheet as a benchmark, set up your own group spreadsheet to perform an accurate cash flow projection on the following company. Determine the minimum balance of the bank account for each month and provide an analysis statement determining if the company has sufficient funds for the next year. Assignment: Prepare a cash flow projection for Proper-Planning Construction. PPC has two contracts currently under contract for next year and anticipates picking up three more projects during the year. For the first project, Southland Terrace, the project owner is holding $24,200 in retention from this year's payments and will continue to hold 10% retention on all payments during the next year. The construction company is holding $9,400 of retention on its subcontractors from this year's payments. The retention for this project is expected to be released in June. The estimated bills to the project's owner and construction costs for the first project are shown below. PROJECT #1 - SOUTHLAND TERRACE Month Dec. Jan. Feb. March Total Bill to Owner ($) 126,800 52,900 88,000 25,700 293,400 Material ($) 25,500 9,900 16,600 5,000 57,000 Costs Labor ($) Sub. ($) 24,900 51,200 10,100 22,400 15,600 32,200 5,100 10,400 55,700 116,200 Other ($) 10,200 4,100 6,800 2,000 23,100 The second project, Winchester Night, started last December and the owner has yet to make a payment. The project's owner will hold 10% retention on this project and is expected to release the retention in August. PPC will withhold retention from the payments to its subcontractors. The estimated bills to the project's owner and construction costs for the second project are shown below. Project #2 - Winchester Night Costs Month Bill to Owner ($) Material ($) Labor ($) Sub. ($) Other ($) Dec. 11,900 3,400 3,700 1,400 1,300 Jan. 33,300 10,300 9,400 4,300 4,000 Feb. 42,200 11,100 10,700 4,900 4,400 March 44,600 14,700 14,500 6,300 5,400 April 70,000 21,500 19,100 9,500 7,600 May 32,200 8,900 8,800 3,600 3,100 June 45,300 12,800 12,800 5,400 5,000 July 15,200 4,600 4,100 1,900 1,700 Total 294,700 87,300 83,100 37,300 32,500 The third project, Waning Technology, is expected to start in April. The project's owner will hold 5% retention on the project and PPC will withhold retention from the payments to its subcontractors. The retention for this project is expected to be released September. The estimated bills to the project owner and construction costs for the third project are shown below. Project #3 - Waning Technology Costs Month Bill to Owner ($) Material ($) Labor ($) Sub. ($) Other ($) April 36,300 6,400 9,400 6,300 4,000 50,800 9,400 12,400 7,600 5,500 June 61,800 10,800 15,100 10,300 6,500 July 36,100 7,400 8,600 6,000 3,900 Total 185,000 34,000 45,500 30,200 19,900 May The fourth project, Gaining Ground Commons, is expected to start in August. The project's owner will hold 10% retention on the project and PPC will withhold retention from the payments to its subcontractors. The retention for this project will not be released this year. The estimated bills to the project's owner and construction cost are below. Project #4-Gaining Ground Commons Costs Month Bill to Owner ($) Material ($) Labor ($) Sub. ($) Other ($) Aug. 19,000 4,600 5,300 2,900 1,800 Sept. 53,200 16,500 16,200 8,700 5,000 Oct. 61,600 16,100 21,500 9,800 6,900 Nov. 73,700 19,900 21,300 11,800 7,600 Dec. 115,400 32,700 33,400 17,000 10,800 Total 322,900 89,800 97,700 50,200 32,100 The fifth project, Rolling Dough Hills, is expected to start in November. The project's owner will hold 5% retention on the project and PPC will withhold retention from the payments to its subcontractors. The retention for this project will not be released this year. The estimated bills to the project's owner and construction costs are below. Project #5-Rolling Dough Hills Costs Month Bill to Owner ($) Material (5) Labor (5) Sub. ($) Other ($) Nov. 36,000 3,650 11,200 11,100 0 Dec. 105,200 31,560 35,000 22,000 7,800 Total 141,200 36,210 46,200 33,100 7,800 The company's fiscal and tax years start in January and end in December. The company uses the percentage-of-completion method of accounting. Revenues are received before the end of the month after the month the company billed the client. Labor costs are paid weekly. Material bills are paid in full when the payment is received from the owner. Subcontractor bills are paid - less retention-when payment is received from the owner. The retention withheld from the subcontractor payments is based on the same retention rate that is held by the project's owner and will be paid to the subcontractor when the owner releases the retention. Other costs are paid at the end of the month that the costs are incurred. Include in the general overhead budget the following: The budget for advertising is to be 0.5% of revenues. The budget for promotions is to include $1,000 in July for a company picnic, $200 in December for Christmas cards and gifts, and $1.300 in December for a company Christmas party. The monthly fuel and maintenance cost for the company vehicle driven by the owner, Mr. PlanMore is estimated to be $225 per month. In April, the company plans on purchasing a new copler for $2.000. The new copier will be subject to the 200% declining balance depreciation method using the midyear convention and a five-year life. The company employs three workers: the owner, an estimator, and a secretary/bookke per. The owner is paid $5,000 per month. The estimator is paid $15.00 per hour and works an average of 2 hours per week. The secretary/bookkeeper is paid $13.50 per hour and works an average of 45 hours per week. All of the hourly employees are paid for 52 weeks of work per year. Time and-a-half must be paid to hourly employees for work over 40 hours per week. The company contributes $175 per month per employee-including the owner-for health insurance. They also deposit $0.50 in an employee's 401 (k) account for every dollar the employee deposits. The maximum the company would deposit is 3% of an employee's wages. The company's owner is included in this match. Historically, the employees have taken full advantage of this benefit. The current social security rate is 6.2% to $106,800 of wages per employee. The current Medicare rate is 1.45%. The company's FUTA rate is 0.8% on the first $7,000 of wages per employee and their SUTA rate is 2.5% on the first $9,000 of wages per employee. The company is charged 0.45% of revenues, 0.65% of wages for hourly office employees, and 1.5% of wages for salaried office employees for general liability insurance. In January the company pays $25 for a business license. It is anticipated that office supplies will cost $150 per month. Rent for the office space is $425 month and includes sewer and water. Office utilities are expected to run as follows: $150 per month for power in June, July, and August and $100 per month during the remaining months of the year, and $130 per month for natural gas during November, December, January, and February and $30 per month during the remaining months of the year. It is anticipated that the company will spend $50 per month for postage and $100 per month for janitorial services. The estimated telephone costs are $125 per month for telephone and long-distance service and $100 per month to provide mobile phone service for the owner. In December the company plans on making a $5,000 charitable contribution to a Seminole State College. In April, the company must pay $500 for its annual plan room membership. The company plans on spending $250 at the first of each quarter for accounting services to close the previous quarter's books and an additional $500 in April for tax services. The estimated cost of meals and entertainments is $50 per week. Bank fees are $50 per month. Allow $75 per month for miscellaneous expenses. Assume that all overhead costs-except labor-are paid at the end of the month they occur. Labor will be paid throughout the month that the costs occur. Also, assume that all of the months are the same length-four and one-third weeks. In addition to the above costs, the company depreciation from previous year's purchases of office equipment is $3,000 per year. The company has an outstanding loan with a payment of$575 per month. Of the monthly loan payments, $3,730 will be in the form of interest and the remaining will reduce the outstanding loan balance. The surplus cash from each month will be placed in a bank account earning a monthly interest rate of 0.45%. Negative cash flows will be covered by funds in this bank account. At the beginning of the year, the balance for the bank account will be $55,000. Inasmuch as the company is an Scorporation, the estimated income taxes for the year will be distributed to the company's owners at the end of the year. The disbursement will be based on a marginal tax rate of 25%. The underbillings (costs and profits in excess of billings) increased by $7,200 and the overbillings (billings in excess of cost and profits) increased by $4,500. Does the company have sufficient funds for the next year? If not, what changes can the company make to ensure that it has sufficient funds