The preparation of a worksheet should be divided into two parts. The first part should be the Input Area wherein all the basic information regarding the project should be entered. The second part would take into account the financial statements i.e. the projected profitability, cash flows and the balance sheets.
The first part is the Input Area wherein all the manual data is input. In no case there should be input figures in the other sheets. Any changes in the input area would have its ultimate effect in all the other sheets by proper linking of the sheets.
Basis and assumptions form an integral part of the input area. This area can broadly be divided into three parts:
- Profitability Assumptions
- Capital Assumptions
- Working Capital Assumptions
Guideline on preparing a financial worksheet for an existing project going in for expansion (Part of a Project Report)
- Installed Capacity
- Capacity Utilization
- Production
- Salable Output
- Sales
- Raw Material Costing
- Expenses
o Power and Fuel
o Wages and Salaries
o Stores and Spares
o Repairs and Maintenance
o Administrative and selling expenses
o Interest rates
o Tax rate and depreciation rates
Capital Assumptions
- Cost of Project
o Land and Site Development
o Building
o Plant and Machinery
o Contingencies
o Other Misc. Fixed Assets
o Preliminary and pre-operative expenses
o Working capital margin
o Means of Finance
§ Equity Capital
§ Unsecured Loan
§ Term Loans
Working Capital Assumptions
- Raw material, WIP and finished goods holding period
- Debtors realization period
- Loans and Advances
o Customers Advance
o Advance to Raw Material Suppliers
o Income Tax Advance
- Creditors
The second sets of statements are basically financial statements prepared by taking into accounts the above assumptions.
Preparation of Projected Profitability:
In case of existing companies going in for expansion there would be two sets of profitability statements, the existing profitability would be entered in the assumptions or input area for existing data and for expansion the profitability would be linked to the assumptions of the input area for expansion.
Preparation of the Cash Flow: The cash flow for the existing company would have to be first entered for the year and then linked for changes in the next years. The expansion cash flow is directly linked to the Cost of Project / Means of Finance and the profitability statements.
Projected Balance Sheet
The balance sheet has to be compulsorily linked from the cash-flow. The linkages are necessary because, any change in the cash flow, the balance sheet has to necessarily change to the effect of it.