You are searching about How To Keep One Value Constant In Excel Formula, today we will share with you article about How To Keep One Value Constant In Excel Formula was compiled and edited by our team from many sources on the internet. Hope this article on the topic How To Keep One Value Constant In Excel Formula is useful to you.
Financial Statement Analysis for Sales and Marketing Executives
While it is not necessary to be a qualified accountant to design a Strategy for Sales Perfection, a basic understanding of what is involved in financial analysis is essential for anyone in sales and marketing. It is too enticing, and often too easy, to use “blue skies” thinking in planning sales and marketing activities. It is even easier to spend money without fully realizing the return one is getting for it. It is critical that sales and marketing executives be more disciplined and analytical in the way they go about planning, executing and evaluating the sales and marketing plans and strategy. One way of introducing more discipline into the process is by having a basic understanding of the financial implications of decision making, and how financial measures can be used to monitor and control marketing operations. The purpose of this text is to provide exactly that, and the first chapter deals basically with an introduction to the activities involved in financial analysis.
The Income Statement
The P&L (profit and loss) statement otherwise known as the income statement is illustrated below. This is an abbreviated version as most income statements contain much more detail, for example, expenses are typically listed based on their individual.
G/L ledger account:
The income statement measures a company’s financial performance over a specific accounting period. Financial performance is assessed by giving a summary of how the business incurs its revenues and expenses through both operating and non-operating activities. It also shows the net profit or loss incurred over a specific accounting period, typically over a fiscal quarter or year. The income statement is also known as the “profit and loss statement” or “statement of revenue and expense.”
Sales – These are defined as total sales (revenues) during the accounting period. Remember these sales are net of returns, allowances and discounts.
Discounts – these are discounts earned by customers for paying their bills on tie to your company.
Cost of Goods Sold (COGS) – These are all the direct costs that are related to the product or rendered service sold and recorded during the accounting period.
Operating expenses – These include all other expenses that are not included in COGS but are related to the operation of the business during the specified accounting period. This account is most commonly referred to as “SG&A” (sales general and administrative) and includes expenses such as sales salaries, payroll taxes, administrative salaries, support salaries, and insurance. Material handling expenses are commonly warehousing costs, maintenance, administrative office expenses (rent, computers, accounting fees, legal fees). It is also common practice to designate a separation of expense allocation for marketing and variable selling (travel and entertainment).
EBITDA – earnings before income tax, depreciation and amortization. This is reported as income from operations.
Other revenues & expenses – These are all non-operating expenses such as interest earned on cash or interest paid on loans.
Income taxes – This account is a provision for income taxes for reporting purposes.
The Components of Net Income:
Operating income from continuing operations – This comprises all revenues net of returns, allowances and discounts, less the cost and expenses related to the generation of these revenues. The costs deducted from revenues are typically the COGS and SG&A expenses.
Recurring income before interest and taxes from continuing operations – In addition to operating income from continuing operations, this component includes all other income, such as investment income from unconsolidated subsidiaries and/or other investments and gains (or losses) from the sale of assets. To be included in this category, these items must be recurring in nature. This component is generally considered to be the best predictor of future earnings. However, non-cash expenses such as depreciation and amortization are not assumed to be good indicators of future capital expenditures. Since this component does not take into account the capital structure of the company (use of debt), it is also used to value similar companies.
Recurring (pre-tax) income from continuing operations – This component takes the company’s financial structure into consideration as it deducts interest expenses.
Pre-tax earnings from continuing operations – Included in this category are items that are either unusual or infrequent in nature but cannot be both. Examples are an employee-separation cost, plant shutdown, impairments, write-offs, write-downs, integration expenses, etc.
Net income from continuing operations – This component takes into account the impact of taxes from continuing operations.
Discontinued operations, extraordinary items and accounting changes are all reported as separate items in the income statement. They are all reported net of taxes and below the tax line, and are not included in income from continuing operations. In some cases, earlier income statements and balance sheets have to be adjusted to reflect changes.
Income (or expense) from discontinued operations – This component is related to income (or expense) generated due to the shutdown of one or more divisions or operations (plants). These events need to be isolated so they do not inflate or deflate the company’s future earning potential. This type of nonrecurring occurrence also has a nonrecurring tax implication and, as a result of the tax implication, should not be included in the income tax expense used to calculate net income from continuing operations. That is why this income (or expense) is always reported net of taxes. The same is true for extraordinary items and cumulative effect of accounting changes (see below).
Extraordinary items – This component relates to items that are both unusual and infrequent in nature. That means it is a one-time gain or loss that is not expected to occur in the future. An example is environmental remediation.
The Balance Sheet
The balance sheet provides information on what the company owns (its assets), what it owes (its liabilities) and the value of the business to its stockholders (the shareholders’ equity) as of a specific date. It is called a balance sheet because the two sides balance out. This makes sense: a company has to pay for all the things it has (assets) by either borrowing money (liabilities) or getting it from shareholders (shareholders’ equity).
Assets are economic resources that are expected to produce economic benefits for their owner.
Liabilities are obligations the company has to outside parties. Liabilities represent others’ rights to the company’s money or services. Examples include bank loans, debts to suppliers and debts to employees.
Shareholders’ equity is the value of a business to its owners after all of its obligations have been met. This net worth belongs to the owners. Shareholders’ equity generally reflects the amount of capital the owners have invested, plus any profits generated that were subsequently reinvested in the company.
The balance sheet must follow the following formula:
Total Assets = Total Liabilities + Shareholders’ Equity
Each of the three segments of the balance sheet will have many accounts within it that document the value of each segment. Accounts such as cash, inventory and property are on the asset side of the balance sheet, while on the liability side there are accounts such as accounts payable or long-term debt. The exact accounts on a balance sheet will differ by company and by industry, as there is no one set template that accurately accommodates the differences between varying types of businesses.
Current Assets – These are assets that may be converted into cash, sold or consumed within a year or less. These usually include:
Cash – This is what the company has in cash in the bank. Cash is reported at its market value at the reporting date in the respective currency in which the financials are prepared. Different cash denominations are converted at the market conversion rate.
Marketable securities (short-term investments) – These can be both equity and/or debt securities for which a ready market exists. Furthermore, management expects to sell these investments within one year’s time. These short-term investments are reported at their market value.
Accounts receivable – This represents the money that is owed to the company for the goods and services it has provided to customers on credit. Every business has customers that will not pay for the products or services the company has provided. Management must estimate which customers are unlikely to pay and create an account called allowance for doubtful accounts. Variations in this account will impact the reported sales on the income statement. Accounts receivable reported on the balance sheet are net of their realizable value (reduced by allowance for doubtful accounts).
Notes receivable – This account is similar in nature to accounts receivable but it is supported by more formal agreements such as a “promissory notes” (usually a short-term loan that carries interest). Furthermore, the maturity of notes receivable is generally longer than accounts receivable but less than a year. Notes receivable is reported at its net realizable value (the amount that will be collected).
Inventory – This represents raw materials and items that are available for sale or are in the process of being made ready for sale. These items can be valued individually by several different means, including at cost or current market value, and collectively by FIFO (first in, first out), LIFO (last in, first out) or average-cost method. Inventory is valued at the lower of the cost or market price to preclude overstating earnings and assets.
Prepaid expenses – These are payments that have been made for services that the company expects to receive in the near future. Typical prepaid expenses include rent, insurance premiums and taxes. These expenses are valued at their original (or historical) cost.
Long-Term assets – These are assets that may not be converted into cash, sold or consumed within a year or less. The heading “Long-Term Assets” is usually not displayed on a company’s consolidated balance sheet. However, all items that are not included in current assets are considered long-term assets. These are:
Investments – These are investments that management does not expect to sell within the year. These investments can include bonds, common stock, long-term notes, investments in tangible fixed assets not currently used in operations (such as land held for speculation) and investments set aside in special funds, such as sinking funds, pension funds and plan-expansion funds. These long-term investments are reported at their historical cost or market value on the balance sheet.
Fixed assets – These are durable physical properties used in operations that have a useful life longer than one year.
This includes: Machinery and equipment – This category represents the total machinery, equipment and furniture used in the company’s operations. These assets are reported at their historical cost less accumulated depreciation.
Buildings or Plants – These are buildings that the company uses for its operations. These assets are depreciated and are reported at historical cost less accumulated depreciation.
Land – The land owned by the company on which the company’s buildings or plants are sitting on. Land is valued at historical cost and is not depreciable under U.S. GAAP (generally accepted accounting principles).
Other assets – This is a special classification for unusual items that cannot be included in one of the other asset categories. Examples include deferred charges (long-term prepaid expenses), non-current receivables and advances to subsidiaries.
Intangible assets – These are assets that lack physical substance but provide economic rights and advantages: patents, franchises, copyrights, goodwill, trademarks and organization costs. These assets have a high degree of uncertainty in regard to whether future benefits will be realized. They are reported at historical cost net of accumulated depreciation.
Current liabilities – These are debts that are due to be paid within one year or the operating cycle, whichever is longer. Such obligations will typically involve the use of current assets, the creation of another current liability or the providing of some service.
Bank indebtedness – This amount is owed to the bank in the short term, such as a bank line of credit.
Accounts payable – This amount is owed to suppliers for products and services that are delivered but not paid for.
Wages payable (salaries), rent, tax and utilities – This amount is payable to employees, landlords, government and others.
Accrued liabilities (accrued expenses) – These liabilities arise because an expense occurs in a period prior to the related cash payment. This accounting term is usually used as an all-encompassing term that includes customer prepayments, dividends payables and wages payables, among others.
Notes payable (short-term loans) – This is an amount that the company owes to a creditor, and it usually carries an interest expense.
Unearned revenues (customer prepayments) – These are payments received by customers for products and services the company has not delivered or for which the company has not yet started to incur any cost for delivery.
Dividends payable – This occurs as a company declares a dividend but has not yet paid it out to its owners.
Current portion of long-term debt – The currently maturing portion of the long-term debt is classified as a current liability. Theoretically, any related premium or discount should also be reclassified as a current liability.
Current portion of capital-lease obligation – This is the portion of a long-term capital lease that is due within the next year.
Long-term Liabilities – These are obligations that are reasonably expected to be liquidated at some date beyond one year or one operating cycle. Long-term obligations are reported as the present value of all future cash payments. Usually included are:
Notes payables – This is an amount the company owes to a creditor, which usually carries an interest expense.
Long-term debt (bonds payable) – This is long-term debt net of current portion.
Deferred income tax liability – GAAP (generally accepted accounting principles) allows management to use different accounting principles and/or methods for reporting purposes than it uses for corporate tax fillings to the IRS. Deferred tax liabilities are taxes due in the future (future cash outflow for taxes payable) on income that has already been recognized for the books. In effect, although the company has already recognized the income on its books, the IRS lets it pay the taxes later due to the timing difference. If a company’s tax expense is greater than its tax payable, then the company has created a future tax liability (the inverse would be accounted for as a deferred tax asset).
Pension fund liability – This is a company’s obligation to pay its past and current employees’ post-retirement benefits; they are expected to materialize when the employees take their retirement for structures like a defined-benefit plan. This amount is valued by actuaries and represents the estimated present value of future pension expense, compared to the current value of the pension fund. The pension fund liability represents the additional amount the company will have to contribute to the current pension fund to meet future obligations.
Long-term capital-lease obligation – This is a written agreement under which a property owner allows a tenant to use and rent the property for a specified period of time. Long-term capital-lease obligations are net of current portion.
Statement of Cash Flow
The statement of cash flow reports the impact of a firm’s operating, investing and financial activities on cash flows over an accounting period.
The cash flow statement shows the following:
How the company obtains and spends cash
Why there may be differences between net income and cash flows
If the company generates enough cash from operation to sustain the business
If the company generates enough cash to pay off existing debts as they mature
If the company has enough cash to take advantage of new investment opportunities
Segregation of Cash Flows
The statement of cash flows is segregated into three sections: Operations, investing, and financing.
Cash Flow from Operating Activities (CFO) – CFO is cash flow that arises from normal operations such as revenues and cash operating expenses net of taxes. These include:
Cash inflow: is the positive influx of funds from (1) positive revenue from sale of goods or services (2) interest from indebtedness and (3) dividends from investments.
Cash outflow: is the negative (payments) most commonly categorized as (1) Payments to suppliers (2) payments to employees (3) payments to the government (4) payment to lenders (5) payment for other expenses.
Cash Flow from Investing Activities (CFI) – CFI is cash flow that arises from investment activities such as the acquisition or disposition of current and fixed assets. These include:
Cash inflow is the receipt of cash from (1) the sale or disposition of property, plant or equipment (2) the sale of debt or equity securities or (3) lending income to other entities.
Cash outflow is the payment of (1) the purchase of property plant and equipment, (2) purchase of debt or other equity securities, or (3) lending to other entities,
Cash flow from financing activities (CFF) – CFF is cash flow that arises from raising (or decreasing) cash through the issuance (or retraction) of additional shares, or through short-term or long-term debt for the company’s operations.
Financial Statement Analysis
Analyzing a single period financial statement works well with vertical analysis. On the income statement, percentages represent the correlation of each separate account to net sales. Express all accounts other than net sales as a percentage of net sales. Net income represents the percentage of net sales not used on expenses. For example, if expenses total 69 percent of net sales, net income represents the remaining 31 percent. Vertical analysis performed on balance sheets uses total assets and total liabilities for comparison of individual balance sheet accounts.
Horizontal analysis is the comparison of data sets for two periods. Financial statements users review the change in data much like an indicator. Optimistic analysts look for growth in revenue, net income and assets in addition to reductions in expenses and liabilities. Calculating absolute dollar changes requires the user to subtract the base figure from the current figure. Expressing changes with percentages requires the user to divide the base figure by the current figure, and multiply by 100.
Review of three or more financial statement periods typically represents trend analysis, a continuation of horizontal analysis. The base year represents the earliest year in the data set. Although dollars can represent subsequent periods, analysts commonly use percentages for comparability purposes. Users review statements for patterns of incremental change representing changes in the business in questions. Financial statement improvements include increased income and decreased expenses.
Ratios express a relationship between two more financial statement totals, and compare to budgets and industry benchmarks. Five common categories of ratios exist: liquidity, asset turnover, leverage, profitability and solvency. Reviewing ratios for performance compared with prior periods or industry specific benchmarks provides financial statements users with recognition of strengths and weaknesses.
Analyzing financial statements presents an opportunity for reviewing past data and possibly budgets. However, the data used is historical in nature, indicating it may not be a good representation of the future due to unforeseeable circumstances. Market value of assets and liabilities can be under or overstated significantly leaving statement users unaware of the real value of a balance sheet. Pro forma statements, or forward-looking financial statements, provide estimates at best resulting in speculation.
Cost-volume-profit analysis provides owners and managers with an understanding of the relationship between fixed and variable costs, volume of products manufactured or sold and the profit resulting from sales. The financial relationship includes contribution margin analysis, break-even analysis and operational leverage. Financial statements provide the data to perform cost-volume-profit analysis.
Contribution margin analysis allows managers to look at the percentage of each sales dollar remaining after payment of variable costs, including cost of goods, commissions and delivery charges. Managers and owners use this analysis to help determine the pricing, mix, introduction and removal of products. Contribution margin analysis also aids managers with determining how much incentive to use for sales commissions and bonuses. Comparing each product offered affords the opportunity to look at product profitability and product mix.
Break-even analysis considers the sales volume at which fixed and variable costs are even. Owners and managers must consider two primary figures when calculating the break-even. First, gross profit margin, which is the percentage of sales remaining after payment of variable costs. And fixed costs, including administration, office and marketing. Financial statements provide both sets of data necessary to calculate the break-even volume.
Every business model contains slightly different operating leverage, which compares the amount of fixed costs to sales. Businesses with higher fixed costs will experience a larger multiplier in their operating leverage, indicating less sales growth results in more profit. However, the same is true for losses, where small reductions in sales exponentially increase net losses. Less operating leverage results in less growth of net income.
A financial ratio expresses a mathematical relationship between two or more sets of financial statement data and commonly exhibits the relationship as a percentage. Profitability, solvency, leverage, asset turnover and liquidity comprise the five standard ratio categories. Managers and owners should review the ratios period over period, determining where unfavorable trends exist. After reviewing trends, benchmark ratios against industry standards, which managers can acquire from a variety of sources including industry-specific organizations.
A financial ratio (or accounting ratio) is a relative magnitude of two selected numerical values taken from an enterprise’s financial statements. Often used in accounting, there are many standard ratios used to try to evaluate the overall financial condition of a corporation or other organization. Financial ratios may be used by managers within a firm, by current and potential shareholders (owners) of a firm, and by a firm’s creditors.
Ratios can be used to judge the organization’s “liquidity”, i.e. can it pay its bills, its “leverage”, i.e. how is it financed and its “activities”, i.e. the productivity and efficiency of the organization. Taking liquidity analysis only, this has a bearing on new product planning, marketing budgets and the marketing decisions.
Financial analysis can be used to serve many purposes in an organization but in the area of marketing it has four main functions:
Gauge how well marketing strategy is working (situation analysis)
Evaluate marketing decision alternatives
Develop plans for the future
Control activities on a short term or-day to-day basis.
Understanding a company’s financial performance is critical to developing a solid Strategy for Sales Perfection as well as being an educated and well informed company executive. The purpose of this discussion is to introduce you to the concepts and points of analyzing financial statements and using ratios to develop informed business decisions. The information discussed in this chapter in no way will substitute the job function of your CFO or your CPA.
Financial statements can be quite complex and accounting principles may have significant effect on the way they are reported. Understand that a coordinated dialogue with your accounting staff is critical to obtain clear and concise knowledge of your company financial statements. Financial ratios have limitations and specific uses if interpreted properly. Attention should be given to the following issues when using financial ratios:
A reference point is needed. To be meaningful most ratios must be compared to historical values of the same firm, your company forecasts, or ratios with similar companies.
Most ratios by themselves are not highly meaningful. They should be viewed as indicators, with several of them combined to draw on a conclusion of the purpose of the analysis.
Take into account seasonal factors and business cycles when using financial ratios. Average values should be used when they are available.
Communicate with your accounting department to understand their philosophy and accounting principles.
Sales and Profit Ratio Model
Several profit models have been introduced over the years to gauge the performance of a company and to build a statistical measure to peak performance. We have developed a very simple model that measures four critical areas of performance: gross profit margin %, net profit margin %, RONA – return on net assets, and GMROI – gross margin return on inventory. Earlier in the chapter, we introduced a set of financial statements of which we will use the data from those as part of our illustration of the Sales and profit model.
COGS – cost of goods sold
Operating expenses – net of depreciation, amortization and interest charges
Fixed assets – property plant and equipment net of depreciation
Net Income – after tax income
This model can be set up in an excel spreadsheet to keep track and measure the company’s progress in attaining peak sales performance; monthly tracking should be supported to insure constant improvements. These four ratios are the best measure of a company’s overall sales performance and should be compared to others in your industry to attain top performance standards.
Gross Margin Return on Inventory (GMROI) is a “turn and earn” metric that measures inventory performance based on both margin and inventory turnover. In essence, GMROI answers the question, “For every dollar carried in inventory, how much is earned in gross profit?” GMROI can be calculated at the organization level and, if the proper data is collected at the item level, all the way down to an individual item.
To set a benchmark for the organization, use either current financial statements or budgets for the future. Calculate the GP %, ITO and compute the existing or target GMROI. Measure every appropriate segment against this target. You will identify groups that are exceeding the targets and also those that are not pulling their own weight. While most organizations have some “loss leaders”, it is important to understand which items/groups that are under-performing. Choices are to live with the performance, improve the margin, improve the turnover or in extreme cases, discontinue the poor performing product.
Break-Even Profit Analysis
In business and economics, break-even analysis is a commonly used practice to set pricing multiples or price indexes. Companies need to use break even analysis to determine many relevant factors when designing a strategy for sales perfection. In the linear “cost-volume profit analysis”, the break-even point in terms of units (X) can be directly computed in terms of total revenue (TR) and total costs (TC) as:
The relationship between gross profit margins and sales revenue is approximately a 3.5 to 1 ratio. Simply stated, if you reduce your margin by 1/2 percentage point (.5%) you will need to raise your revenue by 1.7% to maintain the same amount of gross profit. Look at the table above which clearly illustrates this concept, now compare this illustration to your own company. Let’s assume your company has total revenue of $45,000,000, a reduction in margins of a half percent (0.5%) would require you to raise revenue to $48,375,000 to maintain the same amount of income. Your objective as an executive inside your company is to improve your company’s financial position.
Our website winning sales strategy and our book “Strategies for Sales Perfection: In the New Economy provide detailed analysis and explanations of this information along with a plethora of additional resources to allow your company to succeed during these this new economic recovery period.
Strategies for Sales Perfection: In the new Economy is a book written to help company executives develop a plan to support growth. to learn more visit our website at winning sales strategies.com
Video about How To Keep One Value Constant In Excel Formula
You can see more content about How To Keep One Value Constant In Excel Formula on our youtube channel: Click Here
Question about How To Keep One Value Constant In Excel Formula
If you have any questions about How To Keep One Value Constant In Excel Formula, please let us know, all your questions or suggestions will help us improve in the following articles!
The article How To Keep One Value Constant In Excel Formula was compiled by me and my team from many sources. If you find the article How To Keep One Value Constant In Excel Formula helpful to you, please support the team Like or Share!
Rate Articles How To Keep One Value Constant In Excel Formula
Rate: 4-5 stars
Search keywords How To Keep One Value Constant In Excel Formula
How To Keep One Value Constant In Excel Formula
way How To Keep One Value Constant In Excel Formula
tutorial How To Keep One Value Constant In Excel Formula
How To Keep One Value Constant In Excel Formula free
#Financial #Statement #Analysis #Sales #Marketing #Executives