There is no option to upload excel file so i took photo of it. If you can tell me where i can send you excel file then i will send you. Can you help me do my homework. Thank you
Business Problem Scenario You have been approached by your client, Banana Industries Pty Ltd, to assist them in planning a new business venture. The business is planned to start on 1 July 2020. Banana Industries has developed a new corporate boardroom webcam system which will be competing with others for the massive uptake in videoconferencing technology due to Covid-19. Banana Industries requires you to prepare a budget and analysis of two separate scenarios they have determined to be likely, and have provided you with the following information. Scenario #1 Cost of goods per unit $3750 (excluding GST); mark-up 65%; expected annual sales 410 units.8% of sales revenue (excluding GST) will be spent on marketing and advertising costs. Expenses (excluding GST) Sales staff: 2 positions, $40,000 annual salary each (including superannuation). Sales commissions: 4% of sales (excluding GST). Admin' staff: 1 manager ($95,000], 1 bookkeeper [$55,000), 1 receptionist ($40,000), all salaries given are annual and include superannuation. Rent: $12,000 per month Other operating costs: $5500 per month. Delivery costs: $130 per unit sold Assets (excluding GST) Office furniture: Cost $75,000; expected residual $0; useful life 8 years; depreciation method straight-line; installed on 1 July 2020. Factory equipment: Cost $775,000; expected residual $25,000; useful life 8 years; depreciation method straight-line; installed ready for use 31 July 2020. Computers: Cost $65,000; expected residual $0; useful life 4 years; depreciation method reducing balance; installed ready for use 15 July 2020. Loan from Big Bank Banana Industries will borrow $300,000 at 4.5% p.a.compounding monthly. Repayments will be made each month, over a four-year loan term. The loan start date will be 1 July 2020. Scenario #2 The marketing department at Banana Industries believes that by selling a slightly higher quality webcam system, a higher price and mark-up would be acceptable in the market. They have estimated that with an increased cost per unit ($3900) a mark-up of 70% would be achievable. To support this price increase, marketing and advertising expenditure would increase from 8% to 11%. Annual sales are expected to increase by 10 units compared to scenario #1. Your Tasks: Part A (using the excel file provided on the Moodle] 1) Calculate the GST exclusive and GST inclusive sales price for each boardroom webcam. 2) Calculate interest expense, and depreciation expense, on the separate sheets included in the Excel file on Moodle. Don't forget to include these expenses in your P&L in the next step. 3) Produce a projected profit and loss statement for the financial year ending 30-June-2021 for each of the two scenarios. 4) Calculate on the P&L sheet, the tax payable for Banana Industries Pty Ltd (a 'base-rate' entity), and Net Profit After Tax, for each of the two scenarios. Note: For interest, depreciation, and the P&L generally, calculations to the nearest whole dollar are acceptable. For the price calculations in part A(1), and the tax/NPAT calculations at the end of the P&L, cents may be necessary. Part B [create your report in MS Word) 1) Advise your client on which scenario should be selected. Explain and justify why you would advise this selection (approx. 150 words). 2) Comment on any variances between the scenarios (approx. 150 words). 3) Explain the difference between straight-line depreciation and reducing balance depreciation to your client. Reference any materials cited (approx. 150 words). 4) Prepare break-even analysis on the scenario you recommended in part B(1) above: a. Calculate the break even analysis mathematically. b. Show the break even analysis graphically. C. Explain break-even analysis and why it is an important tool for business. (References required) (approx. 250 words). 5) Produce a Pie Graph showing the break-up of all expenses in the under the scenario recommended in part B[1] above. Comment on the pie graph (approx. 100 words). 6) From your analysis provide your recommendation to the client with supporting evidence (hints: consider contribution margins, NP and GP analysis, and the current economic outlook). Scenario Planning Depreciation Calculations Interest calculation Preted Project Pland 2012 Scani Sersante Unit Calculations Cost of Good per unit) Markup Sales Price IGSTER OST Sale Price OST) Number of lite Revue Less: Cost of Goods Sold GROSS PROFIT Weges sales start Wapes adminstall Depreciation Facity equipment Depreciation - Computers Dep. Marketing Real Operating Hat Delivery costs Interest TOTAL EXPENSES NET PROVIT . Company Tax Rate Compass Tas Payable NPAT Scenario Planning Depreciation Calculations Interest calculation Depreciation Worksheet chestire life of each Scenario Planning Depreciation Calculations Interest calculation Caution of interest and Business Problem Scenario You have been approached by your client, Banana Industries Pty Ltd, to assist them in planning a new business venture. The business is planned to start on 1 July 2020. Banana Industries has developed a new corporate boardroom webcam system which will be competing with others for the massive uptake in videoconferencing technology due to Covid-19. Banana Industries requires you to prepare a budget and analysis of two separate scenarios they have determined to be likely, and have provided you with the following information. Scenario #1 Cost of goods per unit $3750 (excluding GST); mark-up 65%; expected annual sales 410 units.8% of sales revenue (excluding GST) will be spent on marketing and advertising costs. Expenses (excluding GST) Sales staff: 2 positions, $40,000 annual salary each (including superannuation). Sales commissions: 4% of sales (excluding GST). Admin' staff: 1 manager ($95,000], 1 bookkeeper [$55,000), 1 receptionist ($40,000), all salaries given are annual and include superannuation. Rent: $12,000 per month Other operating costs: $5500 per month. Delivery costs: $130 per unit sold Assets (excluding GST) Office furniture: Cost $75,000; expected residual $0; useful life 8 years; depreciation method straight-line; installed on 1 July 2020. Factory equipment: Cost $775,000; expected residual $25,000; useful life 8 years; depreciation method straight-line; installed ready for use 31 July 2020. Computers: Cost $65,000; expected residual $0; useful life 4 years; depreciation method reducing balance; installed ready for use 15 July 2020. Loan from Big Bank Banana Industries will borrow $300,000 at 4.5% p.a.compounding monthly. Repayments will be made each month, over a four-year loan term. The loan start date will be 1 July 2020. Scenario #2 The marketing department at Banana Industries believes that by selling a slightly higher quality webcam system, a higher price and mark-up would be acceptable in the market. They have estimated that with an increased cost per unit ($3900) a mark-up of 70% would be achievable. To support this price increase, marketing and advertising expenditure would increase from 8% to 11%. Annual sales are expected to increase by 10 units compared to scenario #1. Your Tasks: Part A (using the excel file provided on the Moodle] 1) Calculate the GST exclusive and GST inclusive sales price for each boardroom webcam. 2) Calculate interest expense, and depreciation expense, on the separate sheets included in the Excel file on Moodle. Don't forget to include these expenses in your P&L in the next step. 3) Produce a projected profit and loss statement for the financial year ending 30-June-2021 for each of the two scenarios. 4) Calculate on the P&L sheet, the tax payable for Banana Industries Pty Ltd (a 'base-rate' entity), and Net Profit After Tax, for each of the two scenarios. Note: For interest, depreciation, and the P&L generally, calculations to the nearest whole dollar are acceptable. For the price calculations in part A(1), and the tax/NPAT calculations at the end of the P&L, cents may be necessary. Part B [create your report in MS Word) 1) Advise your client on which scenario should be selected. Explain and justify why you would advise this selection (approx. 150 words). 2) Comment on any variances between the scenarios (approx. 150 words). 3) Explain the difference between straight-line depreciation and reducing balance depreciation to your client. Reference any materials cited (approx. 150 words). 4) Prepare break-even analysis on the scenario you recommended in part B(1) above: a. Calculate the break even analysis mathematically. b. Show the break even analysis graphically. C. Explain break-even analysis and why it is an important tool for business. (References required) (approx. 250 words). 5) Produce a Pie Graph showing the break-up of all expenses in the under the scenario recommended in part B[1] above. Comment on the pie graph (approx. 100 words). 6) From your analysis provide your recommendation to the client with supporting evidence (hints: consider contribution margins, NP and GP analysis, and the current economic outlook). Scenario Planning Depreciation Calculations Interest calculation Preted Project Pland 2012 Scani Sersante Unit Calculations Cost of Good per unit) Markup Sales Price IGSTER OST Sale Price OST) Number of lite Revue Less: Cost of Goods Sold GROSS PROFIT Weges sales start Wapes adminstall Depreciation Facity equipment Depreciation - Computers Dep. Marketing Real Operating Hat Delivery costs Interest TOTAL EXPENSES NET PROVIT . Company Tax Rate Compass Tas Payable NPAT Scenario Planning Depreciation Calculations Interest calculation Depreciation Worksheet chestire life of each Scenario Planning Depreciation Calculations Interest calculation Caution of interest and
