Prepare a complete and well-formatted income statement and a statement of cash flow for the years 2020 and 2021– 2022 in excel
Page 1 of 2 Case Study – TaTaR Corporation TaTaR Corporation is a manufacturer of metal shipping containers with two manufacturing facilities – one in Miami, Florida and one in Los Cabos, Mexico. TaTaR Corporation is a public corporation in the USA and whose equity is composed of 100,000 shares of Common Stock of a $1 par value. There are no treasury shares and no preferred stock. Assume that you have been ranked in the top three candidates for hire as the new CFO of TaTaR Corporation headquartered in Miami. You have been asked to complete the task described below as part of your interview. This means you must make the best impression with your solution. Hereafter are the details of the costs required to manufacture one 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 2 labor hours of 2 employees (total of 20 hours). In Los Cabos 5 labor hours of 2 employees (total 10 hours). One labor hour in Miami usually costs $13. In Los Cabos it usually costs $6 per hour. 3. Indirect Costs: For each $10 labor cost, $3 additional will be required for the desired return of equity. 4. Fixed Costs: $7,500 per year in each of the two locations. 5. Additional costs per below: a. Admin costs: Total admin costs for both locations (combined) is $800K per year. b. Finance costs: Finance cost depends on the company’s loans and the interest rate. c. Depreciation is $60K per year. Additional Details: 1. First forecast year is 2020. 2. Because of competition in the workplace, the plant in Miami had to pay $15 an hour while in Los Cabos, the actual labor rate paid was $5.50. 3. At the beginning of 2020 (the date of incorporation) 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 2020. TaTaR’s incremental borrowing rate is 10%. 4. The forecasted manufacture for 2020 is 2,500 containers in Miami and 1,000 in Los Cabos. Expected sales for 2020 is 2,000 containers in the USA and 700 in Mexico. 5. The annual sales growth rate for the next five years is 4% in Miami and 5% in Mexico. Page 2 of 2 6. The corporate tax rate in Miami is 35%, while it is 25% in Mexico. 7. The annual capital investments projected during the next five years is $300K. The depreciation of those investments is included in the depreciation as in item 5c above. 8. The sales price per container: $2,500 - $2,800 in Miami; $1,800 - $2,000 in Mexico. 9. Average credits days to customers is 60 days. Average credit days to vendors is 30 days. 10. In 2020, 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 2020. Please note as you respond to the requirements: Be sure to prepare a model that will be used to enter the parameters needed to arrive at the required financial statements. The model must be dynamic enough to allow for changes in the relevant parameters. Add a document with the explanations of all assumptions you have used (no longer than one page) The company strictly follows FASB’s recommended formats in preparing its financial statements. Requirement: You have the option of completing this case at various levels depending on how impressive you want your attempt to be as you face consideration for the CFO position. The minimal requirement meets the interview standards while the medium level and the advanced levels show increased proficiencies. Select your level wisely. Make sure to indicate clearly the level you are presenting in your submitted work. Minimal Requirement: 1. Prepare a complete and well formatted income statement and a statement of cash flow for the year 2020 in excel (see the “please note” above). Medium Level Requirement: 2. Prepare a complete and well formatted income statement and a statement of cash flow for the years 2020 and 2021– 2022 in excel (see the “please note” above). Higher Level Requirement: 3. Prepare a complete and well formatted income statement and a statement of cash flow for the years 2020 and 2021– 2022 in excel (see the “please note” above). 4. Prepare a memo of the conclusions you’d like to share with the CEO of TaTaR Corporation. You may want to consider the following among your other considerations: a. Profitability b. Which of the two locations should receive greater capital investment and why? c. Breakeven Analysis d. Variance analysis as a source for improvement 5. What further advice can you offer management of TaTaR Company? * * * * * * * * * * * *