Required: 1. Base Budget Prepare a master budget for Lambda Inc. for the period covered by...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Required: 1. Base Budget Prepare a master budget for Lambda Inc. for the period covered by the annual contract (for the year and for each quarter). 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 cas! administrative expenses). j. Cash budget disbursements for selling and Prepare the following for the year. k. Cost of goods manufactured budget 1. Cost of goods sold budget m. Pro forma income statement (using absorption costing) 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 Incom 2. Alternative Budget In a recent meeting, the buyer indicated a possibility of a large special order of 100,000 units of Z350 for $35 per unit. The special order is to be delivered in Q4 2022. Lambda Inc., considering fulfilling some of the special orders, estimates that the unused capacity for 500 units will be available in Q4 2022. Prepare a master budget under a scenario of accepting the special order. 3. Report 1) Should Lambda Inc. accept the special order? Based on the estimated financial impact of the special order on the master budget, determine whether Lambda Inc. should accept the special order. Assume that the special order also follows a cash collection schedule identical to that of regular orders under the annual contract. 2) The buyer asks Lambda Inc. whether the special order price can go lower. The buyer wants to minimize the required capital for a massive sales and promotion campaign, which is essentially the material cost of Z350 to the buyer. The buyer proposes to share some of the excess profits from the event. The share for suppliers will be proportional to the cost-saving each supplier can provide to the buyer by participating in the special order. Lambda Inc., considering the proposal, wants to gather accounting information to help decision-making. Lambda Inc. wants to set a low price to increase the expected profit from the profit-sharing as long as the selling price is high enough to avoid a loss in the special order. What is the lowest possible price Lambda Inc. can set for the special order? Use the information available in the base and the alternative budgets. 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 and the title of the worksheet. 5. 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 Lambda Inc. is a contract manufacturer of consumer electronics. Lambda Inc. supplies a component (Z350) to its major buyer under an annual contract. As of the first quarter of 2022, the annual contract specifies the volume, selling price, and the timing of the delivery from April 2022 to March 2023. The scheduled delivery of Z350 in units for each quarter is the following: 2022 Q2 2022 Q3 2022 Q4 2023 Q1 4,500 3,500 8,500 2,500 The contracted selling price is $45.00 per unit. All sales are on account. Lambda Inc. expects to collect the cash payments in six months for each quarterly delivery of Z350. Item 2. Direct Materials Requirements and Purchasing Policy Each unit of Z350 requires two materials: a PCB module and 3 grams of TPU 95A. Lambda Inc. purchases the PCB for $12 per unit and TPU 95A for $0.2/gram. Lambda Inc. purchases all raw materials on account. Lambda Inc. 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 2022, Lambda expects to have 1,900 PCBS and 7,500 grams of TPU 95A 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. Lambda Inc. 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. Lambda Inc. expects the contracted sales for Q2 and Q3 of the year 2023 would be identical to the previous year. Item 4. Direct Labour The completion requires 0.5 hours of direct labour at a rate of $10 per hour. Item 5. Fixed Overhead The annual budget for fixed manufacturing overhead items follows: Factory salaries $186,200 26,000 28,800 8,000 30,000 10,000 Property taxes Insurance Maintenance Utilities Depreciation Total $289,000 Fixed overhead is applied to production using a predetermined overhead rate based on the estimated annual production volume. All fixed overheads are paid evenly each quarter. Item 6. Selling and Administrative Expenses Annual fixed selling and administration expenses are as follows: Sales salaries Administration salaries Travel $45,000 45,000 1,300 3,400 2,400 8,000 Insurance Utilities Depreciation Total $105,100 Fixed selling and administration expenses are paid evenly over the four quarters of the year. Item 7. Income Tax Payment Lambda Inc. usually makes income tax payments for the previous year's taxable income in the second quarter every year. The company is subject to a 20% tax rate. However, the company already paid all income taxes for the previous year (2021) at the end of Q1 2022. 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 $500,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, 2022 The company's simplified balance sheet as of March 31, 2022 is as follows: Cash $50,000 Accounts Payable (1) $74,300 Accounts Receivable Raw Material Inventory (2) Finished Goods Inventory Buildings and Equipment Accumulated Depreciation (200,000) 24,300 0 Capital Stock tained Earnings 600,000 200,000 1,000,000 Total Assets $874,300 Total Liabilities and Shareholder's Equity $874,300 These balance sheet figures must be taken as given. Negative balances are in the parentheses. (1) (2) Accounts payables are for direct materials; to be paid in cash in Q2 2022 Includes both TPU 95A and PCB Marking Rubric Description Possible Marks Follow Instructions: 1) using one sheet for the base case scenario and the other for the alternative scenario, 2) title and the author identification information in the header and the executive summary in the body of one page, and 3) Following other instructions on the submission process and deadline Base Budget: 1) no redundant manual inputs of data, 2) consistency between the spreadsheet models and the written report, 3) numerical accuracy fully supported by calculations within an excel spreadsheet, 4) identifiable quarterly and annual line items and other components of each schedule, and 5) complete and accurate raw data from the case material 14 Alternative Budget and the Estimation of Cost Behaviour 1) numerical accuracy, 2) use of Excel formulas to estimate the cost behaviour, 3) Discussion of problem, solution, and the logical support of the solution, and 4) writing clarity and style 5) Consistency between the Excel spreadsheet and the report 15 Total Mark 30 Required: 1. Base Budget Prepare a master budget for Lambda Inc. for the period covered by the annual contract (for the year and for each quarter). 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 cas! administrative expenses). j. Cash budget disbursements for selling and Prepare the following for the year. k. Cost of goods manufactured budget 1. Cost of goods sold budget m. Pro forma income statement (using absorption costing) 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 Incom 2. Alternative Budget In a recent meeting, the buyer indicated a possibility of a large special order of 100,000 units of Z350 for $35 per unit. The special order is to be delivered in Q4 2022. Lambda Inc., considering fulfilling some of the special orders, estimates that the unused capacity for 500 units will be available in Q4 2022. Prepare a master budget under a scenario of accepting the special order. 3. Report 1) Should Lambda Inc. accept the special order? Based on the estimated financial impact of the special order on the master budget, determine whether Lambda Inc. should accept the special order. Assume that the special order also follows a cash collection schedule identical to that of regular orders under the annual contract. 2) The buyer asks Lambda Inc. whether the special order price can go lower. The buyer wants to minimize the required capital for a massive sales and promotion campaign, which is essentially the material cost of Z350 to the buyer. The buyer proposes to share some of the excess profits from the event. The share for suppliers will be proportional to the cost-saving each supplier can provide to the buyer by participating in the special order. Lambda Inc., considering the proposal, wants to gather accounting information to help decision-making. Lambda Inc. wants to set a low price to increase the expected profit from the profit-sharing as long as the selling price is high enough to avoid a loss in the special order. What is the lowest possible price Lambda Inc. can set for the special order? Use the information available in the base and the alternative budgets. 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 and the title of the worksheet. 5. 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 Lambda Inc. is a contract manufacturer of consumer electronics. Lambda Inc. supplies a component (Z350) to its major buyer under an annual contract. As of the first quarter of 2022, the annual contract specifies the volume, selling price, and the timing of the delivery from April 2022 to March 2023. The scheduled delivery of Z350 in units for each quarter is the following: 2022 Q2 2022 Q3 2022 Q4 2023 Q1 4,500 3,500 8,500 2,500 The contracted selling price is $45.00 per unit. All sales are on account. Lambda Inc. expects to collect the cash payments in six months for each quarterly delivery of Z350. Item 2. Direct Materials Requirements and Purchasing Policy Each unit of Z350 requires two materials: a PCB module and 3 grams of TPU 95A. Lambda Inc. purchases the PCB for $12 per unit and TPU 95A for $0.2/gram. Lambda Inc. purchases all raw materials on account. Lambda Inc. 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 2022, Lambda expects to have 1,900 PCBS and 7,500 grams of TPU 95A 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. Lambda Inc. 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. Lambda Inc. expects the contracted sales for Q2 and Q3 of the year 2023 would be identical to the previous year. Item 4. Direct Labour The completion requires 0.5 hours of direct labour at a rate of $10 per hour. Item 5. Fixed Overhead The annual budget for fixed manufacturing overhead items follows: Factory salaries $186,200 26,000 28,800 8,000 30,000 10,000 Property taxes Insurance Maintenance Utilities Depreciation Total $289,000 Fixed overhead is applied to production using a predetermined overhead rate based on the estimated annual production volume. All fixed overheads are paid evenly each quarter. Item 6. Selling and Administrative Expenses Annual fixed selling and administration expenses are as follows: Sales salaries Administration salaries Travel $45,000 45,000 1,300 3,400 2,400 8,000 Insurance Utilities Depreciation Total $105,100 Fixed selling and administration expenses are paid evenly over the four quarters of the year. Item 7. Income Tax Payment Lambda Inc. usually makes income tax payments for the previous year's taxable income in the second quarter every year. The company is subject to a 20% tax rate. However, the company already paid all income taxes for the previous year (2021) at the end of Q1 2022. 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 $500,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, 2022 The company's simplified balance sheet as of March 31, 2022 is as follows: Cash $50,000 Accounts Payable (1) $74,300 Accounts Receivable Raw Material Inventory (2) Finished Goods Inventory Buildings and Equipment Accumulated Depreciation (200,000) 24,300 0 Capital Stock tained Earnings 600,000 200,000 1,000,000 Total Assets $874,300 Total Liabilities and Shareholder's Equity $874,300 These balance sheet figures must be taken as given. Negative balances are in the parentheses. (1) (2) Accounts payables are for direct materials; to be paid in cash in Q2 2022 Includes both TPU 95A and PCB Marking Rubric Description Possible Marks Follow Instructions: 1) using one sheet for the base case scenario and the other for the alternative scenario, 2) title and the author identification information in the header and the executive summary in the body of one page, and 3) Following other instructions on the submission process and deadline Base Budget: 1) no redundant manual inputs of data, 2) consistency between the spreadsheet models and the written report, 3) numerical accuracy fully supported by calculations within an excel spreadsheet, 4) identifiable quarterly and annual line items and other components of each schedule, and 5) complete and accurate raw data from the case material 14 Alternative Budget and the Estimation of Cost Behaviour 1) numerical accuracy, 2) use of Excel formulas to estimate the cost behaviour, 3) Discussion of problem, solution, and the logical support of the solution, and 4) writing clarity and style 5) Consistency between the Excel spreadsheet and the report 15 Total Mark 30
Expert Answer:
Answer rating: 100% (QA)
Answer Sales Budget Particular Q2 Q3 Q4 Q1 Sales Unit 4500 Units 3500 Units 8500 Units 2500 Units Selling Price 4500 4500 4500 4500 Sales Revenue 2025... View the full answer
Related Book For
Managerial Accounting
ISBN: 978-0176223311
1st Canadian Edition
Authors: Karen Wilken Braun, Wendy Tietz, Walter Harrison, Rhonda Pyp
Posted Date:
Students also viewed these accounting questions
-
You have developed the following pro forma income statement for your corporation. It represents the most recent years operations, which ended yesterday. Sales ..............$45,750,000 Variable costs...
-
Create a traditional format income statement using Absorption Costing using the information below and from problem #1: Cost information Variable costs per unit: Direct materials $9 Direct labor $10...
-
If a pro forma income statement has 5% for the net income line, what does this mean in terms of a company's total sales and per dollar sales?
-
According to Electronic Designs 2012 Engineering Salary Survey, the mean base salary of a software engineering manager is $126,417the highest mean among all types of engineers. In contrast, a...
-
Capital punishment for murderers exists in most U.S. states. However, a few states ban this form of punishment. Politicians often need to know which members of the public support and which oppose....
-
In Problems 514, there are three figures. In each problem, select two that are topologically equivalent. If none of them are equivalent, then so state. O circle square 7.0 maze
-
The results of longitudinal, transverse, and \(45^{\circ}\) off-axis tensile tests on samples from an orthotropic lamina are shown in Figure 10.35. Based on these results, find numerical values for...
-
Benick Industries purchased a new lathe on January 1, 2011, for $300,000. Benick estimates that the lathe will have a useful life of four years and that the company will be able to sell it at the end...
-
What are the major change activities that need to take place to transition employees? What actions do you need to take based on your stakeholder analysisand change impact assessment?Whatarethe...
-
Cinder and PQ are associated [ITA 256(1)(a)] and connected [ITA 186(4)] for tax purposes. Cinder Inc. is a Canadian-controlled private corporation based in your province. The company operates a...
-
Solve the high and low pass filter equations please alongside the bandpass filter. Function Generator 10.0 kHz 5.00 V 0.00 V 00 N ..... ..... ..... ..... Oscilloscope C1, C2 PE ..... opAmp -|- -|-...
-
The pooling of resources, resulting accumulation of wealth, assets such as stocks, a house, investments in human capital for children, within the context of marriage, was called by Nobel Laureate...
-
Explain what accrual basis accounting is and what cash basis accounting is. Understand revenue realization principle.
-
Management Audit Management audits can drive organizational change by identifying opportunities for improvement. They foster accountability among managers by holding them responsible for their...
-
How do you allocate joint costs in accounting? In developing forecasts of expenses the analyst must take into consideration that expenses can be broken down into components. What is the difference...
-
What cultural variable can affect the decistion - making process?
-
Star Schemas were initially developed by: Group of answer choices E . . F . ( . ( Ted ) ) Codd Ralph Kimball William H . ( . ( Bill ) ) Inmon Bill Gates IBM
-
The MIT Sloan School of Management is one of the leading business schools in the U.S. The following table contains the tuition data for the masters program in the Sloan School of Management. a. Use...
-
Refer to the Cherry Valley Data Set and assume the expansion has a residual value of $950,000 at the end of nine years. Consider how Cherry Valley, a popular ski resort, could use capital budgeting...
-
Top managers of Manion Industries predicted the following years sales of 145,000 units of its product at a unit price of $8. Actual sales for the year were 140,000 units at $9.50 each. Variable...
-
Classify each of the following costs as fixed or variable: a. Credit card transaction fee paid by retailer$0.20 per transaction plus 2% of the sales amount b. Yearly salaries paid to marketing staff...
-
What is the pressure drop associated with water at \(27^{\circ} \mathrm{C}\) flowing with a mean velocity of \(0.1 \mathrm{~m} / \mathrm{s}\) through an \(800-\mathrm{m}-\) long cast iron pipe of...
-
Fully developed conditions are known to exist for water flowing through a \(50-\mathrm{mm}\)-diameter tube at \(0.02 \mathrm{~kg} / \mathrm{s}\) and \(27^{\circ} \mathrm{C}\). What is the maximum...
-
Water at \(35^{\circ} \mathrm{C}\) is pumped through a horizontal, \(200-\mathrm{m}\)-long, \(30-\mathrm{mm}\)-diameter tube at \(0.25 \mathrm{~kg} / \mathrm{s}\). Over time, a 2-mm-thick layer of...
Study smarter with the SolutionInn App