please please please help

Your company will be a small manufacturing/wholesale business that rents a Business Unit that has both a production and an administration area. The company will make the bottles 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. Your Product Your products are water bottles made from biodegradable corn starch. The bottles are manufactured on an industrial scale for sale to supermarkets and department stores in different geographical regions in the U.K. The selling price your company will charge to wholesale customers will be 10 per reusable water bottle, reflecting its premium design. The Managing Director has set a challenge that the business should make a target profit of 300,000 in the first six months and you need to calculate how many items must be sold to achieve this. be required. This will be purchased in July and given 1 month's credit. 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 15,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: 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 300,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 10% 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 'limited resources' computation to show the optimal product mix and resulting profit Limited resource decision - 2025-26 In the company's second year of operation (2025-26) they have planned to massively streamline their operations, reducing fixed costs, and bringing in two further product lines to compliment their standard water bottle. These are: 1. a thermal lined water bottle to keep hot drinks hot, and 2. a fruit insert water bottle, with a mesh central compartment in which you can insert fruit (lemons, strawberries etc) to flavour your water. All 3 products use the same standard corn starch outer bottle, but in different quantities. The variable costs and forecast quantity sold per item of each of these is shown in your data set, and the quantity they forecast to sell are shown in the table below: Your plans have been disrupted however, as a severe drought has hit the main global producers of the corn which is used in your corn starch, the key ingredient of your biodegradable plastic. There is now a globally limited supply of the corn starch required to make the bottles, and you know you will have access only to 500,000kg for 2025 . You need to help the company arrive at a decision as to which product they should prioritise and predict their likely profitability in 2025-6 given the global shortage of the corn starch. REQUIRED You are required to produce an excel workbook that contains the following reports. 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 at the point of dispatch to customers. Therefore, you should calculate the total cost of the packaging each month using the number of units sold. 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 312 December 2024. You and Olivia will both 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's credit. 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 Limiting factor decision - Explain in which priority the three products for the coming year should be manufactured and why. - Outline any two additional factors EcoAqua should consider in arriving at the decision to allocate their scarce corn starch resource. 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 750 word limit. Background Sustainability has rapidly risen to be one of the key concerns of companies across the globe in the face of rapidly rising temperatures and thousands of tonnes of plastic waste going to landfill or destroying sensitive marine life in oceans worldwide. During your Accounting and Finance degree at university, you had been learning about the United Nations 17 sustainable development goals and were particularly concerned about goal number 12 : "Responsible consumption and production" and the need to take urgent action to prevent pollution and its devastating impacts. You were left an inheritance of 400,000 by a distant aunt and together with your friend, Olivia, you decided to use the money to set up a business to manufacture and produce biodegradable reusable plastic water bottles. Olivia holds a bachelor's degree in Environmental Science and has always been passionate about sustainability and the environment. Olivia's expertise in ecofriendly solutions and her commitment to creating a greener future make her an invaluable asset to the venture. Together you have designed a state-of-the-art bottle using biodegradable materials that are both durable and fashionable. This product stands out due to its sleek and robust design and leak-proof technology. A bottle should be reusable by the customer for around 3 years before needing replacement. By incorporating biodegradable bioplastics, you aim to reduce the environmental impact caused by traditional plastic water bottles. Biodegradable bioplastics are derived from renewable sources such as corn starch, vegetable oils, or cellulose. You are using corn starch. The materials are designed to break down naturally over time, reducing their environmental impact compared to traditional plastics. You decide to start a new company, EcoAqua Ltd, which will produce and sell your water bottles. First Steps The first step is to open the Excel file EcoAqua.xIsm 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 EcoAqua 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), recordings of the weekly lectures, pre-work mini-videos and practice exercises in the learning room each week. - Additional video tutorials on key excel functions available on NOW showing the expected profit figures you predict, and how these compare to the 300,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 1key 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) Limiting factor decision - Explain in which priority the three products for the coming year should be manufactured and why. - Outline any two additional factors EcoAqua should consider in arriving at the decision to allocate their scarce corn starch resource. Initial Capital - In July 2024 you will deposit the 400,000 from your inheritance 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 10% per annum and will paid in equal monthly instalments over the life of the loan. The first repayment will be in July 2024. Overdraft Any additional finance that your company requires will be provided by a bank overdraft facility. The bank will charge overdraft 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 (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 1st July 2024. The accounting information that you are required to prepare for the business is for the six months ending 31" December 2024. Sales As your marketing spend raises publicity for your new bottles, demand is expected to expand strongly during the 6 months ending 31st December 2024, 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 geographical area - Credit terms given by your company to customers - Frequently Asked Questions in NOW (see below) - Excel sessions run by the library - Generic feedback from the first coursework 'Winsulate' in May 2023 (see the AFM learning room), and the specific feedback you received in the dropbox, if you submitted a first attempt in May. 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 the module leader drop in sessions 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. Look at the feedback you received if this is a resit, and look at the generic feedback from the last assessment on the learning room as well. Your company will be a small manufacturing/wholesale business that rents a Business Unit that has both a production and an administration area. The company will make the bottles 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. Your Product Your products are water bottles made from biodegradable corn starch. The bottles are manufactured on an industrial scale for sale to supermarkets and department stores in different geographical regions in the U.K. The selling price your company will charge to wholesale customers will be 10 per reusable water bottle, reflecting its premium design. The Managing Director has set a challenge that the business should make a target profit of 300,000 in the first six months and you need to calculate how many items must be sold to achieve this. be required. This will be purchased in July and given 1 month's credit. 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 15,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: 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 300,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 10% 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 'limited resources' computation to show the optimal product mix and resulting profit Limited resource decision - 2025-26 In the company's second year of operation (2025-26) they have planned to massively streamline their operations, reducing fixed costs, and bringing in two further product lines to compliment their standard water bottle. These are: 1. a thermal lined water bottle to keep hot drinks hot, and 2. a fruit insert water bottle, with a mesh central compartment in which you can insert fruit (lemons, strawberries etc) to flavour your water. All 3 products use the same standard corn starch outer bottle, but in different quantities. The variable costs and forecast quantity sold per item of each of these is shown in your data set, and the quantity they forecast to sell are shown in the table below: Your plans have been disrupted however, as a severe drought has hit the main global producers of the corn which is used in your corn starch, the key ingredient of your biodegradable plastic. There is now a globally limited supply of the corn starch required to make the bottles, and you know you will have access only to 500,000kg for 2025 . You need to help the company arrive at a decision as to which product they should prioritise and predict their likely profitability in 2025-6 given the global shortage of the corn starch. REQUIRED You are required to produce an excel workbook that contains the following reports. 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 at the point of dispatch to customers. Therefore, you should calculate the total cost of the packaging each month using the number of units sold. 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 312 December 2024. You and Olivia will both 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's credit. 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 Limiting factor decision - Explain in which priority the three products for the coming year should be manufactured and why. - Outline any two additional factors EcoAqua should consider in arriving at the decision to allocate their scarce corn starch resource. 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 750 word limit. Background Sustainability has rapidly risen to be one of the key concerns of companies across the globe in the face of rapidly rising temperatures and thousands of tonnes of plastic waste going to landfill or destroying sensitive marine life in oceans worldwide. During your Accounting and Finance degree at university, you had been learning about the United Nations 17 sustainable development goals and were particularly concerned about goal number 12 : "Responsible consumption and production" and the need to take urgent action to prevent pollution and its devastating impacts. You were left an inheritance of 400,000 by a distant aunt and together with your friend, Olivia, you decided to use the money to set up a business to manufacture and produce biodegradable reusable plastic water bottles. Olivia holds a bachelor's degree in Environmental Science and has always been passionate about sustainability and the environment. Olivia's expertise in ecofriendly solutions and her commitment to creating a greener future make her an invaluable asset to the venture. Together you have designed a state-of-the-art bottle using biodegradable materials that are both durable and fashionable. This product stands out due to its sleek and robust design and leak-proof technology. A bottle should be reusable by the customer for around 3 years before needing replacement. By incorporating biodegradable bioplastics, you aim to reduce the environmental impact caused by traditional plastic water bottles. Biodegradable bioplastics are derived from renewable sources such as corn starch, vegetable oils, or cellulose. You are using corn starch. The materials are designed to break down naturally over time, reducing their environmental impact compared to traditional plastics. You decide to start a new company, EcoAqua Ltd, which will produce and sell your water bottles. First Steps The first step is to open the Excel file EcoAqua.xIsm 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 EcoAqua 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), recordings of the weekly lectures, pre-work mini-videos and practice exercises in the learning room each week. - Additional video tutorials on key excel functions available on NOW showing the expected profit figures you predict, and how these compare to the 300,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 1key 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) Limiting factor decision - Explain in which priority the three products for the coming year should be manufactured and why. - Outline any two additional factors EcoAqua should consider in arriving at the decision to allocate their scarce corn starch resource. Initial Capital - In July 2024 you will deposit the 400,000 from your inheritance 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 10% per annum and will paid in equal monthly instalments over the life of the loan. The first repayment will be in July 2024. Overdraft Any additional finance that your company requires will be provided by a bank overdraft facility. The bank will charge overdraft 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 (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 1st July 2024. The accounting information that you are required to prepare for the business is for the six months ending 31" December 2024. Sales As your marketing spend raises publicity for your new bottles, demand is expected to expand strongly during the 6 months ending 31st December 2024, 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 geographical area - Credit terms given by your company to customers - Frequently Asked Questions in NOW (see below) - Excel sessions run by the library - Generic feedback from the first coursework 'Winsulate' in May 2023 (see the AFM learning room), and the specific feedback you received in the dropbox, if you submitted a first attempt in May. 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 the module leader drop in sessions 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. Look at the feedback you received if this is a resit, and look at the generic feedback from the last assessment on the learning room as well