Answer Happy

SCENARIO Having been in operation for Two(2) mouth, Wina Bwangu is concerned if it will possibly be able to manage to handle its monthly obligations and if the business will be sustainable by the by December 2022 given the sales for the March and April 2022 are as shown in Table I below. Mar-22 Apr-22 Winal 2556.405 2914.302 Wina2 5675.615 6470.201 Wina3 13448.155 15330.9 Wina4 5693.225 6490.277 Winas 2530.995 2885.334 Wina6 2129.3 2427.402 Table 1: Sales for March and April 2022 Direct labour costs are calculated as follows: The Office Manager is paid a Basic pay of K6500 per month. • The Basic pay for each Agent is pegged at the minimum wage for working for 8 hours on Monday to Friday, and 4 hours on Saturday. • Additionally, each Agent is paid an extra amount of 107% of the hourly rate of the Basic pay. The overtime hours that were accumulated in the 2 mouths are as shown in Table 2. Mar-22 Apr-22 Winal 8 7 Wina2 5 4 Wina 3 12 10 Wina4 6 6 Wina5 8 5 Winаб 6 6 Table 2: Accumulated Overtime Hours for March and April 2022 Other costs are expected as follows: Cost of each Transaction K0.0012 per kwacha The Council Rates for each booth K5 per day K12,000 per year Repairs and maintenance for the booths Insurance K1,200 per year per booth Office Expenses K4,600 per month
Other information i The company used a total of the K45,000 to have the booths made and installed at all the six locations. iii Each Agent and the Office Manager has been given a Phone worth K360 For taxation purposes, depreciation on equipment is based on a 2-year useful life, and is calculated by the straight-line method. iv The company's current tax rate is for small to medium size entrepreneurs. The company's cost of capital is the current BOZ base rate. V REQUIRED: Develop a Financial Model in MS Excel using both absolute and relative referencing as follows; a) Develop the monthly payroll obligations for the mouths of March and April 2022 considering the Direct labour cost calculations including the current Napsa and ZRA obligation according to the current regulations. (10 marks) b) Using the Sales, the Costs and the other considerations produce consolidated monthly cash flow projects for 2022 and given that the company injected a working capital of K300,000 which should be fully recovered at the end of 2021 and the sales and variable costs are expected to increase at the rate of inflation for Quarter 1 of 2022. (10 marks) c) On the Dashboard worksheet and using Excel formulas and relative as well as absolute referencing calculate the following for each project as the outputs of your model i. Payback Period ii. Profitability Index (PI) iii. Internal Rate of Return iv. Net Present Value (16 marks) d) Using the Scenario Manager under the What-if-Analysis, perform Sensitivity Analysis showing the effect on the Monthly Net Cash flows for three cases as follows: i. Base Case Scenario that records the Status Quo. ii. Best Case Scenario if the rate of inflation for Quarter 1 of 2022 reduces by an average 4% by the end of the year 2022. iii. Worst Case Scenario if the rate of inflation for Quarter 1 of 2022 increases by an average 7% by the end of the year 2022 (4 marks) Prepare a report to submit to the Finance Committee at its next meeting. The report should include the following:
i. an "Executive Summary" at the start of the report which is a brief statement (one page) summarising the entire assignment. ii. a clear statement of the assumptions made, if any. iii. Screenshot from the model will be helpful more effectively communicate the results to the finance committee. (10 marks) (Total: 50 marks)