A:18 . B:1 .C:20

please solve all i will give u like

DUE DATE: 11 am Friday 29th October 2021 One of your friends, Emma, has found out you are studying accountancy and that you have acquired basic spreadsheet skills. She has decided to start a business of printing bespoke logos on to tennis racquets but needs to produce a business plan for the bank manager in order to raise a loan. Emma has already formed a private limited company and has introduced 5,000 in to the business bank account. Of this 5,000, 1,000 is to be the initial share capital and the balance of 4,000 is a short-term interest free loan from her. It is her intention to start trading as from 1 January 2022. You have agreed to produce the financial projections for her based on the information provided by Emma. . REQUIRED: 1. Using Excel, construct a concise financial model that includes, as a minimum, the projected cash flow, profit and loss and balance sheet figures for the first year of trading i.e. until 31 December 2022. The statements should show quarterly figures with annual totals, where appropriate. (60 marks) (Marks will be awarded for the use of spreadsheet logic, presentation and layout of the projections and ease of use) 2. Using either Word or Excel, write a short report to Emma to include the following: 4. Using your model produce financial ratios, which may be useful to Emma bearing in mind the reason for the projections. The report should explain why you have selected these ratios and the relevance of the trends to Emma. Include any other comments you consider would be relevant to Emma. b. Emma has expressed an interest in using the model to see the financial impact of changing some of the assumptions. Write a concise user guide (report) on how she would use the model to see the impact of changing the gross margin and other assumptions. Do remember Emma has limited Excel knowledge. c. Explain to Emma the advantages and disadvantages of using Excel to produce the financial projections as compared to producing them manually using "pen and ink" (40 marks) (Total : 100 marks) (Total : 100 marks) (In addition to the technical content marks will be awarded for presentation) 1 Submission Instructions: 1. Using the links provided on Canvas submit the final version of your financial projections (spreadsheet) in a suitable format to enable Emma to show them to her bank manager - Section 1. Include your workings as part of this file and clearly label them so as to differentiate them from the main financial projections. To ensure correct identification of your file, ensure your registration number is on the file heading and is on each worksheet 2. Using the links provided on Canvas submit the final version of your report to Emma as required in Section 2. To ensure correct identification of your file, ensure your registration number is on the file heading and is clearly visible in the report. 3. Retain a copy of your final version of your financial projections (spreadsheet) and your report on a USB and on your computer/university hard drive. It is ESSENTIAL that this version is the same as the one submitted to Canvas and that it has a 'date modified date that is before the date you submitted your work to Canvas. In the unlikely event there is an issue with your submitted version these file(s) will be required to be graded The 'date modified' date will be used to determine whether or not any late submission penalty is to be applied. It is your responsibility to ensure this date is not subsequently overridden It is your responsibility to ensure the electronic versions of your answers to Sections 1 and 2 are submitted via the link on Canvas by the submission date. Late submission of the files will incur penalties in line with the department policy. Information provided by Emma: Please note that each student will be working with a slightly different set of information. There is a unique set of the three parameters A, B and C for each student. You can find your particular values on Canvas. Look in the parameters file in the Excel Coursework Assignment folder. 1. The first quarter's sales are expected to be 3,000 doubling every quarter during the year. 2. The gross profit margin on sales is expected to be 40%. 3. Of each quarter's sales, A% are cash sales. 4. Bad debts are on credit sales, and are anticipated to be B% each quarter. 5. 50% of the credit sales are paid in the quarter in which the sale is made. The remaining debtors, after bad debts, are collected in the following quarter. 6. Sufficient stock is purchased to cover the next quarter's sales e.g. the stock for the sales in the fourth quarter are purchased in the third quarter. In the first quarter assume the first two quarter's purchases are made and assume the final quarter's purchases are 30,000 (the sales for the first quarter of 2023 are unknown). 7. Purchases are paid for in the quarter bought (ie. There are no trade creditors). 2 sales for the first quarter of 2023 are unknown). 7. Purchases are paid for in the quarter bought (i.e. there are no trade creditors). 2 8. Gross staff salaries are 2,100 per quarter and paid in the quarter incurred. 9. Distribution costs are 10% of sales and are paid in the quarter of the sale. 10. Bank interest charged is 2% per quarter calculated on the balance at the end of the previous quarter. It is payable in the following quarter i.e. the quarter in which it is charged. No interest is receivable on credit (positive) balances. 11. The company buys machinery costing 10,000 in the first quarter. 12. A van costing 8,000 is purchased in the third quarter. 13. The company depreciation policy is to depreciate assets at 0% per annum on a straight-line basis. 14. The company incurs a quarterly telephone bill of 525, which is usually paid in the quarter it is incurred. However, the fourth quarter's bill is 600 and will not be paid until January 2023. 15. Ignore the effect of corporation tax, VAT, PAYE and inflation. 16. Include any other expenses Emma has obviously omitted from the above information. 14. The company incurs a quarterly telephone bill of 525, which is usually paid in the quarter it is incurred. However, the fourth quarter's bill is 600 and will not be paid until January 2023. 15. Ignore the effect of corporation tax, VAT, PAYE and inflation. 16. Include any other expenses Emma has obviously omitted from the above information. Technical hint: The level of knowledge of double-entry bookkeeping required to answer this question is fairly basic and is not designed to 'trip you up'. Remember that Cost of Sales - Opening Stock + Purchases - Closing Stock DUE DATE: 11 am Friday 29th October 2021 One of your friends, Emma, has found out you are studying accountancy and that you have acquired basic spreadsheet skills. She has decided to start a business of printing bespoke logos on to tennis racquets but needs to produce a business plan for the bank manager in order to raise a loan. Emma has already formed a private limited company and has introduced 5,000 in to the business bank account. Of this 5,000, 1,000 is to be the initial share capital and the balance of 4,000 is a short-term interest free loan from her. It is her intention to start trading as from 1 January 2022. You have agreed to produce the financial projections for her based on the information provided by Emma. . REQUIRED: 1. Using Excel, construct a concise financial model that includes, as a minimum, the projected cash flow, profit and loss and balance sheet figures for the first year of trading i.e. until 31 December 2022. The statements should show quarterly figures with annual totals, where appropriate. (60 marks) (Marks will be awarded for the use of spreadsheet logic, presentation and layout of the projections and ease of use) 2. Using either Word or Excel, write a short report to Emma to include the following: 4. Using your model produce financial ratios, which may be useful to Emma bearing in mind the reason for the projections. The report should explain why you have selected these ratios and the relevance of the trends to Emma. Include any other comments you consider would be relevant to Emma. b. Emma has expressed an interest in using the model to see the financial impact of changing some of the assumptions. Write a concise user guide (report) on how she would use the model to see the impact of changing the gross margin and other assumptions. Do remember Emma has limited Excel knowledge. c. Explain to Emma the advantages and disadvantages of using Excel to produce the financial projections as compared to producing them manually using "pen and ink" (40 marks) (Total : 100 marks) (Total : 100 marks) (In addition to the technical content marks will be awarded for presentation) 1 Submission Instructions: 1. Using the links provided on Canvas submit the final version of your financial projections (spreadsheet) in a suitable format to enable Emma to show them to her bank manager - Section 1. Include your workings as part of this file and clearly label them so as to differentiate them from the main financial projections. To ensure correct identification of your file, ensure your registration number is on the file heading and is on each worksheet 2. Using the links provided on Canvas submit the final version of your report to Emma as required in Section 2. To ensure correct identification of your file, ensure your registration number is on the file heading and is clearly visible in the report. 3. Retain a copy of your final version of your financial projections (spreadsheet) and your report on a USB and on your computer/university hard drive. It is ESSENTIAL that this version is the same as the one submitted to Canvas and that it has a 'date modified date that is before the date you submitted your work to Canvas. In the unlikely event there is an issue with your submitted version these file(s) will be required to be graded The 'date modified' date will be used to determine whether or not any late submission penalty is to be applied. It is your responsibility to ensure this date is not subsequently overridden It is your responsibility to ensure the electronic versions of your answers to Sections 1 and 2 are submitted via the link on Canvas by the submission date. Late submission of the files will incur penalties in line with the department policy. Information provided by Emma: Please note that each student will be working with a slightly different set of information. There is a unique set of the three parameters A, B and C for each student. You can find your particular values on Canvas. Look in the parameters file in the Excel Coursework Assignment folder. 1. The first quarter's sales are expected to be 3,000 doubling every quarter during the year. 2. The gross profit margin on sales is expected to be 40%. 3. Of each quarter's sales, A% are cash sales. 4. Bad debts are on credit sales, and are anticipated to be B% each quarter. 5. 50% of the credit sales are paid in the quarter in which the sale is made. The remaining debtors, after bad debts, are collected in the following quarter. 6. Sufficient stock is purchased to cover the next quarter's sales e.g. the stock for the sales in the fourth quarter are purchased in the third quarter. In the first quarter assume the first two quarter's purchases are made and assume the final quarter's purchases are 30,000 (the sales for the first quarter of 2023 are unknown). 7. Purchases are paid for in the quarter bought (ie. There are no trade creditors). 2 sales for the first quarter of 2023 are unknown). 7. Purchases are paid for in the quarter bought (i.e. there are no trade creditors). 2 8. Gross staff salaries are 2,100 per quarter and paid in the quarter incurred. 9. Distribution costs are 10% of sales and are paid in the quarter of the sale. 10. Bank interest charged is 2% per quarter calculated on the balance at the end of the previous quarter. It is payable in the following quarter i.e. the quarter in which it is charged. No interest is receivable on credit (positive) balances. 11. The company buys machinery costing 10,000 in the first quarter. 12. A van costing 8,000 is purchased in the third quarter. 13. The company depreciation policy is to depreciate assets at 0% per annum on a straight-line basis. 14. The company incurs a quarterly telephone bill of 525, which is usually paid in the quarter it is incurred. However, the fourth quarter's bill is 600 and will not be paid until January 2023. 15. Ignore the effect of corporation tax, VAT, PAYE and inflation. 16. Include any other expenses Emma has obviously omitted from the above information. 14. The company incurs a quarterly telephone bill of 525, which is usually paid in the quarter it is incurred. However, the fourth quarter's bill is 600 and will not be paid until January 2023. 15. Ignore the effect of corporation tax, VAT, PAYE and inflation. 16. Include any other expenses Emma has obviously omitted from the above information. Technical hint: The level of knowledge of double-entry bookkeeping required to answer this question is fairly basic and is not designed to 'trip you up'. Remember that Cost of Sales - Opening Stock + Purchases - Closing Stock