60.1K

Verified Solution

Link Copied!

Please show step by step on excel

Project EBChip Ltd. (EBC) is a small-scale contract producer and seller of microchips for e-bikes. EBC's customers are bike manufacturers. EBC's master budget will detail each quarter's activity and the activity for the year in total. The company will base its 2023 budget on the following information: 1. Expected sales, in units, for the four quarters of 2023 and the first two quarters of 2024 are as follows: The selling price for 2023 has been set at $51.00 per unit. EBC's fiscal year ends on December 31. All sales are on account. 65% of sales on account are collected in the quarter of sale; 35% of sales on account are collected in the following quarter. Assume that all the balance in accounts receivable (as of December 31, 2022) will be collected in the first quarter of 2023. Assume no bad debts are incurred. 2. Each component requires the following direct inputs: - 6 milligrams (mg) of direct material available at a price of $1.30 per mg. - 0.03 hours of direct labour at a rate of $45.00 per hour. EBC has a policy of maintaining direct material ending inventory equal to 30% of direct materials needed for the next quarter's production requirements. All raw materials are purchased on account. 60% of a quarter's purchases are paid for in the quarter of purchase; the remaining in the following quarter. EBC has a policy of keeping ending finished goods inventory equal to 20% of next quarter's forecasted sales. There is no beginning or ending work-in-process inventory. Direct labourers are paid at the end of each month. 3. Total budgeted variable overhead costs for the 2023 year (at a level of sales estimated in Item 1 above) follow: Variable overhead is applied to components using a predetermined overhead rate based on annual direct labour hours. All variable overhead items are paid for in the quarter incurred. 4. The annual budget for fixed manufacturing overhead items follows: All fixed overheads are paid evenly each quarter except for property taxes which are paid for in the second quarter of the year. Fixed overhead is applied to production using a predetermined overhead rate based on the estimated annual number of units produced. 5. Variable selling and administration expenses include commissions and other administrative expenses. Commissions are budgeted at 6% of sales dollars for the quarter. 60% of these commissions are paid in the quarter they incurred, while 40% are paid in the following quarter. Other variable administration costs are $3.00 per unit. These costs are paid for in the quarter they incurred. Annual fixed selling and administration expenses are as follows: Fixed selling and administration expenses are paid evenly over the four quarters of the year. 6. EBC makes quarterly income tax installments based on the projected taxable income for the year. The company is subject to a 30% tax rate. For the master budget, EBC assumes tax expenses incurring for the year 2023 are paid in cash evenly over the four quarters of the year 2023. 7. EBC plans the following financing and investing activities for the coming year: - The company is planning to buy land, costing $90,000, in the last quarter of 2023 . This land will be held until such time that the company is ready to develop it for future business purposes. The company will pay cash for the land and will finance any resulting cash shortfall by drawing on its operating line of credit. - The company has an operating line of credit established with its bank. This allows the company to borrow to cover any cash shortfalls. All borrowing is assumed to occur at the beginning of the quarter in which the funds are required and all repayment is assumed to be made at the end of the quarter in which funds are available for repayment. Simple interest at the rate of 8% per annum is paid on a quarterly basis on all outstanding short-term loans. - The company currently has $300,000 in an outstanding long-term loan with an annual interest rate of 6% and makes quarterly interest only payments at the end of each quarter. The loan is due in 2035 . - The company outsources some of the manufacturing for $680,000. The company will pay the outsourcing fee in cash at the end of the first quarter of year 2023. 8. The company's simplified balance sheet as of December 31, 2022 is as follows: These balance sheet figures must be taken as given. That is, these specific December 31, 2022 balance sheet amounts override any expectations based on 2022 sales and purchase amounts. Negative balances are shown in parentheses. (1) Only used for direct materials quired: 1. Prepare a master budget for EBC for each quarter of 2023 and for the year in total. The following component budgets must be included: a. Beginning balance sheet (classified as in Item 8, Project) b. Sales budget c. Schedule of receipts 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 disbursements for variable and fixed overheads, in addition to applied variable and fixed overhead expenses). i. Selling and administrative budget (be sure to show disbursements for selling and administrative expenses). j. Cash budget Prepare the following for the year, 2023, in total. k. Cost of goods manufactured budget 1. Cost of goods sold budget m. Budgeted income statement (using absorption costing) n. Budgeted classified balance sheet Cash budget and budgeted income statement are completed at the same time when you build a formula to account for tax expenses, and a set of formulas to account for cash outflow in the cash budget. The budgeted classified balance sheet is completed last. 2. The current price of $51.00 per unit is set during the last year's trial stage production. EBC plans to scale up the production to meet the growing demand. The resulting economies of scale allow the company to consider strategic pricing and compensation. Re-evaluate the current price, using the master budget that you built for part 1 . Strategically, updated pricing should satisfy the following criteria: - The price should be less than $51.00 per unit to beat other small-scale producers in price competition. - The company must achieve profit margins (= Operating income/Sales) between 5% and 10% The company also needs to increase employee benefits and compensation by $150,000 to retain skilled manufacturing workers and production engineers. Provide a single or range of values for the unit price. Provide a written report to provide a recommendation for strategic pricing and compensation. Support your recommendation by drawing upon the Excel budget model that you have built. Consider the applicable business, industry, and regulatory environment in your analysis. Provide quantitative and qualitative support for your strategic plan, explaining how the company may gain competitive advantage given the information provided. Ensure to consider profit maximization/growth, employee compensation, and risk. Instructions for preparing the 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 of the given information is entered in at the top of the workbook and the entire budget for each alternative is to be in a single workbook. 2. When you have finalized the master budget, complete a sensitivity (what-if) analyses for part 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, the results of your changes will be immediately calculated. Label the worksheet tabs and the title of the worksheet so that it is clear what you are calculating. (Use a header to label the worksheet so that the content of the worksheet is clear.) 3. Part of your mark will depend on how well you link the parts of your spreadsheets. 4. You must upload an electronic copy of your budget file (.xlsx) and report (.docx or .pdf) on Canvas Report Instructions: Your report (i.e., Executive summary, and the body of the report) should be no longer than three pages ( 1 inch margins, 12 font size, double spacing, New Times Roman font). Any appendices are not included in this three-page limit. The report will be evaluated on content, writing, and presentation. Your report will include these sections (in this order): Title page Body of the report (introduction, analysis, and recommendation on strategic pricing and support) External References if any (e.g., journal articles, web pages, books) Appendices if any The introduction should succinctly identify the issues facing the company. The report body should provide analysis to address these issues. As a business report, format and structure should Project EBChip Ltd. (EBC) is a small-scale contract producer and seller of microchips for e-bikes. EBC's customers are bike manufacturers. EBC's master budget will detail each quarter's activity and the activity for the year in total. The company will base its 2023 budget on the following information: 1. Expected sales, in units, for the four quarters of 2023 and the first two quarters of 2024 are as follows: The selling price for 2023 has been set at $51.00 per unit. EBC's fiscal year ends on December 31. All sales are on account. 65% of sales on account are collected in the quarter of sale; 35% of sales on account are collected in the following quarter. Assume that all the balance in accounts receivable (as of December 31, 2022) will be collected in the first quarter of 2023. Assume no bad debts are incurred. 2. Each component requires the following direct inputs: - 6 milligrams (mg) of direct material available at a price of $1.30 per mg. - 0.03 hours of direct labour at a rate of $45.00 per hour. EBC has a policy of maintaining direct material ending inventory equal to 30% of direct materials needed for the next quarter's production requirements. All raw materials are purchased on account. 60% of a quarter's purchases are paid for in the quarter of purchase; the remaining in the following quarter. EBC has a policy of keeping ending finished goods inventory equal to 20% of next quarter's forecasted sales. There is no beginning or ending work-in-process inventory. Direct labourers are paid at the end of each month. 3. Total budgeted variable overhead costs for the 2023 year (at a level of sales estimated in Item 1 above) follow: Variable overhead is applied to components using a predetermined overhead rate based on annual direct labour hours. All variable overhead items are paid for in the quarter incurred. 4. The annual budget for fixed manufacturing overhead items follows: All fixed overheads are paid evenly each quarter except for property taxes which are paid for in the second quarter of the year. Fixed overhead is applied to production using a predetermined overhead rate based on the estimated annual number of units produced. 5. Variable selling and administration expenses include commissions and other administrative expenses. Commissions are budgeted at 6% of sales dollars for the quarter. 60% of these commissions are paid in the quarter they incurred, while 40% are paid in the following quarter. Other variable administration costs are $3.00 per unit. These costs are paid for in the quarter they incurred. Annual fixed selling and administration expenses are as follows: Fixed selling and administration expenses are paid evenly over the four quarters of the year. 6. EBC makes quarterly income tax installments based on the projected taxable income for the year. The company is subject to a 30% tax rate. For the master budget, EBC assumes tax expenses incurring for the year 2023 are paid in cash evenly over the four quarters of the year 2023. 7. EBC plans the following financing and investing activities for the coming year: - The company is planning to buy land, costing $90,000, in the last quarter of 2023 . This land will be held until such time that the company is ready to develop it for future business purposes. The company will pay cash for the land and will finance any resulting cash shortfall by drawing on its operating line of credit. - The company has an operating line of credit established with its bank. This allows the company to borrow to cover any cash shortfalls. All borrowing is assumed to occur at the beginning of the quarter in which the funds are required and all repayment is assumed to be made at the end of the quarter in which funds are available for repayment. Simple interest at the rate of 8% per annum is paid on a quarterly basis on all outstanding short-term loans. - The company currently has $300,000 in an outstanding long-term loan with an annual interest rate of 6% and makes quarterly interest only payments at the end of each quarter. The loan is due in 2035 . - The company outsources some of the manufacturing for $680,000. The company will pay the outsourcing fee in cash at the end of the first quarter of year 2023. 8. The company's simplified balance sheet as of December 31, 2022 is as follows: These balance sheet figures must be taken as given. That is, these specific December 31, 2022 balance sheet amounts override any expectations based on 2022 sales and purchase amounts. Negative balances are shown in parentheses. (1) Only used for direct materials quired: 1. Prepare a master budget for EBC for each quarter of 2023 and for the year in total. The following component budgets must be included: a. Beginning balance sheet (classified as in Item 8, Project) b. Sales budget c. Schedule of receipts 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 disbursements for variable and fixed overheads, in addition to applied variable and fixed overhead expenses). i. Selling and administrative budget (be sure to show disbursements for selling and administrative expenses). j. Cash budget Prepare the following for the year, 2023, in total. k. Cost of goods manufactured budget 1. Cost of goods sold budget m. Budgeted income statement (using absorption costing) n. Budgeted classified balance sheet Cash budget and budgeted income statement are completed at the same time when you build a formula to account for tax expenses, and a set of formulas to account for cash outflow in the cash budget. The budgeted classified balance sheet is completed last. 2. The current price of $51.00 per unit is set during the last year's trial stage production. EBC plans to scale up the production to meet the growing demand. The resulting economies of scale allow the company to consider strategic pricing and compensation. Re-evaluate the current price, using the master budget that you built for part 1 . Strategically, updated pricing should satisfy the following criteria: - The price should be less than $51.00 per unit to beat other small-scale producers in price competition. - The company must achieve profit margins (= Operating income/Sales) between 5% and 10% The company also needs to increase employee benefits and compensation by $150,000 to retain skilled manufacturing workers and production engineers. Provide a single or range of values for the unit price. Provide a written report to provide a recommendation for strategic pricing and compensation. Support your recommendation by drawing upon the Excel budget model that you have built. Consider the applicable business, industry, and regulatory environment in your analysis. Provide quantitative and qualitative support for your strategic plan, explaining how the company may gain competitive advantage given the information provided. Ensure to consider profit maximization/growth, employee compensation, and risk. Instructions for preparing the 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 of the given information is entered in at the top of the workbook and the entire budget for each alternative is to be in a single workbook. 2. When you have finalized the master budget, complete a sensitivity (what-if) analyses for part 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, the results of your changes will be immediately calculated. Label the worksheet tabs and the title of the worksheet so that it is clear what you are calculating. (Use a header to label the worksheet so that the content of the worksheet is clear.) 3. Part of your mark will depend on how well you link the parts of your spreadsheets. 4. You must upload an electronic copy of your budget file (.xlsx) and report (.docx or .pdf) on Canvas Report Instructions: Your report (i.e., Executive summary, and the body of the report) should be no longer than three pages ( 1 inch margins, 12 font size, double spacing, New Times Roman font). Any appendices are not included in this three-page limit. The report will be evaluated on content, writing, and presentation. Your report will include these sections (in this order): Title page Body of the report (introduction, analysis, and recommendation on strategic pricing and support) External References if any (e.g., journal articles, web pages, books) Appendices if any The introduction should succinctly identify the issues facing the company. The report body should provide analysis to address these issues. As a business report, format and structure should

Answer & Explanation Solved by verified expert

See Answer