Budget Calculations within a Spreadsheet - Assignment Example

Order 171668 Topic: ACCOUNTING & FINANCE FOR MANAGERS (YEAR3 Introduction This is seeks to prepare a case analysis for Jamboree Marquees Limited (JML) since I am acting as a consultant, the report is addressed to the owners of the business. The paper include budget calculations should be contained within a spreadsheet, and showing monthly cash flows. There is also a need to identify and discuss the problems within the business and provide well reasoned recommendations. An explanation how I have translated my recommendations into the figures used in your Cash Flow Forecast will also be made. 2. Analysis and Discussion 2.1 Calculate the breakeven turnover of JML for the previous year. The breakeven turnover of JML for the previous year is L £ 691,500. (See Appendix A on Computation of breakeven point and effect on profitability.) 2.2 Calculate the effect on profitability if the variable costs were reduced by 5%. The effect on profitability if the variable costs were reduced by 5% is an increase in net profit by 12 times. 2. 3.Calculate the effect on profitability if the fixed costs were reduced by 5% The effect on profitability if the fixed costs were reduced by 5% is an increase in profitability by at least more than 24 times. 2.4 Calculate the effect on profits if turnover increased by 5%. The effect on profits if turnover increased by 5% is an increase by profitability by at least more than 35 times. 2. 5 Calculate the effect on profits if the variable costs increased by 5%. The effect on profits if the variable cost is increased by 5% is a decrease in profitability by at least ten times. 2.6. from an examination of the JML case, your answers to questions 1-5 above, and any other information you think is relevant, analyze the financial problems of JML and explain what needs to be done to improve profitability and control over financial matters. (45 marks) The financial problems could either be found in the high cost of operating its business. As of the latest year, the company was almost operating at breakeven point. From the revenues of £ 693,000 its break even sales is compute at £ 691,500. This could be attributed to the big fixed of operating its business. Case facts say that fixed costs of £472,000 came from bank charges and interest, and rent on the Hemel Hempstead premises while variable costs, amounting £220,000 came mostly from the labour cost of erecting the marquees. To address the very low profitability of the business with less than a million pounds turnover, that John was “impressed by the idea of going for market share and then pausing at some point to allow expenses to stabilize and then going for some more growth, once there is some cash in the business.” (Case facts) Between Bruce and John it would appear each has a point. Bruce was conservative type that he felt that there should some way of controlling cost and that growth should not be at the expense of profitability. However the reality in business is that one cannot have profit if there is no revenue and in every revenue, there is use of assets or incurrence of liabilities that would result to expenses. Between Bruce and John there is therefore a choice of just reducing expenses and increasing revenues further but with possibility of increasing expenses. John was realistic in realistic in seeing the solution to the problem by allowing expenses to stabilize and then going for some more growth, once there is some cash in the business. The company needed to have good cash flows in order to be able to supply its working capital requirements. He could be said therefore to be sacrificing growth at the expense of profitability. Since the part of this section is to analyze the financial problem of JML using the answers to the first five questions, the following are the result of the synthesis of the answers to the first five questions. The company’s the breakeven turnover for the current year (2007) was £ 691,500 which is very near its present revenue turnover of £ 693,000 which means that the company was almost operating at break even point. Such break even point is one where there is neither income nor loss because total expenses are equal with total revenues. For planning purposes, the JML’s data were analyzed and determine the effect on profitability if the variable costs were reduced by 5%. What came out was that, the effect on profitability if the variable costs were reduced by 5% is an increase in net profit by 12 times as compared to results of current operation. There is therefore basis also for the claim of Bruce that control of expenses is important. But there is another option. The option this time is to calculate the effect on profitability if the fixed costs were reduced by 5% instead of the variable cost. What came out as a result as to the effect on profitability if the fixed costs there reduction by 5% is that there was a remarkable increase in profitability by at least more than 24 times compared with current year’s present level of operations. As how the doubling of the increase in net income was manifested by just reducing fixed cost by the 5% rather than affecting the same decrease in variable cost is readily discernible by looking at the ratio of variable costs and fixed costs in relation to sales. At the £472,000 fixed costs as compared to £ 220,000 variable cost, one could hardly escape the bigger portion of fixed cost. This therefore means also that further increasing the fixed costs would be more disadvantageous to JML. Fixed cost has the nature of not varying with production in that they are incurred whether JML operates for the month or not. One best example is the payment for directors’ salaries. The directors will still receive their salaries whether JML operates or not. On the other hand, variable costs are incurred only when there is production activity that is being conducted. On the obvious big amount of fixed costs in relation to turnover, in the first instance is therefore logically expected to be at least operating to first cover the big amount of fixed costs. This is also justified by the fact that is a contrition margin for the company which is expressed by taking the difference between the turnover and the variable cost. Said contribution margin justifies production against non production because there is already an added value or benefit from producing more. One of the questions that was asked earlier was to calculate the effect on profits if turnover increased by 5%. The result of this question was shown in the having the effects on profits to an increase by at least more than 35 times compared with the 2007 or current year’s operations. It could be clearly seen that the option of increasing the revenues generates greater advantage over the choice of decreasing either the fixed or variable cost by the same rate of 5%. This could not be surprising because as a general and as expressed earlier, operating by producing goods that are assumed to be sold is presumed to be more advantageous to the compared by assuming all other things being equal. This supported the simple reality also the company has a good contribution margin every production effort. However, this assumption of increasing the production by 5% is premised in the fact that market matches such requirement. This means that there is ready demand or market for the produce goods. This should be made clear since companies could not just be producing without selling the goods or services. In the case of JML, case facts provide that the demand for company’s products and services are seasonal and any plan to increase revenues must be taken with deed realism that such projection could happen. Business plans could be optimistic some times. It must be observed for the past three possible assumptions that the expectation could be favourable to the company. But on the other hand, there is the other part of reality where expectation would not happen as expected because of possible external factor that would bring a company to its knees. The other question therefore asks the effect on profits if the variable costs increased by 5%. The expected result would be bring profitability lower than 2007 results of operation by at least 10 times. Comparing the result of this answer to the effect of variable cost was forecasted to decrease by the same rate of 5%, it is clear the effect is greater if there was decrease in variable cost since the effect was at 24 times increase. What do these figures mean? It means that greater benefit is expected in variable cost decreases than when it increases by the same rate and which seems to argue to support decrease of variable cost. However, decreasing variable cost also may result to forfeiting the increase in greater profitability if revenues are increased and which carries an automatic increase in variable cost. What should be made clear as the message of comparing the answers to the question leaves one to choose the best strategy that could bring the company, the optimum profitability among the options of decreasing, either the variable or fixed cost, increasing revenues that carries the effect of increasing variable cost and the choice of allowing variable costs to increase by 5%. 7. Prepare a forecast cash budget to show how your suggestions in question 6 above could improve cash flow over the next year and explain how this would impact on the profit for the year and strengthen the balance sheet. (45 marks) A forecast cash budget to show how suggestions made in question 6 above could improve cash flow over the next year in terms increase in cash from £1,000 in 2007 to £48,800 in 2008. (See Appendix B.) As to how this would impact on the profit for the year and strengthen the balance sheet could be explained by the fact that cash follows would insure profitability (Meigs and Meigs, 1995) since filling the needs for cash when they are needed will cause the proper generation of revenues that would be enough to cover the necessary expenses needed to carry the business. This could be proved by the fact the return on assets and net profit margin would improve. The first ratio resulted to increase from 1% in 2007 to 11%, while for the second ratio there was a marked increase from almost 0% to 3% in 2008. (See Appendix B.) Better profitability will necessary result to better financial position since more profits will result to better liquidity and better financial leverage. Liquidity means the capacity of the business to meet its currently maturing obligations and therefore short term insolvency is prevented. Better liquidity is reflected from the current ratio improving from 0.47 in 2007 to 1.26 in 2008 ( See Appendix B ) . Better financial leverage indicates making the company less riskier to invest with since the debt to equity ratios will be improved as a result of better profitability. As the latter could happen is not difficult to understand since better earning would bring better stockholders equity which pass by increasing first the retained earnings account which is part of the stockholders equity portion in the balance sheet. Better financial leverage (Brigham and Houston, 2002) is clear from the expected improvement from 46.37 in 2007 to 7.34 in 2008. (See Appendix B) 3. Conclusion: JML has the good the reason to choose the option on increasing turnover over the options decreasing the fixed cost or the variable cost. The big portion of fixed cost is causing the company much head ache that it is always best to keep operating to recover the same. But the company has also options of maximizing the use of fixed assets as they are not fully utilized. The company could product other income by possibly renting out the other part of its fixed assets. The increase in revenues which is expected to result to better profitability , better liquidity and better financial leverage does not mean that the company could that company is bound to operate the same level of expenses. The case facts has shown inefficiencies on its operations that could be further improved by the company while increasing revenues or turnover. JML can have the best of both worlds by maximizing turnover while minimizing cost by property applying the proper management discipline to its operations. 4. Appendices: Appendix A Computation of breakeven and effect on profitability Question 1 Question 2 Question 3 Question 4 Question5 £000 £000 £000 £000 £000 Turnover 693.00 693.00 693.00 727.65 693.00 Less: Variable cost 220.00 209.00 220.00 220.00 231.00 Contribution Margin 473.00 484.00 473.00 507.65 462.00 Less Fixed Costs: 472.00 472.00 448.40 472.00 472.00 Net profit 1.00 12.00 24.60 35.65 (10.00) Break even point in sales Fixed cost £691.5 Contribution margin ratio Appendix B Current vs. Projected Cash flow 2007 2008 £000 £000 Turnover £693.00 £727.65 Less: Direct labour 170.00 Direct materials 10.00 Direct expenses 40.00 220.00 232.85 Contribution £473.00 £ 494.80 Less Fixed Costs: Rent (£7.25 per sq.ft.) 200.00 Rates and service charges 65.00 Bank interest & charges 45.00 Administration 90.00 Selling & marketing 10.00 Depreciation 25.00 Vehicle expenses 15.00 Miscellaneous expenses 18.00 Insurances 4.00 total fixed 472.00 472.00 Net profit £ 1.00 £ 22.80 Add: Depreciation 25.00 Less/Add: Increase (decrease) in current assets 47.30 Increase (decrease) in current liabilities 0.60 Decrease bank Loans 48.70 net cash flow 1.00 47.80 Beg Bal 1.00 End Bal 1.00 48.80 Balance Sheet as at 2007 and 2008 2,007.00 2,008.00 £000 £000 Fixed assets at book value £115.00 £90.00 Current Assets Stock 2.00 2.00 Debtors 25.00 25.50 Cash 1.00 48.80 Total current assets 28.00 76.30 Total Assets 143.00 215.10 Bank overdraft 60.00 60.60 Bank loans 80.00 128.70 Total Liabilities 140.00 189.30 Equity 3.00 25.80 Total Liabilities and SE 283.00 215.10 Financial Ratios Working capital (32.00) 15.70 Current ratio 0.47 1.26 Debt to Equity 46.67 7.34 Return on Assets 0.01 0.11 Net Profit margin 0.00 0.03 5. References: Brigham and Houston (2002) Fundamentals of Financial Management, Thomson South-Western, London, UK Meigs and Meigs (1995) Financial Accounting, Mc-GrawHill, Inc., London, UK Read More