. Base Budget Prepare a master budget for PLOS Limited for the period from Q2 2023 to Q1 2024 covered by he 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 statement n. Pro forma classified 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 flow 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 first 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 Profit Less: Selling, General, and Administrative Expenses Operating Income Less: Interest Expenses Income before Taxes Less: Tax Expenses (Provision for income taxes) Net Income 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 (.xlsx) 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. Report Instructions: Your report should be one page (1-inch margins, 12 font size, single spacing, New Times Roman font). The report will be evaluated on content, writing, and presentation (grammar, spelling, wordiness, and persuasiveness). Your report should include only the executive summary. Any identification information (e.g., title, author) should be on the header of the document. The executive summary should reveal the problem, solution, and support for the solution. Presenting a context of the budget case without a solution or following support is not an executive summary. Your budgets will be regarded as an appendix of the report. You must refer to the content of your own excel spreadsheet when you need to discuss it in the report. 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 first quarter of 2023, the annual contract specifies 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: 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, PLOS Limited 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 finished 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: 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 fixed selling and administration expenses are as follows: 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 simplified balance sheet as of March 31,2023 is as follows: These balance sheet figures 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 . Base Budget Prepare a master budget for PLOS Limited for the period from Q2 2023 to Q1 2024 covered by he 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 statement n. Pro forma classified 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 flow 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 first 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 Profit Less: Selling, General, and Administrative Expenses Operating Income Less: Interest Expenses Income before Taxes Less: Tax Expenses (Provision for income taxes) Net Income 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 (.xlsx) 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. Report Instructions: Your report should be one page (1-inch margins, 12 font size, single spacing, New Times Roman font). The report will be evaluated on content, writing, and presentation (grammar, spelling, wordiness, and persuasiveness). Your report should include only the executive summary. Any identification information (e.g., title, author) should be on the header of the document. The executive summary should reveal the problem, solution, and support for the solution. Presenting a context of the budget case without a solution or following support is not an executive summary. Your budgets will be regarded as an appendix of the report. You must refer to the content of your own excel spreadsheet when you need to discuss it in the report. 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 first quarter of 2023, the annual contract specifies 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: 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, PLOS Limited 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 finished 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: 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 fixed selling and administration expenses are as follows: 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 simplified balance sheet as of March 31,2023 is as follows: These balance sheet figures 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