Order this Assignment Now: £79

Explain the nature of the budgeting process and the interlinking of various budgets in an organisation in the context of management planning and communication.

Coursework Assessment Brief

Trendsetters Ltd 

Excel Based Spreadsheet Modelling - Business Start-up

Type of Assessment	Summative Individual IT Based Coursework (a Management Accounting based spreadsheet modelling and report writing exercise).
Weighting	50% of the overall grade for the Module.
Submission Date	16th May 2022 11.00pm. The standard undergraduate lateness penalties will begin to run after this time as per Quality Handbook 16A section 8: Up to 5 days late - work will be capped at low 3rd. Over 5 days late - work will be awarded a mark of zero. Note: even 5 seconds late counts as a late submission
Documents for Submission:	Excel file financials produced to be uploaded to the AFM Dropbox. Word file commentary to be uploaded to the AFM Dropbox (including declaration of independent working which covers both excel and Word submissions) Templates are available to download from NOW

Learning Outcomes

On successful completion of the coursework you will be able to:

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.

Trendsetters – Detailed Brief

Photo by Deagreez on Unsplash

Photo by Martin Bernetti/AFP via Getty Images

Background

Fashion has existed for as long as human beings have lived but the distinction between ready-made /ready to wear fashion and Haute Couture (High-end, custom-fitted Fashion) did not exist until the mid-19^th Century.

With the advent of the year 2000 the fashion retail business witnessed a new phenomenon coined as ‘fast fashion.’ The big fashion houses in America identified a new fad that was taking the fashion retail market by storm. The fashion retailers on the high streets – Zara, Primark, Topshop & H&M to name a few – started to dominate the high street with this new trend. Fast fashion thrives on the concept of quickly turning over designs that feature on the catwalk to current fashion trends.

One of the attractions of fast fashion is that it is less expensive and has quicker manufacturing and shipping processes and methods. This coupled with increased consumer purchasing power and desire for the latest fashion styles, especially among the younger population has encouraged the need for instant gratification.

However, the downside of fast fashion is that the manufacturers choose to operate in countries where the labour laws are lax. As a result, the workers in this industry are low paid and work in conditions that are unsafe and risky e.g. sweatshops where employees work in unsafe conditions. The fast fashion industry has therefore stoked environmental and ethical issues and is seen as being unsustainable, exploitative and heavily resource based.

You and your two siblings have recently received an unexpected sum of £125,000, by way of an inheritance from the estate of a distant Aunty. Inspite of the problems associated with fast fashion, you all believe that investing this money into the fast fashion industry would yield an attractive return for all. Given the current economic climate where prices of commodities are increasing due to inflation and supply chain problems, being able to offer affordable clothing would go a long way in putting money back into the pockets of the consumers. 

You and your two siblings decide to start a new company, Trendsetters Ltd., which will produce and sell fashion shirts.

Coursework context

The inheritance money will be used as the initial capital in Trendsetters Ltd which will manufacture fashion shirts and sell them directly to business customers both online and in stores. You have been given the role of the Finance Director in the new business and being the eldest, your brother John will be the Managing Director. Your sister, Paula, will carry out the role of the Marketing Director.                                                                                                                

The context for your coursework is planning your 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. The financial analysis you prepare will be used to negotiate a loan with the bank, which is critical to allow you to finance the business in its early stages.

Your financial reports in Excel should 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 business.

Once the business plan has been completed, you will prepare a report in Word to advise John 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 2 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 “Trendsetters.xlsm”

Detailed Brief & Financial Information

Your company will be a small manufacturing/retail business that rents a Business Unit that has both a production and an administration area. The company will make the shirts and sell them to customers. The customers will require credit from your company.

The following diagram illustrates the supply chain of which your business will form part.

  sells to  sells to      sells to  

                                        

Your Product

Your product are shirts made from polyester and nylon. The shirts are manufactured on an industrial scale for sale to both men and women in different geographical regions in the U.K.

The selling price your company will charge to customers will be £20 per shirt.

The Managing Director has set a challenge that the business should make a target profit of £150,000 in the first six months and you need to calculate how many items must be sold to achieve this.

Initial Capital

• In July 2022 you will deposit the £125,000 from your inheritance win into the business bank account as share capital.
• The remaining finance will come from a bank loan (look at your individual data set for the amount). The decision to approve this amount by the bank will only be made on the acceptability of the financial reports you have prepared.
• The loan interest rate is 9% per annum and will be amortised over a period of 2 years with monthly instalments.  The first repayment will be in July 2022. 

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 3% per month in any month that the “closing cash balance” is negative. The interest will be paid in the following month (i.e. 1 month in arrears). 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 budget.

Accounting Period

Your company will begin trading on 1^st July 2022. The accounting information that you are required to prepare for the business is for the six months ending 31^st December 2022. 

Sales

As your marketing spend raises publicity for your new shirts, demand is expected to expand strongly during the 6 months ending 31^st December 2022, 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 sales by customer sector
• Credit terms given by your company to customers

Purchases of Raw Materials

The supplier of your raw materials will allow 1 month’s credit (i.e. you pay 1 month in arrears). There will be no inventories of raw materials at the end of each month. In your individual data set you will be able to find the inventory policy (for finished goods).  The cost of raw materials is in the data table below.

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 no credit (i.e. you pay immediately) and no inventory of packaging is held at the end of each month.

Directors’ Salaries

You will be the Finance Director of the company for the 6 months ending 31^st December 2022. You, John the Paula will be paid a director’s salary. The total amount of these will be advised in your individual dataset.

Marketing

You have a monthly marketing budget to advertise your product but need to know which geographical region is most likely to want your product to help you in the targeting of your advertising. Customers come from different areas in the United Kingdom, including Scotland, Northern Ireland, Wales, Northern England, the Midlands and London. An advertising research company has provided some data on the likely demand by region. This information has been given to you in your dataset.  You will be asked to illustrate the split of sales by geographical region in your written report using a pie chart.

Plant and machinery

Your company will need to purchase assets to help run the business operations. Plant & machinery will be required. This will be purchased in July and given 1 month credit. Depreciation on plant & machinery is to be calculated on a straight-line basis assuming that the plant and machinery will last for 4 years and will have a scrap value of £7,000 at the end of its life. 

Estimated Expenditure/income of the Business

Below is a summary of the information the company has already identified in relation to business expenditure/income and accounting policies:

Expenditure	Amount	Payment terms
Wages of production staff	£9.50	Per Hour
Labour hours per unit produced	6 minutes	Per unit produced, paid in the month of production
Rent	£18,000	Paid quarterly in advance
Direct materials	£5.00	Per shirt; paid 1 month in arrears
Administration costs	£35,000	Paid monthly (in the month incurred)
Machine power	£0.04	Per unit produced. Paid monthly, 1 month in arrears
Packaging	£0.3	Per unit produced. Paid immediately in the month incurred.
Marketing and advertising	£4,000	Paid monthly (in the month incurred)
Overdraft rate	3% per month on negative cash balance	Paid 1 month in arrears (after it is incurred)
Bank loan interest	9% per annum	Amortised with monthly instalments (Loan is amortised on reducing balancing basis)
Share capital (Inheritance)	£125,000	Received in July
Bank loan	See your dataset	Received in July. Repaid monthly over 2 years (24 months)
Director salaries	See your dataset	Paid monthly (in the month incurred)
Machinery purchase	See your dataset	Equipment received in July and paid for in August
Machinery residual value	£7,000
Machinery expected useful life	4 years
Sales quantities	See your dataset
Percentage sales by Geographical split	See your dataset
Closing inventory policy	See your dataset
Selling price per item	£20	See dataset for credit terms
Best case %	15% better than expected sales
Worst case %	15% worse than expected sales
Opening balance cash	£0
Opening balance inventory (finished goods)	0 units
Opening balance payables	£0
Opening balance receivables	£0
Target profit	£150,000	For the first 6 months
Cash inflow growth	See your dataset

Make or Buy decision

There has been increased displeasure from the public with fast fashion due to the environmental problems it poses. The market feels that the carbon footprint of Trendsetters is high and the use of polyester and nylon materials are unsustainable and not eco-friendly. Trendsetters is therefore considering making changes to their manufacturing and sales processes. The company is considering producing a range of shirts from Bamboo and sustainable viscose materials. These materials are bio-degradable and better for the environment. Trendsetters Ltd has the facilities available to produce the shirt with their current spare capacity However, a supplier has approached Trendsetters Ltd and is willing to supply them with the exact same shirt at a price of £40. Alternatively, if Trendsetters decide to use their facilities (produce in-house) then the costs related with the production of the shirt would be as follows:

Production costs of Bamboo & Sustainable Viscose Shirt

	£
Direct material	15	per unit
Cost of Machinery	20,000
labour	10	per unit
Electricity	1000	per month
Electricity	2	per unit
Factory Rent	4,000	per month
Machine power	5	per unit
Packaging	2	per unit
Transportation	1.5	per unit

You need to help the company arrive at a decision as to whether they should make the new range of shirts using their current capacity or accept the offer to buy the shirts from the supplier 

Investment appraisal

Trendsetters is looking to expand production in the coming years. In order to do this, the Finance Manager has proposed they consider investing in a new factory. An initial amount of £100,000 would be invested in this proposed project. The initial cash flow from this project will be £20,000 and this cash flow will grow in subsequent years using the cash inflow growth percentage in the individual dataset. We need to consider if this project is worth investing in using cost of capital of 10%.

REQUIRED

You are required to produce an excel workbook that contains the following reports. John has set up a template for you to use for each report as part of your starting Excel file. However, John may be a great director and communicator, but he is NOT an accountant. Some of the cost headings he has used should not be included in the templates he has given you. You must determine which rows you need to populate, and which ones you do not.

John is also not very good at Excel, the templates are not well formatted, and you will need to add all the relevant formulae, as well as making them look professional. Please do not change the titles or order of rows in the templates.

EXCEL SKILLS (25 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:

• Linking separate worksheets
• Completion of a data sheet linked to other worksheets
• Use of appropriate formulae for all calculations
• Formatting of cells and numbers for a good standard of presentation including use of thousand comma separators, consistent decimal places, bold, underline and use of brackets for minus numbers
• Use of ‘IF’ function and ‘Conditional Formatting’
• Use of ‘ROUND’ function on overdraft interest and closing inventories
• Creation and labelling of a Break-even graph
• Illustration of cash data using bar chart by month and marketing split using pie chart by customer sector.
• Carry out an NPV and IRR calculation using excel functions
• Use excel function to amortise a loan and then produce amortisation (repayment) schedule 

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.

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.

• The number of units which you would need to sell in order to achieve a target profit of £150,000 over the 6-month period.

• A table showing the expected profit or loss of the business for 6 months at:

§  the expected level of activity

§  the level of activity which exceeds your expectations (i.e. the `best case`)

§  the level which is some way below your expectations (your ‘worst case’)

§  the quantity of products that need to be sold in order to break-even      

§  Margin of safety as a number of units, and as a percentage.

Note: the best case and worst-case fluctuations are 15% either side of the expected level.  You will need to explain some possible reasons why the results may end up being better than expected (opportunities) or worse than expected (risks) in your written report. The coursework briefing lecture will discusses 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 the split of sales by geographical region
• A make or buy computation to show the impact/effect of a decision to adopt more environmentally friendly materials

3. FINANCIAL REPORTS (25 marks) 

• Detailed working capital budgets for the first 6 months of trading for:
• Inventories of Finished Goods (in Quantity)
• Trade Receivables
• Trade Payables
• 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                    

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.

4. A WRITTEN SUMMARY (1000 words maximum), DESCRIBING THE BUSINESS AND IDENTIFYING KEY FIGURES FROM THE ACCOUNTING REPORTS YOU HAVE PRODUCED (30 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 sentence briefly introducing the purpose of your report.

Then:

Marketing

Import the pie chart of how you will spend your marketing budget by geographical region. 

Profitability and risk

Explain how your business is performing showing the expected profit figures you predict, and how these compare to the £150,000 target profit. Explain one kind of risk and one opportunity which could impact your sales projections to give your worst and best case scenarios?

Comment on the margin of safety, and the sensitivity of profit to changes in selling price or costs either variable or fixed. Illustrate this with some sensitivity analysis (=‘What-if’ analysis) on your selling price, and 1 key cost figure.

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. 

Liquidity

Analyse the key aspects of the cash budget prepared for the first six months of trading and identify the amount of additional finance you would recommend that the company should raise, and when they would need it.

Illustrate your commentary by including the bar chart indicating monthly cash position. 

Working Capital

Highlight 2 ways in which your business could improve their working capital position. 

Use ‘what if’ analysis to show the potential impact on cash balances of implementing 1 of these recommendations. 

Other cash recommendations

Discuss at least 2 further ways the business could improve its cash balances (apart from more efficient use of working capital)  

Investment decision

Should Trendsetters accept or reject the proposed investment based NPV and IRR criteria. Explain why?

Make or buy decision

Explain why you believe they should make or should buy the new Bamboo shirts from the supplier

Outline any two factors Trendsetters should consider in arriving at the make or buy decision

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?

Future prospects: are there any risks you should raise on the future prospects of the business and its trading over the next 5 years in relation to the economy, competition or other trading environment risks? 

Good presentation and report style and clear use of English.

For clarity: ‘What-if’ analysis involves changing a single variable (cost figure or cash assumption) in your cash or profit assumptions, then note the impact of this on your overall cash or profit (screen shot it for your own records if this helps) and then return the variable back to its original value in your model. Then you can repeat for other variables.

Note that the marker will neither consider nor award marks for anything written beyond the 1000 word limit.

First Steps

The first step is to open the Excel file Trendsetters.xlsm and save it using a name that incorporates your student number.

Read the student instructions on the first sheet of the file and click the ‘enable’ button there to enable the workbook. You need macros enabled to do this, instructions on how to enable your macros will appear if your macros are not enabled.

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 type your student number into the ‘Data’ worksheet in cell B3, after which this cell will lock. Look at the Individual Data Set on your excel file and find your student number. Copy the data against your student number into the relevant yellow cells on the data sheet.

Then you are good to go.  You can start with any further calculations and setting up your financial reports and analyses on separate worksheets as per the requirements.

Open up the Word document Trendsetters Report Template.docx 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.

Additional Information

Support on Coursework. The following support is available, and you are strongly advised to make use of it all:

Your notes from the weekly AFM seminars/IT workshops (accounting topics and excel skills), on demand mini-lectures and practice exercises in the learning room each week.

• Additional video tutorials on key excel functions available on NOW
• Drop-in sessions/Office hour offered by module staff
• 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.  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 seminars and tutor 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.

Independent Working

It should be noted that this assessment is an individual coursework, and needs to be completed by each student independently, not collaborating and not as a group effort. Any evidence of collaborating, copying or sharing work files will be subject to academic misconduct investigations, per the Quality Handbook Section 17C. For the avoidance of doubt, allowing another student to copy your work is just as serious as copying someone else’s work.  Each student will be required to declare their work is their own independent contribution prior to submission on Dropbox.

Student work will be tracked using an inbuilt audit function within the Excel workbook, identifying when each keystroke was made and by whom.

Timescale

We cannot emphasise too strongly the need to plan the project and to work steadily on it from the date of its release until submission.   

Good Luck!!