I need you to fill in the Questions in the Excel Spredsheet. Follow the Word document for context to the questions. Thanks
Cover Sheet Created by Sam Menai_04/04/2023 Assessment task 2 Finance and resources management Semester 2 - 2023 Student Name: Student ID: Class #: Teacher name &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# STUDENT answer sheet Question 1 and 2 Question 1 As per the financial statements listed above (P&L and balance sheet), does Smart Accessories Pty Ltd use the Cash or Accrual Accounting method? Is the method used appropriately given their business operations? Provide an in-context explanation for your response. Answer 1 Question 2 Given the business structure of Smart Accessories Pty Ltd, find the appropriate Income Tax Rate. Is this the tax rate used above (in the profit and loss statement)? Answer 2 &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# Question 3 Question 3 To monitor Smart Accessibility Pty Ltd’s budgets using the information data we gave you, construct a quarterly budget for your department using Excel. Equation functions must be used for all calculations. The Sales Revenue, the Cost of Sales, wages, bad debts, and other operating expenses are expected to fluctuate throughout the year In the first quarter, you expect a 20% drop and in the fourth quarter, you expect a 25% drop In the second quarter, heading towards Christmas, you expect an increase of 30%, and in the third quarter an increase of 15% from the average. Wholesale Department Budget 2021-22 Movement %20% drop30% increase15% increase25% drop AnnualQ1Q2Q3Q4 Credit Sales Revenue$ 6,000,000$1,200,000$1,950,000$1,725,000$1,125,000 Cost of Sales-$ 3,100,000 Wages-$ 800,000 Telephone-$ 50,000 Travel-$ 35,000 Office Supplies-$ 7,000 Bad Debts-$ 150,000 Other Operating Expenses-$ 125,000 Departmental Profit$ 1,733,000$ 1,200,000$ 1,950,000$ 1,725,000$ 1,125,000 &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# Question 4 Question 4 Describe two of the Excel equations you have used and why you have used them. &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# Question 5 Question 5 Instead of a 20% drop in quarter one, there has been a 30% drop, however, Travel Expenses have decreased by 25% from the a) budgeted average, bad Debts for the period have been ($40,000), and Telephone ($10,000). Using Excel and techniques, compare the budgeted figures for the first quarter to the actual figures, and Bad Debts AnnualQ1 BudgetedActualVariance $Variance %U or Fthere is a higher risk of not collecting our money Credit Sales Revenue$ 6,000,000$1,200,000$ 1,050,000$150,00012.50%there is a potential risk of financial distress Cost of Sales-$ 3,100,000$0$0ERROR:#DIV/0!there is also a risk of lack of liquidity Wages-$ 800,000$0$0ERROR:#DIV/0! Telephone-$ 50,000$0$0ERROR:#DIV/0!Credit sales revenue Travel-$ 35,000$0$0ERROR:#DIV/0!less sales may equal less profit Office Supplies-$ 7,000$0$0ERROR:#DIV/0!market share Bad Debts-$ 150,000$0$0ERROR:#DIV/0!products/pricing issues Other Operating Expenses-$ 125,000$0$0ERROR:#DIV/0! Departmental Profit$ 1,733,000$1,200,000$ 1,200,000$00%Recommandation b) Report and analyse any ‘high priority’ variation in your budgeted figures. Why do you think these are of high priority, What are some possible reasons for this variation? c) What recommendations and actions would you take to bring the budget back under control for the remaining quarters? How would you go about implementing these recommendations within your team? Draft a short report outlining your finding. short report: &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# Question 6 Question 6 From your experience, you estimated that 75% of the Debtors ledger balance will pay within the first month from the sale, 16% within the second month, 7% within the third, and 2% will be written off as bad debts. Your Credit Terms with your suppliers stipulate you must pay within 45 days of purchasing. a) Using Excel, complete a quarterly Aged Debtors Budget for your department using the information above as well as any required information provided in previous sections. Equation functions must be used for all calculations. AnnualQ1Q2Q3Q4 Credit Sales Revenue$6,000,000$1,200,000$1,950,000$1,725,000$1,125,000 Current75%$900,000 30 days16%$180,000 60 days7%$120,750 Bad Debts2% b) At the end of the first quarter, you find the total owing by debtors is $555,000. Using your previously calculated actual sales for the quarter calculate the Debtors’ Aging Ratio for the quarter. Please use the following debtor Debtor aging formula: DAR = (Debtor’s balance/Total sales) x (365/4). Express your result in DAYS. For example, if result is 32, then you need to express it as 32 days. Step 1: Debtor balance Step 2: Sales figureGet this figure from question 5, QRT sales actual result. Step : RationERROR:#DIV/0!days Debtor aging formula: DAR = (Debtor’s balance/Total sales) x (365/4) c) How would these findings affect the business’s cash flow position? What recommendations would you make based on these? How is your recommendation likely to affect contingency planning? d) To implement these recommendations, write a formal email to your team (and copy in your manager) advising that the recommendations will commence in a week. Submit a copy of the email. (approximately 25-50 words) The email must be written using appropriate business protocol e.g. no abbreviations, capital I, correct grammar, no spelling errors, salutation (Dear Lee), and sign-off must be your name, title, and contact details. &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# Question 7 Question 7 a) Prepare your contingency plan to implement three potential risks that could affect your department using the table below. You must provide information in each of the columns. Risks IdentifiedRisk LevelPotential ImpactMitigation StrategyContingency Plan Customers (Debtors) do not pay by the due dateHighLoss of revenueCredit Checks prior to making credit arrangementsSend out reminder letters - collection phone calls - take further legal action 2) 3) &"Calibri"&12&KEEDC00RMIT Classification: Trusted&1# RMIT Classification: Trusted STUDENT – PRODUCT ASSESSMENT TASK Task Number 2 of 3. Task Name Short Answers Portfolio National unit/s code BSBOPS501 BSBFIN501 National unit/s title Manage Business Resources Manage Budgets and Financial plans National qualification code BSB50120 National qualification title Diploma of Business RMIT Program code C5406 RMIT Course code BUSM6517C ACCT5434C Section A – Assessment Information Assessment duration and/or due date Due 23 April, Sunday 11:59pm Task instructions Type of Product (tick which applies) ☐ Project ☐ Report Portfolio ☐ Case study Summary and Purpose of Assessment This assessment is Assessment Task two (2) of three (3) assessment tasks in this cluster of two courses: BSBOPS501 Manage Business Resources and BSBFIN501 Manage Budgets and Financial plans. All three (3) Assessment Tasks must be satisfactorily completed to be deemed competent in these two courses. The purpose of this assessment is to for you to demonstrate your understanding and application of a range of financial planning approaches. This includes monitoring and controlling budgets and financial plans, and reviewing and evaluating financial management process and business resources. You will be required to analyse business resources requirement, conduct variance analysis, review and report on resource usage, review company performance and develop contingency plans, and collaboration with their team members. Assessment Instructions What This assessment will be using a scenario that enables students to demonstrate the necessary skills to manage budgets and financial plans, and business resources. You must work in groups of 3 so you can complete parts of this assessment. This will be done via meetings during class/online. Please note that whilst there will be group discussion, consultation ,and collaboration, you are required to complete your own assessment as an individual and not as a group assessment. You must answer all 7 questions by the due date. You are encouraged to complete two questions every second week in order to break down the task and make it more manageable. A recommended word range is provided next to each question as a guide. Answers can be typed into a word document and submit responses to all questions in Canvas. This scenario “Smart Accessories Pty Ltd” has been derived from the main fictional “Stingray Corporation” an overarching case study used throughout the diploma of business. Section 1 - Smart Accessories Pty Ltd Stingray’s Smart Watch Business Unit also operates Smart Accessories Pty Ltd a mid-sized wholesale watch accessory based in the western suburbs of Melbourne. As well as their wholesale business, they have a small retail operation facility where they sell a select number of specialist varieties of watch bands. The following financial statements for the 2020/21 tax year have been provided. A full copy of the case study can be downloaded from Canvas. Smart Accessories Pty Ltd Balance Sheet as at 30/6/21 Owner’s Equity $ $ Capital 2,766,000 Add: Net Profit 469,000 3,235,000 Less: Drawings 350,000 2,885,000 Assets Current Assets Cash at Bank 200,000 Debtors Control 350,000 Stock Control 550,000 1100,000 Non-Current Assets (Fixed Assets) Premises 5,000,000 Equipment 1,000,000 Vehicles 750,000 6,750,000 Total Assets 7,850,000 Liabilities Current Liabilities Creditors Control 915,000 GST Clearing 50,000 965,000 Non-Current Liabilities Loan XYZ Bank 4,000,000 Total Liabilities 4,965,000 Net Assets (Assets less Liabilities) 2,885,000 Smart Accessories Pty Ltd Profit and Loss Statement For the year ended 30/6/21 Revenue $ $ Retail Cash Sales 1,050,000 Wholesale Credit Sales 5,450,000 Interest Received 10,000 6,510,000 Less Cost of Sales Cost of Goods Sold 3,200,000 Cartage Inward 150,000 3,350,000 Gross Profit 3,160,000 Less Expenses Wages & Salaries 1,055,000 Interest Paid 200,000 Other Operating Expenses 985,000 Bad Debts 100,000 Depreciation 150,000 2,490,000 Net Profit Before Tax 670,000 Income Tax Expenses 201,000 Net Profit After Tax 469,000 Use the above information to assist you in answering the following questions. You will also be required to use the ATO website to research some of the answers. Where appropriate, provide details of where you found your information and any calculations performed. Question 1: As per the financial statements listed above (P&L and balance sheet), does Smart Accessories Pty Ltd use the Cash or Accrual Accounting method? Is the method used appropriately given their business operations? Provide an in-context explanation for your response. Question 2: Given the business structure of Smart Accessories Pty Ltd, find the appropriate Income Tax Rate. Is this the tax rate used above (in the profit and loss statement)? Section 2 – Construct and monitor budget You are the Sales Manager for the Wholesale Department at Smart Accessories Pty Ltd. You have a team of 7 salespeople and one supervisor who assists in the filling of orders. You have been given the following departmental budget to follow for the 2020/21 financial year. Wholesale Department Budget 2020/21 Credit Sales Revenue $6,000,000 Cost of Sales ($3,100,000) Wages ($800,000) Telephone ($50,000) Travel ($35,000) Office Supplies ($7,000) Bad Debts ($150,000) Other Operating Expenses ($125,000) Departmental Profit $1,733,000 Due to the seasonality nature of this business, the Sales Revenue, the Cost of Sales, wages, bad debts, and other operating expenses are expected to fluctuate throughout the year. In the first quarter, you expect a 20% drop and in the fourth quarter, you expect a 25% drop from the average. In the second quarter, heading towards Christmas, you expect an increase of 30%, and in the third quarter an increase of 15% from the average. You will need to monitor compliance with the project budgets and take corrective action that you feel is appropriate by performing the following tasks. Question 3: To monitor Smart Accessibility Pty Ltd’s budgets using the information data above, construct a quarterly budget for your department using Excel. Equation functions must be used for all calculations. Question 4: Describe two of the Excel equations you have used and why you have used them. Question 5: Actuals for Quarter one (1) Instead of a 20% drop in quarter one, there has been a 30% drop, however, Travel Expenses have decreased by 25% from the budgeted average, bad Debts for the period have been ($40,000), and Telephone ($10,000). a) Using Excel and techniques, compare the budgeted figures for the first quarter to the actual figures, and conduct variance analysis in dollars and in percentages. Equation functions must be used for all calculations. b) Report and analyse any ‘high priority’ variation in your budgeted figures. Why do you think these are of high priority? What are some possible reasons for this variation? c) What recommendations and actions would you take to bring the budget back under control for the remaining quarters? How would you go about implementing these recommendations within your team? Draft a short report outlining your finding. Question 6: From your experience, you estimated that 75% of the Debtors ledger balance will pay within the first month from the sale, 16% within the second month, 7% within the third, and 2% will be written off as bad debts. Your Credit Terms with your suppliers stipulate you must pay within 45 days of purchasing. a) Using Excel, complete a quarterly Aged Debtors Budget for your department using the information above as well as any required information provided in previous sections. Equation functions must be used for all calculations. b) At the end of the first quarter, you find the total owing by debtors is $555,000. Using your previously calculated actual sales