You are a staff accountant for a publicly-traded firm. Part of your job is to maintain financial details in Excel of current company projects. At any given time, the company will have hundreds of projects in process; and hundreds more that will be undertaken in the near future. As a result, the company sets aside capital several years in advance for future projects.

You have been given a list of 30 projects that the company plans on starting two years from now. Each project has a seven-digit project code. These projects have been selected from over 10,000 possible opportunities.

To ensure that the company has sufficient capital for these projects, the companys controller has asked you to prepare an investment plan for the company. The investment plan will consist of the following elements:

• A worksheet with the details of the proposed investments, the total estimated project costs, and the necessary capital outlays to cover these project costs. Your worksheet should contain the following elements:

• A table with four columns including the project codes, estimated project costs, project category, and a column for the amounts of projects that will be internally funded (including a total).
  • The project codes are provided in the Excel Quiz Data spreadsheet (under the Project Codes tab).
  • The estimated project amounts are under the Project List tab.
  • Assign each project to one of two project categories:
    1. Projects less than $70,000 are internally funded (INTERNAL) by the business segment undertaking the project.
    2. Projects that are $70,000 or more are corporate projects (CORPORATE) that are entirely funded by corporate headquarters.
  • Since all projects of $70,000 or more are funded by corporate headquarters, you will only need to ensure that enough capital is on hand to fund projects less than $70,000. The fourth column should list the amounts necessary for each project that is internally funded (i.e. if a project is funded by corporate headquarters, the amount in this column should be $0). The sum total of internally funded projects will be the amount of capital necessary to fund these projects in two years.

• Calculate the amount of an annuity payment needed for all internally funded projects assuming that payments are made at the end of each month over the next two years. Assume that the company can earn 12% annual return (compounded monthly) on all invested funds.

• Instead of paying the monthly amount that you calculated in the previous section, your company has decided to make payments of $25,000 at the end of every month, then contribute the difference between the required funds (calculated in column 4) and the amount saved using the monthly $25,000 annuity at the end of the two-year period. Prepare an amortization table containing the details of the monthly $25,000 annuity payments. Below the amortization table, provide the calculation of the difference between the required funds and the sum total of annuity payments (including accumulated interest). All amounts (with the exception of a single manual input for the $25,000 annuity payment) should be automatically calculated based on the figures provided or calculated in the above requirement.

Is there anyone could help with just the excel fomulas????