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 the 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, two labor hours of 2 employees (a total of 20 hours). In Los Cabos, five 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. The 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 are 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.

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. The average credit days to customers is 60 days. The 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.

Requirement: Prepare a complete and well-formatted income statement and a statement of cash flow for the years 2020 and 2021– 2022 in excel