2. Alternative Business Scenario for Alternative Excel Sheet(s) and Report PLOS Limited foresees a shortage of material supply. As a result, the company will deliver only 20,000 units in the second and third quarters of 2023. The company will be able to meet the scheduled delivery for the rest of the contract year. The downturn in production will significantly deteriorate the company's profitability, so the management is considering a permanent downsizing of its labor force. A layoff will save $200,000 in salary expenses annually but will reduce the firm's production capacity to 70% in the following year (from Q2-2024 to Q1-2025). The annual contract size will also be reduced to 70% of the current amount. Should the company downsize its labor force or not? Instructions for preparing Excel spreadsheets: 1. Develop your entire master budget in one worksheet; i.e., present your beginning balance sheet first, then present your sales budget below it and the next budget below, etc. All information should be presented at the top of the workbook or next to each budget. The master budget following the alternative scenario should be in another worksheet. 2. When you have finalized the master budget for the base case scenario, start working on Requirement 2. First, copy the master budget onto a new worksheet (i.e., a new tab). Then make the necessary changes on the copied worksheet. If you have fully programmed the first worksheet, your changes will be immediately reflected on the master budget. Label the worksheet tabs.3. This project likely to require two modifications of the base budget with an extended budget period: a. Master Budget for an alternative scenario for Q2-2023 - Q1-2025 with the layoff b. Master Budget for an alternative scenario for Q2-2023 - Q1-2025 without the layoff Assume all assumptions continue to hold and the annual contract remains unchanged for the following year. 4. Part of your mark will depend on how well you link the parts of your spreadsheets. 5. You must upload an electronic copy of your budget file (.xIsx) and report (.docx or .pdf) on Canvas 6. Do not round any numerical values; Instead, use appropriate display formats via "Format Cells" or shortcuts for display formats to indicate unit of each quantity or to determine the number of decimal places displayed.Project Item 1. Annual Manufacturing Contract PLOS Limited is a contract manufacturer of industrial materials. PLOS Limited supplies a construction material to its major buyer under an annual contract. As of the rst quarter of 2023, the annual contract species the volume, selling price, and the timing of the delivery from April 2023 to March 2024. The scheduled delivery in units for each quarter is the following: 2023 Q2 45,000 2023 Q3 35,000 2023 Q4 85,000 2024 Q1 25,000 The contracted selling price is $45.00 per unit. All sales are on account. PLOS Limited expects to collect the cash payments in six months for each quarterly delivery. Item 2. Direct Materials Requirements and Purchasing Policy Each unit requires 1 unit of direct materials. PLOS Limited purchases the materials for $28 per unit. PLOS Limited purchases all raw materials on account. PLOS Limited pays 60% of a quarter's purchases in cash during the quarter of purchase and the rest in the next quarter. Item 3. Inventory Stock and Policy At the beginning of Q2 2023, Lambda expects to have 814 units of materials in inventory. Assume constant material purchasing prices and inventory values for the purpose of budgeting. The manufacturing process follows a short cycle of assembly completed within a day, and therefore there are no work in progress inventories. PLOS Limited has a policy of keeping a desired ending inventory of 12% of next quarter's sales in nished goods inventory and a desired ending inventory of 10% of next quarter's production needs for direct materials. PLOS Limited expects the contracted sales for Q2 and Q3 of the year 2024 would be identical to the previous year. Item 4. Direct Labour The completion requires 0.5 hours of direct labour at a rate of $15 per hour. Item 5. Fixed Overhead The annual budget for fixed manufacturing overhead items follows: Factory salaries $900,000 Property taxes 26,000 Insurance 28,800 Maintenance 80,000 Utilities 300,000 Depreciation 10,000 Total $1,344,800 Fixed overhead is applied to production using a predetermined overhead rate based on the estimated annual production volume. All fixed overheads are paid in cash evenly each quarter.Item 6. Selling and Administrative Expenses Annual xed selling and administration expenses are as follows: Sales salaries $45,000 Administration salaries 45,000 Travel 1,300 Insurance 3 ,400 Utilities 2,400 Depreciation 8,000 Total $105,100 Fixed selling and administration expenses are paid in cash evenly over the four quarters of the year. Item 7. Income Tax Payment PLOS Limited usually makes income tax payments for the previous year's taxable income in the second quarter every year. The company is subject to a 25% tax rate. However, the company already paid all income taxes for the previous year (2022) at the end of Q1 2023. Item 8. Cash Management and Debt Financing The cash management goal is to keep the cash balance above $15,000 at the end of each quarter. The company will utilize the line of credit at 2% of annual interest up to $4,000,000 to meet the cash balance target. Assume that the borrowing occurs at the end of the quarter. Interest payments occur at the end of the quarter. Item 9. Balance Sheet as of March 31, 2023 The company's simplied balance sheet as of March 31, 2023 is as follows: Cash $50,000 Accounts Payable $74,300 Accounts Receivable 0 Raw Material Inventory 22,800 Finished Goods Inventory 0 Capital Stock 600,000 Buildings and Equipment 1'000'08 Retained Earnings 198,500 Accumulated Depreciation (200'000) Total Assets $872,800 :05? L1ab111t1es and Shareholder s $872,8g q_f% These balance sheet gures must be taken as given. Negative balances are in the parentheses. Accounts payables are for direct materials; to be paid in cash in Q2 2023 Required: 1. Base Budget Prepare a master budget for PLOS Limited for the period from Q2 2023 to Q1 2024 covered by the annual contract. The following components must be included: a. Beginning balance sheet (disaggregate asset accounts if possible) b. Sales budget c. Schedule of expected cash collection d. Production budget e. Direct materials purchases budget f. Schedule of disbursements for materials g. Direct labour budget h. Overhead budget (be sure to show cash disbursements for variable and fixed overheads, in addition to applied variable and fixed overhead expenses). i. Selling and administrative budget (be sure to show cash disbursements for selling and administrative expenses). j. Cash budget Prepare the following for the year. k. Cost of goods manufactured budget 1. Cost of goods sold budget m. Pro forma income statement11. Pro forma classied balance sheet You should complete the cash budget, the pro forma income statement, and the pro forma balance sheet by building Excel formulas to account for information ow between three statements. For example, provisions for income tax or tax expenses in the income statement depend on the taxable income, which depends on interest expenses determined in the cash budget. Completing cash budget and pro forma income statement rst allows you to complete the pro forma balance sheet. Please use the following structure for the income statement: Sales Less: COGS (Cost of Goods Sold) Gross Prot Less: Selling, General, and Administrative Expenses Operating Income Less: Interest Expenses Income before Taxes Less: Tax Expenses (Provision for income taxes) Net Income