Business Accounting & Finance - A1 Assignment - Individual Assignment Assignment Instructions: Assignment Reference BAF/StudentNumber/Jan18/A1 Due Date
Question:
Business Accounting & Finance - A1 Assignment - Individual Assignment Assignment Instructions:
Assignment Reference
BAF/StudentNumber/Jan18/A1
Due Date
08 February 2018
Weighting
20% of the course
File Format
MS Excel - See Template
Submission Details
Blackboard - instructions provided on Blackboard
Assignment Question:
Instructions concerning this assignment -
- Download the Excel template from blackboard.
- Complete the Cash Budget, Income Statement and Balance Sheet using the
- Excel templates provided. Textual headings in this template are protected. They contain all the headings you need to complete the assignment and should therefore remain protected. Only the cells coloured in YELLOW should be completed. You can use other excel sheets as workings, or use the additional sheets in the Excel template, but please note you can only submit this template, and only the first worksheet in the template will be used as the basis for marking. You will see that this Excel sheet self-marks your assignment as you progress, and you should therefore not change any of the text or format of any of the textual cells, nor move any of the three statements (cash budget, income statement or balance sheet) into different sheets in excel.
- Save your assignment according to your student ID number, followed by 'BAF', for example: '9794015BAF'
- Submit your completed Excel-based assignment through blackboard.
[The assignment can be found across the following pages]
1
The E-Bike'' (represents 20% of total weighting of the BAF module)
Inspired by the 1912 Harley Davidson and his search for answering the current
environmental problems, designer Janpeter Eilander developed the e-cruizer.
Inserted with European Space Agency (ESA) technology, the e-bike generates the
maximum output with a minimal electricity consumption. The Dutch company behind
this great product, B4-bikes
, is changing the image of the electric bicycle. No longer
will only elderly people dominate this e-bike category. With the cool design features
of the e-cruizer, every tough guy in your neighbourhood will soon make his
statement on it. The e-cruizer is easily running 25 km per hour which is just below
the maximum speed for driving without a helmet. The e-bike is completely hand
made with customised finishing.
China is both the leading producer as well as the largest consumer of e-bikes. This
was largely triggered by local Chinese governments restricting motorcycles in city
centers avoiding traffic disruption and accidents. Even though road safety concerns
continue, the positive environmental impact is becoming increasingly important as
well. In Europe, Germany and the Netherlands are leading the e-bikes sales.
Especially, design innovations such as the e-cruizer lead to more adoption with the
younger crowd. Also countries like Switzerland with urban centres spread over
mountainous areas, the e-bike is a helpful push in the back cycling up the hill. In
order to deceive your neighbours, the batteries supporting you in your endeavour are
disguised as water bottles.
[Source: http://www.greenentrepreneurship.com/e-cruizer-makes-e-bike-cool/]
In commencing the production of the first model, Janpeter is preparing a business plan that he can take to banks and venture capitalists to seek finance and which will also help to guide him in the process of setting up and starting up the E-Bike business, and has asked you for your help with the financial part of this (i.e. a budget). As a starting basis, he has put together some initial forecasts and estimates which are outlined below.
Business Units
The business premises will consist of a workshop where the bikes will be made, with a loading bay at which vehicles can be loaded for despatch to customers, and a showroom which can display the product and variants of colour schemes for customers, and to offer test-rides. He will also make some sales directly. He has segmented his market and has set differing prices and credit terms for the three distinct groups of customers to whom he is planning to sell:
Direct sales to customers Other retailers LargeCorporate Clients
Selling price per product* 2,500 2,100 1,800
Credit terms No credit (cash sales only) 1 month 2 months
Expected proportion of total sales 20% 45% 35%
* NB: Whilst the business has since evolved from the initial start-up period, offering various customisations at a price, assume the business will produce only a single standard product throughout its first year, with no option for customisation or change to the sales price.
Sales: Janpeter estimates that in May (his first month's trading) he will sell 300 products in total. This follows in June with 400, July 500, August 600, September 700, October 800, November 900 and December 1,000. He is assuming that there will not be any seasonality in demand.
The raw materials required to produce a single bike will cost 900 per bike. As a new business, Janpeter cannot assume that suppliers will offer him any credit, so to be prudent he is planning on the basis that their invoices will all have to be paid at the time of purchase. He plans to keep (in stock) at all times, sufficient inventory of raw materials to cover the following month's trading (so for example, raw materials for May will have to be procured and paid for in Aril, etc.). As he plans to make-to- order and to manufacture on a just-in-time basis, it is not necessary to plan for any inventory of either finished products or work-in-progress. You should assume that sales in January in the second year of operation will be 1,000 units.
Staff costs are forecast to be:-
Workshop: 10 full time people paid at 15 per hour, each working a 50-hour week.
Showroom: 4 full time people paid at 10 per hour, each working a 50-hour week.
Staff wages will be paid at the end of each week.
The company will also have to pay a further 20% of this amount in social security and pensions contributions, and these will be payable 1 month in arrears. Both categories of staff will be employed as from the start of March.
Energy & water: When the business starts trading, the workshop will consume energy and water at the rate of 100 per product. The showroom will also consume energy at the rate of 2.50 for each hour that it is open for business.
In the period January - April the workshop and showroom will each consume energy & water to the value of:
Workshop Showroom
January 250 100
February 250 100
March 1,200 250
April 2,000 250
Energy costs are payable 1 month in arrears.
He plans to open the showroom from 9:30am to 6pm each day for 6 days per week, i.e. 50 hours per week (rounded, for simplicity).
Premises: rent of 6,500 per month will be payable quarterly in advance, plus a deposit equal to one quarter's rent as soon as Janpeter moves in (i.e. on January 1st). This will be returnable only when Janpeter vacates the premises. The landlord has agreed not to charge any rent for the period January to April, so the first payment of rent will be needed only at the end of April (covering the period May- July), with subsequent quarterly payments for August-October (payable in July), November-January (payable in October). The rental agreement will expire after 7 years, after which the building will revert to the landlord.
Showroom fixtures and fittings: The premises will need specialist fixtures and fittings costing 90,000 before business can start trading. These will be installed by an outside contractor over the period January, February and March. The contractor allows 1 months credit, and payment is to be spread equally over three months (therefore 30,000 in Feb, 30,000 March and 30,000 April)
Business rates are charged at a rate of 36,000 per annum. The property company has agreed to absorb this cost up to the end of the current UK government financial year, which ends on 31st March. Janpeter will have to bear this cost as from 1st April onwards. The charge for the year is payable in 10 equal monthly instalments, from April to January inclusive (so 3,600 payable from April onwards).
Various equipment costing 300.000 will be delivered in March and installed and tested in April, in time to start trading on May 1st. The suppliers allow 1 month's credit after the date of delivery, and Janpeter will take up this offer. The equipment is expected to last for 5 years before it will need replacing, and will have 10% disposal value. The company has chosen a straight line depreciation method for the equipment.
A delivery van will be purchased in April in time for the start of business on 1st May. It will cost 30,000, to be paid in full at the time of purchase. It is expected to last for
5 years, after which it will be sold for 5,000. The company has chosen a reducing balance method and the rate per annum is 35% ((and charges depreciation for full year in the first year of operation (i.e not proportion to the opening month of trading))
Marketing & advertising: Janpeter considers that he will need to invest 10,000 in marketing in March, 10,000 in April, then at an ongoing rate of 1,000 per month indefinitely into the future. This will have to be paid for as it is incurred, on the first day of each month (i.e. there is no credit).
Financing: Janpeter is investing 200,000 of his own savings in the form of equity plus a further 69,210 that family and friends have offered to invest as shareholders. He also plans to borrow a further 200,000 on a 7-year loan at an interest rate of 6% per annum, payable monthly. This loan would be taken in two equal instalments, on 1st January and 1st April. It would be on an interest-only basis with interest paid monthly at the end of each month and no repayments of capital until the end of the loan period. Any further finance beyond this would have to be borrowed through an overdraft. His bank has assured Janpeter the bank will make this available, if required.
[NB: overdrafts are a common form of short-term financing in the UK and many other countries, though not universally. In essence it simply means that your bank has agreed to allow you to run your current (checking) account with a negative cash balance, up to a pre-agreed limit (overdrafts are not a separate source of new finance). If in your cash budget you find that the cash balance is negative, it should be included in the budgeted balance sheet as a negative figure in the current asset section (cash in bank). (NB: for simplicity, assume for this assignment that this overdraft is interest-free)]
General management of the business: Janpeter will handle the general management, marketing and sales to business customers himself. He does not intend to take any money out of the company during its first year.
Assumptions
For simplicity and to keep this exercise manageable, make the following working assumptions:-
- Reminder! Assume that months 1 and 2 in each quarter (January, February, April, May etc.) are of 4 weeks, and the third month (March, June, September, December) is a 5-week month.
- Assume 52 weeks in each year.
- Tax: Since the E-bike has been identified as a sustainable business by
- various governments, and is located in a tax-free zone, no tax will be payable
- for the first five years of its operation.
- Round any odd amounts to the nearest whole pound (do not show pence).
year in the first year of operation (i.e. not proportional to the opening month of
and charges depreciation for the full
trading)
Requirements
Prepare, for Year 1 from January to December inclusive, based on the data and forecasts above:
- A Cash Budget (not a historic cash flow statement) for the period January to December on a month-by-month basis. This should show the total cash in and out for the year as a whole for each item, and the closing balances of cash both for each month and at the year-end. Doing so in the template provided, and no other variant of the template [13.75 marks]
- A budgeted Income Statement for the whole of the period January to December (not month-by-month), following usual financial accounting conventions (e.g. the accruals principle, and concerning the recognition of assets). Doing so in the template provided, and no other variant of the template [23.25 marks]
- A budgeted Balance Sheet (statement of financial position) as at 31st December. Doing so in the template provided, and no other variant of the template [23 marks]
Agenda: Aprox 60 mins
1. Go through each line item from the assignment to clarify any ambiguity
2. template for the cash budget & complete the cash budget up till the May trading period
3. Reinforce the connection between each of the financial statements
4. Unsertand the logic of the Income Statement
5. Understand the logic of the Balance Sheet (aka the Statement of Financial Position)