Foundations of Business Computing: Project Income Statement of Maxs Grocery Mart - Assignment Example

Max’s Grocery Mart- Projected Income ment (Enter (Enter Number) (Enter (Enter Faculty Declaration: I, ……………( Student Name), certify that this assignment is my own work, based on my personal study and / or research, and that I have acknowledged all material and sources used in the preparation of this assignment whether they be books, articles, reports, lecture notes, and any other kind of document, electronic or personal communication. I also certify that the assignment has not previously been submitted for assessment in any other course or at any other time in this course, unless by negotiation, and that I have not copied in part or whole or otherwise plagiarized the work of other students and / or persons. I have read the CQU policy on plagiarism and understand its implication. I can produce a hard copy of this assignment within 24 hours if requested. Executive Summary Max’s Grocery Mart is a family-owned business that has been experiencing a stable growth since its inception. Mr. Leroy Feronti, the owner of the business now wants to expand the business by renovating the grocery mart building with a bank loan. To help the application for bank loan, Mr. Feronti has prepared the pro forma financial statements for three years from 2007 onwards. The data of the accounting period 2006 are taken as the base, upon which the projections for 2007, 2008 and 2009 are based. The bank has demanded to prepare Pro forma Income Statement for three years on the basis of the likely growth and other important parameters of the firm. The statements are prepared projecting the future likely growth of the firm. The growth rates estimated are 7.25, 7.75 and 8.25 annually. The projected in come statement exhibits that at 7.25% growth rate, the firm will be able to achieve $ 477175 net income. Similarly, for the year 2008 and 2009, the projected net income is $479400 and $481624 respectively. Mr. Feronti is also asked to prepare projected income statement to reflect the changes in sales at various rates. The income statement is also prepared to reflect the impact on the net income of the increase in workers hourly rate from $12 to $14 and Mr. Feronti’s salary is reduced from 15% to 8% of Gross Profit. The impact of increase in the number of workers from 17 to 19 is also projected through income statement. Table of Contents Introduction 4 Discussion 5 Projected Income Statement for the Year 2007, 2008 and 2009 7 Statement Showing the Impact on Net Profit on account of 9% growth in sales 9 Statement Showing the Impact on Net Profit on account of 9.5% growth in sales 10 Statement Showing the Impact on Net Profit on account of 10% growth in sales 11 Statement Showing the Impact on Net Profit on account of 4% growth in sales 12 Statement Showing the Impact on Net Profit on account of 5% growth in sales 13 Statement Showing the Impact on Net Profit on account of 5.5% growth in sales 14 Pie Chart 15 Statement showing the impact on Net Income of the changes in employee hourly rate and Mr. Feronti’s Salary 16 Statement showing the impact on Net Income of the changes in the no. of employees from 17 to 19 17 Recommendations 18 Appendix 19 Introduction Max’s Grocery Mart is a family-owned business that has been experiencing a stable growth since its inception. Mr. Leroy Feronti, the owner of the business now wants to expand the business by renovating the grocery mart building with a bank loan. To finance the business expansion, Mr. Feronti has approached a bank. The bank wants Mr. Feronti to prepare Pro forma Income Statement for three years to substantiate the loan allocation as a formality. The pro forma financial statements are usually prepared in line with the likely changes in the financial affairs of a firm. Mr. Feronti prepared the statements taking into account the various changes that are likely to incur in his business. First, Income statement is prepared to reflect the growth at 7.25%, 7.75 %and 8% rates. The projected in come statement exhibits that at 7.25% growth rate, the firm will be able to achieve $ 477175 net income. Similarly, for the year 2008 and 2009, the projected net incomes are $479400 and $481624 respectively. Mr. Feronti is also asked to prepare projected income statement to reflect the changes in sales at various rates. At a 9% growth in sales, Mr. Feronti’s net income is reduced from $444919 to $ 243493. However, at all other rates of growth in sales the firm is likely to achieve more income than the current year. Income statement is also prepared to exhibit the effect of the increase in workers hourly rate from $12 to $14 and Mr. Feronti’s salary on the net income. The result is that net income is increased from $ 444919 to $516911. The impact of increase in the number of workers from 17 to 19 on the net income is also projected through pro forma income statement, which shows a decline in the net income to $64118. Discussion In line with the demand made by the Bank for the preparation of Pro forma Income Statement, Mr. Feronti has prepared the Income statement reflecting certain likely changes. The following attempts are made by Mr. Feronti First, Mr. Feronti is asked to prepare the Pro forma Income Statement for three years commencing from 2007 taking data from the income statement of the year 2006. Accordingly, Income statement is prepared to reflect 9%, 9.5% and 10% growth rates. At 9% growth rate, the Income Statement exhibits a decline in the net income from $ 444919 to $243493. At 9.5% and 10%, the net income is increased to $482946 and $498445 respectively. In addition to the above, Pro forma Income statement is prepared to exhibit the impact of change in the sales on the net income. Three possibilities of sales increase are likely to incur, namely 4%, 5% and 5.5%. Projected Income statement is prepared separately to reflect the changes in sales for three years with different sales growth rate. The sales growth rate also affect cost of goods sold and operating expenses as they depend upon the volume of sales and cost of goods sold. The net income shows a decline of net income from $444919 to $444714 at 4% sales growth rate. The other two sales growth rates (5% and 5.55) show positive results. Besides, Mr. Feronti prepares two more Pro forma Income Statements to support his loan application to the bank. In the first case, he prepares Income statement to reflect the impact on net income of the hike in workers’ hourly rate from $12 to $14 along with a reduction in the salary of Mr. Feronti from 15% to 8%. The decision seems to be rational as the net income has increased from $444919 (base period) to $516911. Secondly, Mr. Feronti wants to know what will be the impact of increase in the number of workers from 17 to 19 on the net income. A Pro forma Income statement is prepared accordingly to incorporate such changes in the profitability of the firm. It shows a negative result, which signifies that going for increasing the number of workers will adversely affect the profitability of the firm. All the Pro forma statements are prepared using the Ms Excel Spread sheet program, which is attached along with this report. All calculations have been made using the formula suggested in the program. The pro forma statements are presented in the report separately for each question, which is supported by Excel Spread Sheet as attachment. For the calculation of Total Operating Expenses, wages is inclusive of Mr. Feronti’s salary. Projected Income Statement for the Year 2007, 2008 and 2009 Sales 2006 Growth Rate for 2007 Growth in Absolute terms- 2007 Income Satatment2007 Growth Rate 2008 Growth in Absolute terms 2008 Income Satatment2008 Growth Rate for 2009 Growth in Absolute terms 2009 Income Satatment2009 Deli 240000 Dairy 720000 Canned goods 480000 Frozen goods 800000 Meats 840000 Produce 500000 Dry Goods 360000 Video 60000 Total sales 4000000 7.25 290000 4290000 7.75 310000 4310000 8.25 330000 4330000 Cost of Goods Sold Deli 120000 Dairy 360000 Canned goods 360000 Frozen goods 520000 Meats 420000 Produce 325000 Dry Goods 237600 Video 18000 Total CGS 2360600 7.25 171143.5 2531743.5 7.75 182946.5 2543546.5 8.25 194749.5 2555349.5 Gross Profit 1639400 1758256.5 1766453.5 1774650.5 Operating Expenses Sales and Marketing 220000 General and Administration 350000 Depreciation 20000 Wages 364910 Total Op.Expenses 954910 7.25 69230.975 1024141 7.75 74005.53 1028915.5 8.25 78780.08 1033690.1 Income before Tax 684490 734115.53 737537.98 740960.43 Tax @35 % 239571.5 256940.43 258138.29 259336.15 Net Income 444918.5 477175.09 479399.68 481624.28 (Note: Wages in the Operating Expenses include Mr. Feronti’s Salary) Question No. 1 Statement Showing the Impact on Net Profit on account of 9% growth in sales 2006 Projected Income Statement for 2007 Sales 2006 Growth @9% Sales CGS as % of sales CGS Gross Profit Operating Expenses Net Income before Tax Tax @35% Net Profit after tax Deli 240000 21600 261600 50 130800 Dairy 720000 64800 784800 50 392400 Canned goods 480000 43200 523200 75 392400 Frozen goods 800000 72000 872000 65 566800 Meats 840000 75600 915600 50 457800 Produce 500000 45000 545000 65 354250 Dry Goods 360000 32400 392400 66 258984 Video 60000 5400 65400 30 19620 Total sales 4000000 360000 4360000 2573054 1786946 1412341 374605 131111.75 243493.3 (Note: Wages in the Operating Expenses include Mr. Feronti’s Salary) Question No. 1 Statement Showing the Impact on Net Profit on account of 9.5% growth in sales 2006 Projected Income Statement for 2008 Sales 2006 Growth @ 9.5% Sales CGS as% Sales CGS Gross Profit Op.Expenses Net Profit Tax @35% Net Profit after tax Deli 240000 22800 262800 50 131400 Dairy 720000 68400 788400 50 394200 Canned goods 480000 45600 525600 75 394200 Frozen goods 800000 76000 876000 65 569400 Meats 840000 79800 919800 50 459900 Produce 500000 47500 547500 65 355875 Dry Goods 360000 34200 394200 66 260172 Video 60000 5700 65700 30 19710 Total sales 4000000 380000 4380000 2584857 1795143 1052150 742993 260047.55 482945.5 (Note: Wages in the Operating Expenses include Mr. Feronti’s Salary) Question No. 1 Statement Showing the Impact on Net Profit on account of 10% growth in sales 2006 Projected Income Statement for 2009 Sales 2006 Growth @ 10% Sales CGS as% Sales CGS Gross Profit Op.Expenses Net Profit Tax @35% Net Profit after tax Deli 240000 24000 264000 50 132000 Dairy 720000 72000 792000 50 396000 Canned goods 480000 48000 528000 75 396000 Frozen goods 800000 80000 880000 65 572000 Meats 840000 84000 924000 50 462000 Produce 500000 50000 550000 65 357500 Dry Goods 360000 36000 396000 66 261360 Video 60000 6000 66000 30 19800 Total sales 4000000 400000 4400000 2596660 1803340 1036501 766839 268393.65 498445.4 (Note: Wages in the Operating Expenses include Mr. Feronti’s Salary) Question No. 2 Statement Showing the Impact on Net Profit on account of 4% growth in sales 2006 Projected Income Statement for 2007 Sales 2006 Growth @ 4% Sales CGS as% Sales CGS Gross Profit Op.Expenses Net Profit Tax @35% Net Profit after tax Deli 240000 9600 249600 50 124800 Dairy 720000 28800 748800 50 374400 Canned goods 480000 19200 499200 75 374400 Frozen goods 800000 32000 832000 65 540800 Meats 840000 33600 873600 50 436800 Produce 500000 20000 520000 65 338000 Dry Goods 360000 14400 374400 66 247104 Video 60000 2400 62400 30 18720 Total sales 4000000 160000 4160000 2455024 1704976 1020800 684176 239461.6 444714.4 Question No. 2 Statement Showing the Impact on Net Profit on account of 5% growth in sales 2006 Projected Income Statement for 2008 Sales 2006 Growth @ 5% Sales CGS as% Sales CGS Gross Profit Op.Expenses Net Profit Tax @35% Net Profit after tax Deli 240000 12000 252000 50 126000 Dairy 720000 36000 756000 50 378000 Canned goods 480000 24000 504000 75 378000 Frozen goods 800000 40000 840000 65 546000 Meats 840000 42000 882000 50 441000 Produce 500000 25000 525000 65 341250 Dry Goods 360000 18000 378000 66 249480 Video 60000 3000 63000 30 18900 Total sales 4000000 200000 4200000 2478630 1721370 1026500 694870 243204.5 451665.5 Question No. 2 Statement Showing the Impact on Net Profit on account of 5.5% growth in sales 2006 Projected Income Statement for 2009 Sales 2006 Growth @ 5.5% Sales CGS as% Sales CGS Gross Profit Op.Expenses Net Profit Tax @35% Net Profit after tax Deli 240000 13200 253200 50 126600 Dairy 720000 39600 759600 50 379800 Canned goods 480000 26400 506400 75 379800 Frozen goods 800000 44000 844000 65 548600 Meats 840000 46200 886200 50 443100 Produce 500000 27500 527500 65 342875 Dry Goods 360000 19800 379800 66 250668 Video 60000 3300 63300 30 18990 Total sales 4000000 220000 4220000 2490433 1729567 1029350 700217 245075.95 455141.1 Question No. 3 Pie Chart Question No.4 Statement showing the impact on Net Income of the changes in employee hourly rate and Mr. Feronti’s Salary Items sales % of sales CGS Gross Profit Operating Expenses Net Income before Tax Tax @35% Net Profit after tax Deli 240000 50 120000 Dairy 720000 50 360000 Canned goods 480000 75 360000 Frozen goods 800000 65 520000 Meats 840000 50 420000 Produce 500000 65 325000 Dry Goods 360000 66 237600 Video 60000 30 18000 Total sales 4000000 2360600 1639400 844152 795248 278336.8 516911.2 Question No.5 Statement showing the impact on Net Income of the changes in the no. of employees from 17 to 19 Item sales % of sales CGS Gross Profit Operating Expenses Net Income before Tax Tax @35% Net Profit after tax Deli 240000 50 120000 Dairy 720000 50 360000 Canned goods 480000 75 360000 Frozen goods 800000 65 520000 Meats 840000 50 420000 Produce 500000 65 325000 Dry Goods 360000 66 237600 Video 60000 30 18000 Total sales 4000000 2360600 1639400 1386910 252490 88371.5 164118.5 Recommendations After the preparation of various Pro forma statements, the following situations are advisable for the firm. 1. Growth rate of 9.5%, which indicates an increase of net income to $482946 2. Growth rate of 10%, which results in an increase of net income to $ 498445 3. Sales growth rate of 5 % and 5.5% seem to increase profitability 4. The reduction in the salary of Mr. Feronti from 15% to 8% along with an increase in workers’ hourly rate shows an increase in net income Appendix Read More