The objective of this financial model is to come up with projections for Cutting Edge B2B Inc. this will help the company to explicitly analyze its position financially in the future. The model as a tool to forecast a variety of financial properties such as sales , total cost of goods , other operating expenses, financial and tax projections, assets, liabilities and equity on shareholders projections. The model allows you to enter your personalized data and prepare a report on the projections. This allows the user to adapt to changes without redoing everything again.
Assumptions for the model
The assumption section contain inputs and variables that may affect the projections such as price/earnings ,sales growth rate, interest rate on short-term debt, interest rate on long-term debt ,dividend payout rate , capital expenditure and tax rate this is pegged on regulations which are subject to change
The base historic data. In this section the user is expected to input financial information from the four previous years , between 2003-2006 this is an extract of previous financial report.
The Income Statement
The user is expected to provide sales data which incorporates sales and cost of goods sold. The model through formula 2 calculates the gross margin. The margin is the difference between sales and the total sum of cost goods inclusive of the administrative costs. The average of the base data is used to determine the sales projections for the years 2007-2009 by subjecting it to the average growth rate through formulas 28-30. The user is expected to enter the following information; sales growth rate and tax rate the model then calculates respective sales and taxes using formulas 17 and 18 respectively. With data for interest rate on long and short term debt, dividends and net income provided by the user the worksheet calculates the dividends payout rate. Holden, (2005).
The balance sheet being a tool for business evaluation is important in this model. The user provides information on net income, sum of all current assets, accumulated depreciation on property, plant and equipment, sales, total liabilities and shareholders' equity. This provides data for base calculations to provide input for modeling the projections. Once the data is provided the worksheet outputs the financial projections for the subsequent years this include: current assets, net property, plant and equipment by subjecting average history PPE and current assets to the average growth rate. Holden,(2008)
Asset turnover section shows the measure of efficiency on asset use by comparing the total revenue to the total assets. This is determined by first getting the receivables turnover and inventory turnover which is done by the formulas 61 and 62 respectively. The market value is expressed in terms of the price to earning which is a ratio of total current assets to total current liabilities. The market to book ratio is calculated to show the earnings per share in the market price. Financial leverage is shown by the ratio of debt to sum of debt and equity. By effective use of borrowed funds to raise revenues the leverage is improved.
Financial Principles Involved
Financial forecasts are an explicit position of a company in the future based on assumption and trend in the past financial statements are prepared to show cash flow expectations, sales and total cost of goods. A financial statement is an explicit account of a company's activities written quantitatively describing the financial status of the entity. The share holders equity basically is the difference between the assets and the liabilities of an entity the financial leverage of company is well visible in its balance sheet. ( Meyer and Weaver , 2000 ) finding its projection in the future
The ratio analysis as tool for forecast access the liquidity, capital base and profitability of an entity. The aspect of market share projections takes into account competition from other players in the industry. Financing must be factored in the financial position of an entity this include all initiatives of the entity to raise funds from its own shareholders or other sources. Shim,Siegel, & Dauber, (2008) show that financial ratios involved in the model return on sales, return on assets and return on equity basically show the profitability of the entity.
Lessons Learned And Observations
The user is not required to input anything in the cells which have excel formulas since the program auto completes them. In case of changes in the assumption cells you do not need to go changing all other variables affected by the change the model automatically updates them. As an example a change in the sales growth rate simply change the value on the cell and this will be propagated to the income statement section as well as the balance sheet. To enhance understandability and the general readability of the worksheet it is necessary to set the cells containing the titles and years fixed . If the floating comments are not included the model would be very hard to understand. Leaving some cells blank leads to an error message output in some other dependent cells.
This can be solved by entering reasonable nonzero values in these cells. The model is not well structured since the user as to go through the whole worksheet looking for a single cell to enter a value. This can be solved by having the model in different worksheets, an input worksheet, a process work sheet to give base inputs for projection calculations and an output worksheet. A key observation is the potential model risk which was not factored in anywhere. Projections only cannot have effect prediction of future economic scenario. Based on assumptions of the tax rates and any other regulation that could directly affect the business or otherwise the magnitude of uncertainty cannot be determined.