Assessment Task
You are required to produce a budget report, complete with analysis and commentary, on a given scenario. The report consists of THREE compulsory parts.
HAMBLE LTD
Assessment Scenario
Hamble Ltd is an established food packaging and distribution business operating across London and the south east of England. The Directors of Hamble Ltd are considering expanding the business over the next 12 months by purchasing an existing established business which operates in the east of England and the midlands.
PART A (35% weighting)
The directors of Hamble Ltd have asked you, as a recently employed assistant management accountant, to prepare and review their cash budget. The bookkeeper started the cash budget but was unable to complete it due to ill health.
In addition, Hamble Ltd requires you to evaluate the different funding options for its expansion plans.
Estimated sales and purchases for each of the nine months to September are given below:
| Jan
£,000 |
Feb
£,000 |
Mar
£,000 |
April
£,000 |
May
£,000 |
June
£,000 |
July
£,000 |
Aug
£,000 |
Sept
£,000 |
|
| Credit sales | 9,500 | 8,000 | 6,000 | 11,000 | 12,350 | 14,000 | 13,250 | 4,500 | 12,500 |
| Cash sales | 250 | 350 | 800 | 600 | 620 | 900 | 500 | 350 | 800 |
| Credit purchases | 6,000 | 5,200 | 5,800 | 8,000 | 8,700 | 6,300 | 5,200 | 5,000 | 5,100 |
Trade Receivables: Credit sales are to be collected two months after the date they have been sold.
Trade Payables: Credit purchases are to be paid one month after the date they have been purchased.
Borrowings: The terms of a 5-year loan are coming to an end and Hamble Ltd are due to repay the capital of £1,000,000 on 31st July.
Current Liabilities: Corporation tax of £100,000 is due to be paid to HMRC in the month of May.
Delivery vehicles: Hamble Ltd has a policy of replacing delivery vehicles every 2 years. A number of delivery vehicles are due to be replaced under this policy in June at a cost of £1,250,000, but existing delivery vehicles will be traded in for £310,000 as part of the deal.
Other overheads: Other overheads will be £2,200,000 per month settled on a monthly basis in the month incurred.
Cash: Hamble Ltd expects to have £90,000 in cash at the beginning of April.
You are required to:
- Using Excel, prepare a cash budget for the business for the 6-month period from April to September.
NOTE: To achieve full marks you MUST show your working out, i.e. by using Excel cell referencing and formula.
- Examine the cash budget you have prepared and advise the directors of Hamble Ltd of any possible solutions to cash flow problems that might be evident. Marks will be awarded depending on the depth of discussion.
- Identify five sources of finance available to Hamble Ltd and evaluate the advantages and disadvantages of each. You should then make a recommendation to the directors of Hamble Ltd of the most appropriate source of finance for the proposed expansion plans. Marks will be awarded depending on the depth of discussion.
- Within the next 3-5 years Hamble Ltd intend to expand their business throughout the UK following which their aim is to list the business on the London Stock Exchange. The Directors of Hamble Ltd have asked you to briefly explain the advantages and disadvantages of raising finance through the issue of shares.
PART B (40% weighting)
The Directors of Hamble Ltd have identified two existing established businesses that may be suitable to purchase to fulfil their expansion plans and have asked you to calculate and compare ratios for both companies before making a recommendation on which company to purchase.
| Income Statements for the Year Ended 31st Dec 2020 | |||||
| Norwich Ltd | Salford Ltd | ||||
| £’000 | £’000 | ||||
| Sales revenue | 8,320 | 11,250 | |||
| Cost of sales | (6,020) | (9,030) | |||
| Gross profit | 2,300 | 2,220 | |||
| Operating expenses | (1,048) | (1,535) | |||
| Operating profit | 1,252 | 685 | |||
| Finance charges | (20) | (70) | |||
| Profit before tax | 1,232 | 615 | |||
| Taxation | (62) | (30) | |||
| Profit for the year | 1,170 | 585 | |||
| SOFP (Balance Sheet) as at 31st Dec 2020 | |||||
| Norwich Ltd | Salford Ltd | ||||
| £’000 | £’000 | £’000 | £’000 | ||
| Non-current assets | 502 | 198 | |||
| Current assets | |||||
| Inventory | 1,290 | 2,437 | |||
| Trade receivables | 730 | 1,990 | |||
| 2,020 | 4,427 | ||||
| Total assets | 2,522 | 4,625 | |||
| Equity | |||||
| Share capital | 1,350 | 800 | |||
| Reserves | 580 | 1,145 | |||
| 1,930 | 1,945 | ||||
| Current liabilities | |||||
| Trade payables | 430 | 850 | |||
| Taxation | 62 | 30 | |||
| Bank overdraft | 35 | 350 | |||
| 527 | 1,230 | ||||
| Non-current liabilities | |||||
| Loans | 65 | 1,450 | |||
| Total equity and liabilities | 2,522 | 4,625 | |||
You are required to:
- Use your Excel skills to calculate the following ratios for both companies.
You should then present your calculated ratios within your report in a simple table format for ease of comparison.
NOTE: To achieve full marks you MUST show your working out, i.e. by using Excel cell referencing and formula.
- Gross profit margin
- Operating profit margin
- Return on capital employed
- Asset turnover
- Current ratio
- Acid test
- Inventory days
- Trade receivable days
- Trade payable days
- Working capital cycle/operating cycle
- Gearing
- Interest cover
- Compare the performance of the two companies in the 4 key areas of profitability, efficiency, liquidity and gearing using the ratios calculated in part 1 and make a recommendation as to which of the two companies Hamble Ltd should consider purchasing. Marks will be awarded depending on the depth of discussion.
PART C (25% weighting)
Hamble Ltd has a varied client base that includes schools, hospitals and the hospitality sector and has experienced fluctuating consumer demands since the beginning of the Covid-19 pandemic.
One of their products is a fresh produce box, consisting of local seasonal produce, and which is ordered in units. The following information is available for the year ending 30th September.
- Due to these fluctuating demands, you have been asked to use Excel to prepare a flexible sales budget for quantities of 200,000 units and 300,000 units. Sales revenue is fully variable.
NOTE: To achieve full marks you MUST show your working out, i.e. by using Excel cell referencing and formula.
| Budget | ||
| Sales (units) | 250,000 | |
| £/unit | £000s | |
| Sales revenue | 3,250 | |
| Variable produce costs | (875) | |
| Variable production overheads | (150) | |
| Fixed production costs | (700) | |
| Fixed administration costs | (1,160) | |
| Profit | 365 |
- Consider specific factors that may contribute to an increase or decrease in Hamble Ltd’s consumer demand. You should identify a minimum of 2 factors that may contribute to an increased demand and a minimum of 2 factors that may contribute to a decreased demand. Marks will be awarded depending on the depth of discussion.
