show Excel step-by-step

not for any academic grading

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 statement visit https://portal.office.com to obtain Office 365 Apps for Windows and Mac OS. Office 365 for Android or iOS may not provide some features required for this project. 2 CST (Central Standard Time) is 6 hours behind the UTC (Coordinated Universal Time). Please be familiar with the local time zones you are located in.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 (.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. 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.Project Item I. Annual Manufacturing Contract PLUS Limited is a contract manufacturer of industrial materials. PLUS 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 deliveryr 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 35.000 2024 Q1 1 25.000 The contracted selling price is $45 .00 per unit. All sales are on account. PLUS Limited expects to collect the cash payments in six months for each quarterly delivery. Item 2. Direct Materials Reguiremcnts and Purchasing Policy Each unit requires 1 unit of direct materials. PLUS Limited purchases the materials for $23 per unit. PLUS Limited purchases all raw materials on account. PLUS Limited pays 50% 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 2023I Lambda expects to have 314 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. PLUS Limited has a policy of keeping a desired ending inventory of 12% of neat quarter's sales in nished goods inventory and a desired ending inventory of 10% of neat quarter's production needs for direct materials. PLUS 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 fixed 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 3. Cash Management and Debt Financing The cash management goal is to keep the cash balance above $15,{l at the end of each quarter. The company will utilize the line of credit at 2% of annual interest up to $4 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: Cash Accounts Payable Accounts Receivable ill Raw Material Inventory 21,3011} Finished Goods inventory {It Capital Stock 6005110 Buildings and Equipment llg Retained Etunings [assoc Accumulated Degreciation {MUM} Total Liabilities and Shareholder's Smith} Equity . it These balance sheet l'igurcs must be taken as given. Negative balances are in the parentheses. Accounts payablcs are for direct materials; to be paid in cash in Q: 3123 Total Assets $310 .3th