Reference no: EM133645848

Business and Financial Applications

Assessment - Spreadsheet Model and Report

Pineapple Blast Imports Ltd

Brothers, Simon and Ben, plan to commence a wholesaler business importing a new brand of pineapplebeer from Sao Miguel Island in the Azores, called Pineapple Blast on 1 June 2024. The brothers discovered the product whilst on a recent visit to the islands of the Azores. They were extremely impressed with the quality of the local organic ingredients, the purity of the 6% alcohol drink, and its balance between smoothness, sweetnessand crispness delivering a great flavour and palette. The plan is to use a new way of transporting the product, a battery cooled thermal-bag container, called a Flexible Intermediate Bulk Container. This is a much lighter, quicker, and cheaper way of transporting the base product. However, it will require bottling in 500ml bottles once in the UK.

Create a financial model of the expected cash flows of the business and an income statement for the first 6 months.

The following information relates to the brothers' trading expectations for the first six months.

The business has set out below basic assumptions on costs and revenues. These are likely to change as the business gains experience of the trading environment:

Business set-up costs £1,000.

Customers are given 2 months to pay. Expenses and Wages are paid in the month to which they relate.

Product purchase and transportation from the Azoresis expected to be £1 per 500ml bottle and the bottling and labelling costs in the UK are expected to be £0.45per bottle. In addition, the management plans to hold stock of 30% of each month's sales during the initial 6 months to be able to meet unexpected demand peaks.

Pineapple Bomb Imports have only managed to secure credit terms of 1 monthfrom the Azorean suppliers.

The brothers have met with and offered Steffi the position of sales manager if the project takes off. Steffiwill bepaid on a commission only basis at 2% of sales paid one month in arrears. The retail selling pricehas not been set yet. During the interview,Steffi suggested a price £6.50 per bottle. She has extensive contacts and experience in the industry, and she expects that during August the business should have reached a marketing stage where they might be able to secure a promotional slot with a couple of national off licence chains and, potentially, a major supermarket. During August sales will be made on a 3 for 2 basis.

Wages for the handling of the units, which are separate from salaries, are likely to fluctuate depending on the number of units bought each month. The table below maps the expected wages cost to purchasevolume.

Advertising and special promotions costing £30,000 will be purchased and paid for in July.

Warehouse equipment worth £10,000 will be purchased on 1st June and paid for in equal monthly instalments over the first six months. Alternatively, they could lease the warehouse equipment for £500 per month.

On 1 August,the business plans to purchase a vehicle costing approximately £20,000. This is to be paid in equal monthly instalments over three years. The leasing cost of these types of vehicles is approximately £1,000 per month.

The business plans to lease premises at a rental of £15,000 per annum, payable quarterly, and beginning on the 1 June.

The equipment will be depreciated using the straight-line method of depreciation at a rate of 12% per annum, and the vehicle at a rate of 20%.

Add any other costs or considerations into your model.

Required:

1. Pineapple Bomb Imports would like you to develop a cash budget model which they can use to assess this and future expected cashflows. The model should show opening balance, cash inflows, cash outflows and closing balances for each month.

2. Produce an expected Income Statement model for the period to the end of November. The model should show revenues, costs and profit using the methods and structures you have learnt in previous studies.

3. You should develop formulae to link the independent variables to the dependent following the methodology taught in-class.

4. The design and layout must be easy to understand and use and follow general accounting structures. Consideration must be made to avoid loss of the model or errors occurring.

5. Write a report (approx. 800 words) on the results of your analysis, commenting on any assumptions you have made, profit levels, the break-even point, cash requirements, potential scenarios and other issues the firm should consider.