Please I need help with this assignment

Design and Use of a Dynamic Accounting Model in Decision Making at Tata Company: An Accounting Capstone Case Highlights Undergraduate accounting capstone course teaching case. Integrates financial accounting with managerial accounting concepts across one entity. GAAP based financial statements are prepared. Cost behavior concepts are applied in decision making. Various analyses are performed - breakeven, profitability, and return on invested capital. A dynamic data collection and analysis model is developed using Microsoft Excel. Introduction This case provides you an opportunity to demonstrate mastery of the subject matter and a deeper understanding of the integrative relationship of accounting with the broader business discipline. In this case, you are tasked with creating an Excel based dynamic model that will capture data points for use in calculating production and administrative costs for the outputs of two manufacturing plants and then use the summary data to prepare financial statements. You will also use evaluative techniques for measuring efficiency and profitability, all while strategically deciding which of the two plants should receive a proposed infusion of capital . Upon completion of your task, you will have gained valuable experience in using Microsoft Excel to create formulas, cell references, and employ multiple worksheets. You will also have gained an enhanced perspective on the integrative and interactive role of costing systems in providing data for financial statements as well as for use in making managerial decisions. Keywords Data Model; Financial Statements: Costs Behavior: Breakeven Analysis: Decision Making: Retum on Invested Capital: DuPont Method 1. CASE ASSIGNMENT: TTaR Company Assume that you have been ranked in the top three candidates for hire as the new assistant to the CFO of Tatar Corporation headquartered in Miami, USA. As part of your interview process, you have been asked to provide a solution to the case described below. It goes without saying that this means you must make the best impression with your solution. TaTar, Corporation is a small-scale manufacturer of metal shipping containers with two manufacturing facilities - one in Miami, Florida [headquarter) and one in Los Cabos, Mexico. TATER. Corporation is a public corporation in the USA whose equity is composed of 500.000 shares of Common Stock of $10 par value. There are no treasury shares and no preferred stock. TaTaR Corporation is a relatively new business having completed only one accounting period [2021]. After much research during its first year of operation, TaTaR Corporation desires to penetrate the shipping containers industry more aggressively by increasing its manufacturing capacity. Tatar Company will manufacture an additional line of new styled containers with the goal that its corporate wide revenues will double. The company management is therefore, considering making an additional large capital investment over the next two years in order to accommodate this new capacity. The newly added infrastructure is expected to be fully operational beginning in Tataris fourth year of operation. The company has asked you to analyze its current operations and make a recommendation as to which of the two locations it should direct its proposed new investment. The requirements listed at the end of this case will help to guide your analysis. 1.1 Hereafter are the current details of the costs required to manufacture one metal container: 1. Steel: 8-10 tons of steel (depending on the utilization efficiency). Steel Cost: In Miami, one ton of steel costs $80, but the price may vary and so the model needs to be dynamic. If the steel is purchased in Los Cabos, the cost is 20% cheaper than in Miami but there is a 30% import tariff imposed by USA if materials'goods are imported from Los Cabos. If this option to purchase the steel in Los Cabos and ship to Miami is taken the shipping cost will be $1,000 per shipment (fixed costs) with a maximum of 50 tons per shipment. 2. Labor: In Miami 22 labor hours of 2 employees (total of 40 hours). In Los Cabos 16 labor hours of 4 employees (total 60 hours). One labor hour for welders and machinists in Miami usually costs $18 per hour (minimum wage rate is $10.00). In Los Cabos it usually costs $8 per hour (minimum wage rate is $5.00). The company policy is to avoid paying overtime and employee benefits for part-time labor. 3. Indirect Costs: For each $io labor cost, $5 additional will be required for the desired retum of equity. 4. Fixed Costs: $45,000 per year in Miami and $25,000 in Los Cabos. 5. Additional overall costs per below: a. Admin costs: Total admin costs for both locations (combined) is $800,000 per year. b. Finance costs: Finance cost depends on the company's loans and the interest rate. c. Depreciation is $60,000 per year. 1.2 Additional Details: 1. Data for 2021 operation is as described. The first forecast year is 2022. 2. Because of competition in the workplace, the plant in Miami had to pay $20 an hour while in Los Cabos, the actual labor rate paid was $7.50. 3. At the ending of 2021 (right after incorporation and preparedness for launch of operations) Tatar Company took a zero-interest bearing loan of $200,000. The loan will be repaid in 4 equal installments of $50,000 at the end of each year, with the first installment due in 2021. TaTa is incremental borrowing rate is 10%. 4. The forecasted manufacture for 2022 was 2,000 containers in Miami and 1,500 in Los Cabos. Sales for 2022 are 1,600 containers in the USA and 1,100 in Mexico. 5. The annual sales growth rate (without considering the planned increase in capacity for the next three years is 5% in Miami and 8% in Mexico. 6. The corporate tax rate in Miami is 35%, while it is 25% in Mexico. 7. The normal annual capital investments for existing operations as projected for the next three years is $250,000 at each location. The depreciation of those investments is included in the depreciation as in item 5c above. 8. The sales price per container: 54,500 - $5,500 in Miami; $3,500 - 54,500 in Mexico. Use the average price for analysis. 9. Average credit days to customers is 60 days. Average credit days to vendors is 30 days. 10. In 2021, Tatar Corporation declared and paid $0.50 per share dividends to its shareholders. The company board adopted a policy to pay dividends every fifth year beginning with 2021. 11. Management considers direct labor hours as an adequate cost allocation base where needed. 12. TaTas long-term productive assets are as follows: $3 million in Miami and $1,500,000 in Los Cabos. 13 Case Requirements As part of your application for the Assistant CFO position, you have been offered the option of completing this case requirement at various levels depending on how impressive you want your attempt to be. The practical level requirement meets the minimal interview standards while the progressive level and the proficient levels offer a greater opportunity to showcase higher level abilities. Select your level of presentation wisely because the base for maximum possible points is based on your level chosen. The maximum total points you can earn on your overall assignment are indicated for each level Keep in mind your choice of other than the proficient level may offer an opportunity for one of your other two competing potential hires to be viewed as more competitive. Make sure to indicate clearly the level you are presenting in your submitted solution. 1.4 Requirement A: Practical Level [33% maximum points) 1. Prepare a model that will be used to enter the parameters needed to automatically arrive at the required amounts in the preformatted financial statements. The model must be dynamic enough to accommodate any variation in the amounts entered in the relevant parameters. And then 2. Using the case data provided, prepare a complete and well formatted income statement and a statement of cash flow for the year 2021 using excel (see the "please note" below). 1.5 Requirement B: Progressive Level [66% maximum points] 3. Complete requirements in A above and then prepare a complete and well formatted income statement and a statement of cash flow for the years 2023 2024 in excel (see the "please note" below). No data should be manually entered in the financial statements - prepare links connecting the model to the financial statements. 16 Requirement C: Proficient Level [100% maximum points] 4. Complete requirements A and B above and then prepare a summary of the conclusions you'd like to share with the CEO of Tatar Corporation regarding performance at the two locations and the company as a whole. You may want to consider the following among other considerations of your choice: a. Profitability b. Breakeven Analysis c. Components of the DuPont Method: ROIC [return on invested capital] which is a product of net operating margin (net income sales) and invested capital turnover (sales average invested capital]. Also considers the Financial Leverage as a quotient of ROIC [total invested capital (assets) Avg shareholders' equity). DuPont's full model is expressed as: Net Income Net Income Sales Total Assets Shareholders' Equity Sales Total Assets Shareholders Equity d. Return on equity (ROE) (Net Income Average Equity). Consider what impact or contribution each component of the DuPont Method in () above has on ROE. 5. Based on the analysis performed so far along with any other insights you gathered from the operations, which of the two locations should the company management select to receive the new substantial capital investment? 6. Prepare a report that summarizes your findings and your recommendations after conducting your analysis. You may target the CFO as the recipient of your report. Please note as you respond to the requirements: Include with your solution, a document (titled Assumptions") with the explanations of any/all assumptions you have taken (no longer than one page). You may present it as a bullet list. The company strictly follows FASB's recommended formats in preparing its financial statements. Important: This assignment requires you to demonstrate your understanding of technical accounting elements, data-based decision-making, and communication skills. Due Date: The completed assignment should be submitted no later than (date) at (time). The assignment should be submitted by [specify how it is to be submitted]. Name your file as follows: YourlastiName space FirstName space TaTaR.doc (or docx) For example: DoeJohn-TaTaR.docx Grading: This case is worth _points (see syllabus for point allocation). Note: This is an INDIVIDUAL case assignment and not a group case. Your work will be evaluated for originality. Design and Use of a Dynamic Accounting Model in Decision Making at Tata Company: An Accounting Capstone Case Highlights Undergraduate accounting capstone course teaching case. Integrates financial accounting with managerial accounting concepts across one entity. GAAP based financial statements are prepared. Cost behavior concepts are applied in decision making. Various analyses are performed - breakeven, profitability, and return on invested capital. A dynamic data collection and analysis model is developed using Microsoft Excel. Introduction This case provides you an opportunity to demonstrate mastery of the subject matter and a deeper understanding of the integrative relationship of accounting with the broader business discipline. In this case, you are tasked with creating an Excel based dynamic model that will capture data points for use in calculating production and administrative costs for the outputs of two manufacturing plants and then use the summary data to prepare financial statements. You will also use evaluative techniques for measuring efficiency and profitability, all while strategically deciding which of the two plants should receive a proposed infusion of capital . Upon completion of your task, you will have gained valuable experience in using Microsoft Excel to create formulas, cell references, and employ multiple worksheets. You will also have gained an enhanced perspective on the integrative and interactive role of costing systems in providing data for financial statements as well as for use in making managerial decisions. Keywords Data Model; Financial Statements: Costs Behavior: Breakeven Analysis: Decision Making: Retum on Invested Capital: DuPont Method 1. CASE ASSIGNMENT: TTaR Company Assume that you have been ranked in the top three candidates for hire as the new assistant to the CFO of Tatar Corporation headquartered in Miami, USA. As part of your interview process, you have been asked to provide a solution to the case described below. It goes without saying that this means you must make the best impression with your solution. TaTar, Corporation is a small-scale manufacturer of metal shipping containers with two manufacturing facilities - one in Miami, Florida [headquarter) and one in Los Cabos, Mexico. TATER. Corporation is a public corporation in the USA whose equity is composed of 500.000 shares of Common Stock of $10 par value. There are no treasury shares and no preferred stock. TaTaR Corporation is a relatively new business having completed only one accounting period [2021]. After much research during its first year of operation, TaTaR Corporation desires to penetrate the shipping containers industry more aggressively by increasing its manufacturing capacity. Tatar Company will manufacture an additional line of new styled containers with the goal that its corporate wide revenues will double. The company management is therefore, considering making an additional large capital investment over the next two years in order to accommodate this new capacity. The newly added infrastructure is expected to be fully operational beginning in Tataris fourth year of operation. The company has asked you to analyze its current operations and make a recommendation as to which of the two locations it should direct its proposed new investment. The requirements listed at the end of this case will help to guide your analysis. 1.1 Hereafter are the current details of the costs required to manufacture one metal container: 1. Steel: 8-10 tons of steel (depending on the utilization efficiency). Steel Cost: In Miami, one ton of steel costs $80, but the price may vary and so the model needs to be dynamic. If the steel is purchased in Los Cabos, the cost is 20% cheaper than in Miami but there is a 30% import tariff imposed by USA if materials'goods are imported from Los Cabos. If this option to purchase the steel in Los Cabos and ship to Miami is taken the shipping cost will be $1,000 per shipment (fixed costs) with a maximum of 50 tons per shipment. 2. Labor: In Miami 22 labor hours of 2 employees (total of 40 hours). In Los Cabos 16 labor hours of 4 employees (total 60 hours). One labor hour for welders and machinists in Miami usually costs $18 per hour (minimum wage rate is $10.00). In Los Cabos it usually costs $8 per hour (minimum wage rate is $5.00). The company policy is to avoid paying overtime and employee benefits for part-time labor. 3. Indirect Costs: For each $io labor cost, $5 additional will be required for the desired retum of equity. 4. Fixed Costs: $45,000 per year in Miami and $25,000 in Los Cabos. 5. Additional overall costs per below: a. Admin costs: Total admin costs for both locations (combined) is $800,000 per year. b. Finance costs: Finance cost depends on the company's loans and the interest rate. c. Depreciation is $60,000 per year. 1.2 Additional Details: 1. Data for 2021 operation is as described. The first forecast year is 2022. 2. Because of competition in the workplace, the plant in Miami had to pay $20 an hour while in Los Cabos, the actual labor rate paid was $7.50. 3. At the ending of 2021 (right after incorporation and preparedness for launch of operations) Tatar Company took a zero-interest bearing loan of $200,000. The loan will be repaid in 4 equal installments of $50,000 at the end of each year, with the first installment due in 2021. TaTa is incremental borrowing rate is 10%. 4. The forecasted manufacture for 2022 was 2,000 containers in Miami and 1,500 in Los Cabos. Sales for 2022 are 1,600 containers in the USA and 1,100 in Mexico. 5. The annual sales growth rate (without considering the planned increase in capacity for the next three years is 5% in Miami and 8% in Mexico. 6. The corporate tax rate in Miami is 35%, while it is 25% in Mexico. 7. The normal annual capital investments for existing operations as projected for the next three years is $250,000 at each location. The depreciation of those investments is included in the depreciation as in item 5c above. 8. The sales price per container: 54,500 - $5,500 in Miami; $3,500 - 54,500 in Mexico. Use the average price for analysis. 9. Average credit days to customers is 60 days. Average credit days to vendors is 30 days. 10. In 2021, Tatar Corporation declared and paid $0.50 per share dividends to its shareholders. The company board adopted a policy to pay dividends every fifth year beginning with 2021. 11. Management considers direct labor hours as an adequate cost allocation base where needed. 12. TaTas long-term productive assets are as follows: $3 million in Miami and $1,500,000 in Los Cabos. 13 Case Requirements As part of your application for the Assistant CFO position, you have been offered the option of completing this case requirement at various levels depending on how impressive you want your attempt to be. The practical level requirement meets the minimal interview standards while the progressive level and the proficient levels offer a greater opportunity to showcase higher level abilities. Select your level of presentation wisely because the base for maximum possible points is based on your level chosen. The maximum total points you can earn on your overall assignment are indicated for each level Keep in mind your choice of other than the proficient level may offer an opportunity for one of your other two competing potential hires to be viewed as more competitive. Make sure to indicate clearly the level you are presenting in your submitted solution. 1.4 Requirement A: Practical Level [33% maximum points) 1. Prepare a model that will be used to enter the parameters needed to automatically arrive at the required amounts in the preformatted financial statements. The model must be dynamic enough to accommodate any variation in the amounts entered in the relevant parameters. And then 2. Using the case data provided, prepare a complete and well formatted income statement and a statement of cash flow for the year 2021 using excel (see the "please note" below). 1.5 Requirement B: Progressive Level [66% maximum points] 3. Complete requirements in A above and then prepare a complete and well formatted income statement and a statement of cash flow for the years 2023 2024 in excel (see the "please note" below). No data should be manually entered in the financial statements - prepare links connecting the model to the financial statements. 16 Requirement C: Proficient Level [100% maximum points] 4. Complete requirements A and B above and then prepare a summary of the conclusions you'd like to share with the CEO of Tatar Corporation regarding performance at the two locations and the company as a whole. You may want to consider the following among other considerations of your choice: a. Profitability b. Breakeven Analysis c. Components of the DuPont Method: ROIC [return on invested capital] which is a product of net operating margin (net income sales) and invested capital turnover (sales average invested capital]. Also considers the Financial Leverage as a quotient of ROIC [total invested capital (assets) Avg shareholders' equity). DuPont's full model is expressed as: Net Income Net Income Sales Total Assets Shareholders' Equity Sales Total Assets Shareholders Equity d. Return on equity (ROE) (Net Income Average Equity). Consider what impact or contribution each component of the DuPont Method in () above has on ROE. 5. Based on the analysis performed so far along with any other insights you gathered from the operations, which of the two locations should the company management select to receive the new substantial capital investment? 6. Prepare a report that summarizes your findings and your recommendations after conducting your analysis. You may target the CFO as the recipient of your report. Please note as you respond to the requirements: Include with your solution, a document (titled Assumptions") with the explanations of any/all assumptions you have taken (no longer than one page). You may present it as a bullet list. The company strictly follows FASB's recommended formats in preparing its financial statements. Important: This assignment requires you to demonstrate your understanding of technical accounting elements, data-based decision-making, and communication skills. Due Date: The completed assignment should be submitted no later than (date) at (time). The assignment should be submitted by [specify how it is to be submitted]. Name your file as follows: YourlastiName space FirstName space TaTaR.doc (or docx) For example: DoeJohn-TaTaR.docx Grading: This case is worth _points (see syllabus for point allocation). Note: This is an INDIVIDUAL case assignment and not a group case. Your work will be evaluated for originality