Introduction For this assessment, you are required to provide evidence of your ability to: • create a simple spreadsheet budget to capture and monitor information • use the budget spreadsheet to produce a report on expenditure in accordance with organisational policies and procedures • modify a contingency plan • collect and analyse financial data; and make recommendations to improve existing processes • create a plan to implement and monitor solutions. Assessment Description 32026B/03 Case Study Case Study Company overview Babies on the Go is a pram manufacturer based in Perth, Western Australia. The company produces prams which it sells to retailers in the domestic Australian market. The senior management structure of the company appears below. Person Position Jan Goodwin CEO Henry George Managing Director Anita Tran CFO Anna Peters Operations General Manager George Floro Senior Accountant Sam Georges Sales General Manager Brett Price Production Manager Taylor Jones HR Manager According to company's strategic plans, the company aims to achieve a net profit before tax of $1,000,000. The main risks to this goal are: 1. poor sales due to economic downturn 2. increase in expenses such as wage expenses. In addition, Babies on the Go is considering manufacturing overseas to take advantage of reduced costs. The company is also considering diversifying its product range to reduce exposure to poor sales of one product. Part A 1. The Managing Director, Henry George, has asked you to implement a process to monitor expenditure and income. Henry has asked you to prepare a spreadsheet to capture and compare actual income and expenditure to budgeted figures. Your spreadsheet must contain columns for each of the four quarters of the financial year. You are required to gather data from the relevant managers to complete a Budget Variation Report. The report should meet the organisational requirements in policies and procedures and contain: • columns to show actual account values • dollar variation • percentage variation • favourable/unfavourable status. a. Implement a budgetary tool that enables you to connect and collaborate with team members to monitor actual expenditure and control costs across the team. Download the Excel template and label the columns so that it conforms to the organisational requirements. b. Monitor the expenditure and cost data in Appendix 1 – Budget data – Actuals and identify cost variations and expenditure overruns. c. Review and monitor the current Contingency Plan and modify and implement to maintain financial objectives in Appendix 2 – Case Study – Babies on the Go Pty Ltd. d. Identify two major variances and potential causes and explain what changes they have made to the contingency plan and the reason(s) why. (150-250 words)   Part B According to the company's strategic plans, the company had aimed to achieve a net profit before tax of $1,000,000. Actual figures showed the company fell about $175,000 short of this goal. After successful labour cost-cutting measures and improved sales team performance, the company aims to generate a net profit before tax of $1,200,000 from Australian operations alone. This year, in addition to Australian operations, the company is considering manufacturing overseas to take advantage of reduced costs. The company is also considering diversifying its product range to reduce exposure to poor sales of one product. The board of directors of Babies on the Go feels that more cash will be needed to make investments to achieve strategic aims. One chief risk to plans is bad debt and poor cashflow due to large and unsustainable trade debtor balances quarter-by-quarter. Note: Strategic plans dictate that Babies on the Go must reduce its debt levels and so additional financing to increase cashflow is not an option. As the Manager, you are aware that one risk to the strategic plan of Babies on the Go is bad debt and poor cashflow due to large trade debtors balances. You will need to consider the following: ● according to its policies, Babies on the Go offers 30-day payment terms to debtors ● currently, Babies on the Go does not train sales staff on credit terms ● there is currently no enforcement of credit terms ● warehousing of stock is expensive at its current leased premises ● many prams need to be thrown out if parts rust; this problem exacerbates the problem of waste expense. You have the following information from the Statement of Financial Position and current ledger accounts in the electronic accounting system. Current ledger accounts Account $ Trade debtors 362,500 Trade creditors 80,000 Opening stock 100,000 Closing stock 300,000 Purchases 1,000,000 Sales 2,900,000 1. Review the Appendix 3 – Statement of Financial Performance to calculate: The average debtor days The average creditor days The average stock turnover Note: Show calculations and results on your response document for this assessment task. 2. Consider the existing Babies on the Go ageing debtor's budget Appendix 4 – Aging debtor's budget. a. Make two written recommendations for improvement to existing financial management processes to improve cashflow ( 250 words). To support your recommendations: refer to data sources, organisational needs, and analytical techniques, for example: I. statement of financial performance II. ledger accounts III. Case Study information IV. ageing debtors V. ratios. b. List three sources of financial information which assisted in making your recommendations. 3. Soon, you will need to prepare a business activity statement (BAS) for the first quarter on 2014/15. a. Using the figures below, complete the GST budget to anticipate the GST liability. Enter your calculation in the table below. GST Budget July August September Budgeted cash receipts incurring GST: Cash sales 20,000 10,000 10,000 Cash revenue (besides sales) 0 0 0 Cash receipts from sale of assets (not stock) 0 0 0 Total receipts for GST 20,000 10,000 10,000 Budgeted non-cash receipts incurring GST: Debtors sales 180,000 230,000 150,000 Total non-cash receipts: 180,000 230,000 150,000 Total budgeted receipts incurring GST 200,000 240,000 160,000 Budgeted cash payments incurring GST Cash purchases of stock 0 0 0 Cash expenses 4,300 5,200 5,250 Total cash receipts incurring GST 4,300 5,200 5,250 Budgeted credit payments incurring GST Credit purchases of stock incurring GST 25,000 30,000 25,000 Credit purchases of assets (besides stock) 4,300 5,200 5,250 Total cash payments incurring GST 29,300 35,200 30,250 Total budgeted cash payments incurring GST 33,600 40,400 35,500 GST cash budget calculations a) Cash receipts b) Cash payments c) GST liability 4. Choose one of the recommendations you developed in 'Part B, Question 2', and develop an implementation plan using the Appendix 5 – Implementation Plan template. You will need to include the relevant activities, monitoring tools, timelines and accountabilities. a. Draft an email to your team members, requesting them to implement the attached implementation plan as per the specified timelines. Appendices Appendix 1 – Budget data – Actuals FY Actual Q1 Actual Q2 Actual Q3 Actual Q4 Actual REVENUE Sales 3,100,000 2,900,000 700,000 600,000 1,000,000 900,000 700,000 800,000 700,000 600,000 Cost of Goods Sold 400,000 380,000 100,000 95,000 100,000 95,000 100,000 95,000 100,000 95,000 Gross Profit 2,422,500 2,247,500 532,500 440,000 825,000 732,500 532,500 640,000 532,500 440,000 EXPENSES General and Administrative Expenses Travel 20,000 22,000 5,000 4,000 5,000 6,000 5,000 6,000 5,000 6,000 Legal Fees 5,000 4,500 1,250 1,050 1,250 1,150 1,250 1,150 1,250 1,150 Bank Charges 600 700 150 200 150 200 150 150 150 150 Office Supplies 5,000 4,000 1,250 1,000 1,250 1,000 1,250 1,000 1,250 1,000 Postage and Printing 400 500 100 125 100 125 100 125 100 125 Dues and Subscriptions 500 600 125 150 125 150 125 150 125 150 Telephone 10,000 11,200 2,500 2,800 2,500 2,800 2,500 2,800 2,500 2,800 Repairs and Maintenance 50,000 45,000 25,000 20,000 25,000 25,000 Payroll Tax 25,000 25,000 6,250 6,250 6,250 6,250 6,250 6,250 6,250 6,250 Marketing Expenses Advertising 200,000 208,000 50,000 52,000 50,000 56,000 50,000 50,000 50,000 50,000 Employment Expenses Commissions 77,500 72,500 17,500 15,000 25,000 22,500 17,500 15,000 17,500 15,000 Direct wages fixed 200,000 200,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 Superannuation 45,000 45,000 11,250 11,250 11,250 11,250 11,250 11,250 11,250 11,250 Wages and Salaries 500,000 500,000 125,000 125,000 125,000 125,000 125,000 125,000 125,000 125,000 Staff Amenities 20,000 23,000 5,000 6,000 5,000 5,000 5,000 6,000 5,000 6,000 Occupancy Costs Electricity 40,000 38,000 10,000 8,000 10,000 10,000 10,000 10,000 10,000 10,000 Insurance 100,000 100,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 Rates 100,000 100,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 Rent 200,000 200,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 Water 30,000 35,000 7,500 10,000 7,500 10,000 7,500 7,500 7,500 7,500 Waste Removal 50,000 60,000 12,500 15,000 12,500 15,000 12,500 15,000 12,500 15,000 TOTAL EXPENSES 1,401,500 1,422,500 362,875 362,825 362,875 374,925 337,875 342,375 337,875 342,375 NET PROFIT (BEFORE INTEREST AND TAX) 1,021,000 825,000 169,625 77,175 462,125 357,575 194,625 297,625 194,625 97,625 Income Tax Expense (25%Net) 255,250 206,250 42,406 19,294 115,531 89,394 48,656 74,406 48,656 24,406 NET PROFIT AFTER TAX 765,750 618,750 127,219 57,881 346,594 268,181 145,969 223,219 145,969 73,219 Appendix 2 – Case Study: Babies on the Go Pty Ltd It has come to the attention of the Managing Director, Henry George, that due to the current economic climate, sales volume may be 20% below target this financial year. Henry is worried that this may severely impact profit projections. The company can accept as much as a 10% variance in profit projections; however, more than this could severely affect the company's ability to pay obligations and invest. Reliable data to determine whether the risk has eventuated should be available by midway through the second quarter (Q2), when sales data for the company's product are in. Consider the contingency plan and the implementation plan for the contingency below. You have already implemented a portion of the contingency plan, namely the monitoring of budget performance in the variation report you have prepared. You should now analyse the report to determine the effectiveness of the contingency plan and its implementation. You have received the following feedback from team members: • full-time workers and sales people resentful of time-wasting and distracting contract employees • overtime is not used but employees are resentful of suggestion it might not be approved if needed • training suited the needs of many sales team members but was not relevant to about half • sales team members were happy with the incentives program and tried hard to make sales in the third quarter (Q3); however, they were also resentful at the threatening tone of emails and soon lost enthusiasm • effect of one-day training wearing off • 50% of direct wages costs are attributable to short-term contract employees whose contracts have expired and who are no longer needed • employees concerned about lack of attention paid to wastage: water; electricity, paper; raw materials • employees feel left out of budgetary decision-making in general. The Managing Director would like you to submit a revised Contingency Plan and contingency plan implementation to bring income and expenses under more effective control. Contingency plan Contingency Plan Company name: Babies on the Go Person developing the plan: Name: Henry George Position: Managing Director Risk identified: Profit for FY more than 10% less than budgeted Strategies/activities to minimise the risk By when By whom Produce quarterly variation reports to identify income/expenditure and profit shortfalls over 10%. Q2 PR Implement sales training/coaching. Q2 PR Implement incentives program. Q2 PR Reduce overtime. Q2 PR Contingency implementation plan Risk identified: Profit for FY more than 10% less than budgeted Activity Monitoring activity and date Person(s) responsible Monitor variance. Completion of report: Q2. PR Analysis of report to identify issues. Management report: Q2. PR Email to warn employees of risk to jobs. Monitoring of variation report results: Q4. PR Email to announce rise of commission from 2% to 2.5%. Monitoring of variation report results: Q3. PR Email to inform employees that overtime will no longer be approved. Monitoring of variation report results: Q3. PR Email to inform employees of mandatory sales skills training: set program. Monitoring of variation report results: Q3. PR Mandatory training conducted. Monitoring of variation report results: Q3. PR Budget preparations ● The business plan will set the key parameters for all financial budgeting. ● Variations to the business plan must be approved by the CEO and senior management strategic committee. ● Prior period results are to be analysed to identify the profit level of cost centres, identify correlations between financial statistics and to set key performance indicators and benchmarks for future budgets. ● The budget planning committee will meet prior to budgets being developed and agree on budget parameters. The committee will consist of all department managers plus the CEO and finance manager. ● A CAPEX budget will be developed from the approved business plan. ● A detailed sales budget must be completed before completing the profit budget for the year. ● A cashflow budget covering the first three months will be prepared after the profit budget is completed. ● A master budget including profit projections will be completed from which cost centre allocations will be made. ● Budget notes that contain all the assumptions used in the budgets should accompany the master budget or be made available on a separate document. Where possible, the notes should justify the basis on which the estimates were made. ● Overheads (non-direct expenses) will be apportioned across the cost centres equally. Exceptions need to be negotiated with relevant authorities. ● All expenses and income will be spread equally throughout the year unless otherwise required by business needs or business environment. ● The financial cycle for budgeting purposes will be yearly ending 30 June. Reporting requirements Software applications to be used in reporting: ● environment – Windows ● accounting Information System –MYOB AccountRight plus ● data analysis –Microsoft Excel 2010. Actual results will be produced monthly by the MYOB accounting system. Actual variances to budget will be performed by Excel with a report prepared for senior management for significant variances. Financial delegations ● Each manager is responsible for achieving the revenue budgets agreed to by the budget committee. ● Each manager is responsible to approve, by signing the necessary paperwork, all expenditures that fall within their area of responsibility. ● Expenditures must be within the budget guidelines for the individual departments. Format for budgets and reports All budgets must include the following details: ● name of the person who prepared it ● cost centre (if applicable) ● name of the budget/report, i.e. sales, expenses, CAPEX, cashflow, budget variation report ● period of the budget.   Appendix 3 – Statement of financial performance Statement of Financial Performance Babies on the Go Statement of Financial Performance For the year ended 30 June 2014 REVENUE Sales 2,900,000 Less direct wages and Commissions 272,500 Opening stock 100,000 Purchases 300,000 Closing stock 20,000 Less cost of goods sold 380,000 Gross Profit 2,247,500 EXPENSES General & Administrative Expenses Travel 22,000 Legal fees 4,500 Bank charges 700 Office supplies 4,000 Postage and printing 500 Dues and subscriptions 600 Telephone 11,200 Repairs & maintenance 45,000 Payroll tax 25,000 Marketing Expenses Advertising 208,000 Employment Expenses Superannuation 45,000 Wages and salaries 500,000 Staff amenities 23,000 Occupancy Costs Electricity 38,000 Insurance 100000 Rates 100,000 Rent 200,000 Water 35,000 Waste Removal 60,000 TOTAL EXPENSES 1,422,500 NET PROFIT (BEFORE INTEREST AND TAX) 825,000 Income Tax Expense 206,250 NET PROFIT AFTER TAX 618,750 Appendix 4 - Ageing debtors budget Babies on the Go AGED DEBTORS BUDGET 2011/12 TOTAL Qtr 1 Qtr 2 Qtr 3 Qtr 4 Sales 2,900,000 600,000 900,000 800,000 600,000 % Debtors Sales 50% 50% 50% 50% Total Debtors 100% 300,000 450,000 400,000 300,000 Current 65% 195,000 292,500 260,000 195,000 30 Days 20% 60,000 90,000 80,000 60,000 60 Days 12% 36,000 54,000 48,000 36,000 90 Days 3% 9,000 13,500 12,000 9,000 Appendix 5 – Implementation Plan Recommendation: Activity Monitoring activity and date Person(s) responsible