The nature of the budgeting process and the interlinking of various budgets

a) Explain the nature of the budgeting process and the interlinking of various budgets in an organisation in the context of management planning and communication. b) Identify the appropriate classification of costs relating to a product or service and apply this understanding in Cost Volume Profit Analysis. c) Evaluate the financial performance and financial position of an organisation. d) Analyse internal financial information to inform decision making in a business context. e) Present and structure analysis and arguments clearly in numerical and written form. f) Construct and apply Excel spreadsheets as an aid to producing management accounting information and to depict this information graphically. A Passion for Fashion A Passion for Fashion Background “Our aim is to champion designer business support, encouraging designers not only to innovate, but to commercialise their work.” – UK Fashion Foundation The fashion sector has witnessed the demise of many high street retail outlets in recent years, as consumers turn to online shopping, and e-commerce continues to damage department stores and high street clothing retailers alike. Many stores are trying hard to promote their online ranges in order to compete, and there are great market opportunities for products which the traditional retailers can easily retail online. The UK Fashion Foundation, an association backed by major fashion industry sponsors, set out to help support this. They recently launched a competition for entrepreneurs to design a fashion item which could be retailed online under the category “One size fits all”, to minimise the need to try-on and return items. The prize money was £200,000. Coursework context You and your friend Donatello, a keen fashion designer, submitted an idea to the competition and won the £200,000 prize money. You have decided to use this cash together as share capital to start a business to manufacture the item. Donatello will be the Marketing Director, and sell the products on to retailers who can sell them as part of their online offering. Your role will be Finance Director and general business advisor. The following diagram illustrates the supply chain of which your business will form part. sells to sells to sells to The context for your coursework is therefore planning your fashion business project ready for its launch. You will be using your knowledge of finance and Excel to prepare a range of accounting reports in an excel spreadsheet that form the key elements of a business plan. Raw material supplier Manufacturer (This is you!) Retailer (store or online) Customer (store or online) A Passion for Fashion You will need to analyse the numbers and comment on potential risks and opportunities, carefully planning the financial requirements of the business. The financial analysis you prepare will be used to negotiate a loan with the bank. The loan, together with the £200,000 prize money, is critical to allow you to finance the business in its early stages. Your financial models should also be fully flexible, to allow you to make changes to key input data as your plan progresses, without having to re-design the supporting spreadsheets. This is one of the most important features of spreadsheet design, and illustrative of how these models are used in the real world. Once the business plan has been completed, you will prepare a report in Word to advise Donatello on the prospects for the business, and any further action which should be taken in the planning stage before the business is launched. There are 3 sources for the detailed financial inputs you require: 1. Those given to you in this brief 2. Those specific to you individually which will be given to you in an individual dataset which you will access in the AFM learning room in the file “passion for fashion.xls” 3. Inputs you will be asked to identify yourself using your own research and estimations. Support on Coursework. The following support is available, and you are strongly advised to make use of it all: - Weekly AFM Lectures (covering a range of accounting topics). - Coursework Briefing in AFM lecture, (w/c 12th November 2018) - Weekly AFM seminars/IT workshops (accounting topics and excel skills). - Additional video tutorials on key excel functions available on NOW - Feedback on progress from seminar tutor. - Frequently Asked Questions in NOW (see below) - Excel sessions run by the library Frequently asked Questions (FAQ) on coursework During the preparation of the coursework, any questions e-mailed to tutors will be answered in the FAQ area on NOW. This will allow everyone to benefit from the answers and, at the same time, help those returning to a topic at a later stage. Please be sure to check this area first before e-mailing your tutor as your question may well have been answered already. You are strongly advised to make use of your tutor’s office hours for any specific questions you may have. However, tutors will not do your coursework for you or do any preliminary type of marking, it is an opportunity to ask questions only. A Passion for Fashion Timescale We cannot emphasise too strongly the need to plan the project and to work steadily on it during term one. The weekly programme for the module includes a series of lectures and seminars that relate directly to the requirements of the coursework. So… “ don’t just stand there, let’s get to it (Strike a pose, there’s nothing to it……)” Detailed Brief & Financial Information Your company will be a small manufacturing business that rents a Business Unit that has both a production and an administration area. The company will make the products and sell them to Retailers across the country who can use them in their online offering. The retailers will require credit from your company. Your Product You will need to create a name for your company, and define your product. The product should focus on simplicity and the ability to retail online. You can select any fashion item (e.g. accessories, jewellery, clothing) which you believe will sell well and achieve a positive contribution, but it must be made in only one size. It is expected that the selling price your company will charge to retailers will be somewhere in the region of £10-£50 per item. The retailers will then apply their own mark-up before selling on to the end customers. You should assume this retailer markup would be 100%. You will need to define/quantify: • The selling price you will charge to the retailers within the £10-£50 range • Cost of the material per item • A percentage increase in quantity sold which would represent your best case sales, and a percentage decrease in quantity sold which you believe would be worst case sales. Do some research about the type of prices retailers charge for your kind of product, and don’t forget that the retailer will include their own mark up before selling to the end customer. Your product will need to be competitive in order to sell. A Passion for Fashion You will be required to note in your written summary the retailers you have identified and the prices they charge for a similar product, whilst justifying the prices you have set. You must also ensure that your product has a positive contribution, i.e. that the selling price is greater than the variable costs per item. If not, is your selling price too low? Are your material costs too high? Initial Capital • In January 2019 your company will receive £200,000 from your prize money for share capital. • The remaining capital will come from a bank loan (look at your individual data set for the amount). The loan interest rate is 12% per annum and is calculated on the initial amount of the loan. The loan repayments (including interest) are by regular monthly instalments over a period of 12 months. The first repayment will be in January 2019. Overdraft • Any additional finance that your company requires will be provided by a bank overdraft facility. The bank will charge interest at a rate of 2% per month in any month that the “closing cash balance” is negative. The interest will be paid in the following month. NOTE: due to the unpredictable nature of overdraft interest, this is NOT included in your product cost structure but will be required in your cash flow budget. Accounting Period • Your company will begin trading on 1st January 2019. The accounting information that you are required to prepare for the business is for the six months ending 30th June 2019. Sales As word spreads and your new trend catches on, the demand for your company’s product is expected to expand strongly during the 6 months ending 30th June 2019, so the quantity of items sold each month will rise over this period. In your individual data set you will be able to find: - Monthly sales quantities of your product - Percentage split of your potential product sales by geographical region - Credit terms given by your company to customers - Remember you must determine the selling price yourself Purchases of Raw Materials The supplier of your raw materials will allow 1 month’s credit. There will be no inventories of materials at the end of each month. In your individual data set you will be able to find the inventory policy (for finished goods). You must yourself identify the cost of materials per item. A Passion for Fashion Packaging Each item will be packaged in the production process. Therefore, you should calculate the total cost of the packaging each month using the number of units produced. Suppliers of packaging allow two months’ credit and no inventory of packaging is held at the end of each month. Remember that packaging suppliers are also a trade payable, just like raw materials suppliers. Your Salary You will be the Finance Director of the company for the 6 months ending 30th June 2019. You will be advised in your individual dataset of your director’s salary. Marketing You have a monthly marketing budget to advertise your product to the public, but need to know where in the country you should spend it. Research has indicated the potential for sales of your product in different parts of the country, and this information has been given to you in your dataset. The more the end customers demand your product, the more the retailers will buy from you, so it is important to use your marketing budget effectively. You will be asked to illustrate your geographical split of marketing spend in your written report using a pie chart. Depreciation Your business will need to purchase assets to help run the business. Plant & machinery will be required, as will five motor vehicles. Depreciation on plant & machinery is to be calculated on a straight line basis assuming that the plant and machinery will last for 6 years and will have a scrap value of £7,000 at the end of its life. The five motor vehicles will be depreciated on a straight line basis, assuming they will last for 5 years and have a scrap value of £1,000 each at the end of this time. Estimated Expenditure/income of the Business Below is a summary of the information Donatello has already identified in relation to business expenditure/income, together with a summary of all the key information sources: Expenditure Amount Payment terms Wages of production staff £15.00 Per Hour Labour hours per unit produced 15 minutes Per unit produced Rent £20,000 Paid quarterly in advance Sales commission paid to marketing team £0.20 Paid per unit sold in the month of sale Sales peoples’ salaries £8,000 Paid monthly (in the month incurred) Administration costs £36,000 Paid monthly (in the month incurred) Marketing Director salary £3,000 Paid monthly (in the month incurred) Machine power £0.15 Per unit produced. Paid monthly, 1 month in arrears A Passion for Fashion Packaging £1.50 Per unit produced. Paid 2 months after production Marketing and advertising £3,000 Paid monthly (in the month incurred) Overdraft rate 2% per month on negative cash balance Paid the month after it is incurred Bank loan interest 12% per annum Repaid monthly based on initial loan amount Share capital winnings £200,000 Received in January Bank loan See your dataset Received in January. Repaid monthly over 12 months FD salary See your dataset Paid monthly (in the month incurred) Motor vehicles See your dataset Paid in January Plant and Machinery See your dataset Equipment received in January, with one month credit for payment Sales quantities See your dataset Percentage sales split by region See your dataset Closing inventory policy See your dataset Materials cost per item TBC* Paid monthly with 1 month’s credit Selling price per item TBC* See dataset for credit terms *TBC items are going to be determined by you, following your research A Passion for Fashion REQUIRED You are required to produce an excel workbook that contains the following reports on separate worksheets: 1. FINANCIAL REPORTS (25 marks) • A detailed cash budget for the first 6 months of trading (analysed into months) which highlights the expected bank/cash balances at the end of each month and at the end of the period. You should also show a total column for the six month period. • A bar chart illustrating the closing cash position per month • Detailed working capital budgets for the first 6 months of trading for: o Trade Receivables o Trade Payables (including materials and packaging) o Inventories of Finished Goods (in Quantity) As with the cash budget, you should show a total column for each of your working capital budgets. Note – you are not required to produce a detailed income statement and statement of financial position for the first 6 months of the business. 2. PREDICTED PRODUCT COSTS AND PROFIT FORECASTS (20 marks) • A detailed product cost breakdown, showing variable costs per unit, selling price per unit, contribution per unit and total fixed costs for the 6 month period. • A table showing the expected profit or loss of the business for 6 months at: o the expected level of activity o the level of activity which exceeds your expectations (i.e. a 'best case') o the level which is some way below your expectations (your ‘worst case’) and the quantity of products that need to be sold in order to break-even o Margin of safety as a number of units, and as a percentage. Note: the best case and worst case fluctuations should be between 10-30% either side of the expected level. Within these limits, you pick the percentage fluctuation that you think is most appropriate to your business and justify the choice of percentage in your written summary. The coursework briefing lecture will discuss this point further. • A break-even chart that shows: total revenues; total costs; total fixed costs; total variable costs; the expected break-even point and margin of safety clearly labelled . • A pie chart to show, at expected sales levels, the projected split of sales by geographic region. A Passion for Fashion 3. A WRITTEN SUMMARY (750 words maximum), DESCRIBING THE BUSINESS AND IDENTIFYING KEY FIGURES FROM THE ACCOUNTING REPORTS YOU HAVE PRODUCED (25 marks) The Summary should be prepared in ‘Word’ using the template provided to you on NOW. Use bullet points /subheadings to separate the different sections of your report. You are required to start with a brief introduction to your product and your market. Then: Pricing - Justify the selling price you have selected with reference to competitors and existing retail prices on similar products. Be precise with your sources. (2 marks) - Import the pie chart of potential regional sales to illustrate which regions you should focus your marketing on. (1 mark) Profitability and risk - Evaluate the business risk in terms of the expected profit figures you predict. Explain the percentages you have assumed as best case and worst case scenarios with your reasons for selecting those percentages (eg what kind of risks could impact your sales projections?). (2 marks) - Comment on margin of safety, and the sensitivity of profit to changes in costs either variable or fixed. Illustrate this with some sensitivity analysis (=‘What-if’ analysis) on 2 key cost figures. (4 marks) - Suggest 1 way in which the company could reduce its financial risk / increase its profit numbers from where it currently stands. Illustrate the impact of your suggestion with What-if analysis. (2 marks) Liquidity - Analyse the key aspects of the cash budget prepared for the first six months of trading and identify the nature and amount of additional finance you would recommend that the company should raise, and when they would need it. (2 marks) - Illustrate your commentary by including the bar chart indicating monthly cash position. (1 mark) Working Capital - Comment on the levels of working capital the business is operating with. (1 mark) - Highlight 3 ways in which your business could improve their working capital position. (3 marks) - Use ‘what if’ analysis to show the potential impact on cash balances of implementing 1 of these recommendations. (2 marks) Other cash recommendations - Discuss at least 2 further ways the business could improve its cash balances (apart from more efficient use of working capital) (2 marks) Conclusion Conclusion: Do you believe the business is currently in a position to start trading using its existing financial plan, or do you recommend implementation of some of your suggestions above before submitting the loan application to the bank? (1 mark) Good presentation and report style (including introduction), clear use of English. (2 marks) First Steps Note that the marker will neither consider nor award marks for anything written beyond the 750 word limit. EXCEL SKILLS (30 marks) In determining the mark for the coursework you will be assessed on how you have demonstrated your excel IT skills in producing the above reports. In particular your completed coursework should apply the following skills: - Creation and linking separate worksheets - Completion of a data sheet linked to other worksheets - Use of VLOOKUP - Use of appropriate formulae for all calculations - Formatting of cells for a good standard of presentation - Use of ‘IF’ function and ‘Conditional Formatting’ - Creation and labelling of a Break-even graph - Illustration of cash data using bar chart by month and sales split using pie chart by geographical location If you set up the spreadsheet correctly, you should find that if you change any of your estimates – “what-if” changes - (on the data sheet) the spreadsheet will automatically recalculate all of the profit forecasts and financial reports in your workbook. First Steps The first step is to open the Excel file Passion for Fashion.xlsx and save it using a name that incorporates your student number. Have a look at the data sheet and compare the information there (in the green cells) with that outlined in this coursework brief so that you understand exactly what it means. Next you should look at the Individual Data set on your excel file and use VLOOKUP to collect the additional information that is unique to you and complete the cells highlighted in yellow in the data sheet. You will be marked on your use of VLOOKUP formulae. Note, if you are unable to use VLOOKUP formulae it is possible to manually type in your allocated data from the dataset, BUT you will lose the marks for use of VLOOKUP formula. Thirdly, having done your research into your product, add your company name, product, and the figures you have researched: selling price, and material cost per unit (into the orange cells). Then you are good to go. You can start with any further calculations, and setting up your financial reports and analyses on separate pages as per the requirements. Open up the Word document Passion for Fashion Report.doc and save it using a name which incorporates your student number. Use this template to create your business report, which should be submitted at the same time, and to the same dropbox as your Excel file.                                                                                                                                                                                                                                                                                                                                                                        

Sample Solution