Collaboration Exercise - Spreadsheet Example Page 1 (A) COLLABORATION EXERCISE Worked Example - Joint Venture Co. EM - Supplying to Partners only JVC SALES VOLUMES YEAR 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 TOTAL Sales Volume - Amazonia only 8 8 8 80 Export Volume-from Amazonia 6 6 6 6 6 6 6 60 FINISHED PRODUCT PRICING Group: PRODUCT COST STRUCTURE: ASSEMBLY IN AMAZONIA Ex Works price - JVC to CNI $225 CNI Markup ( % EW ) 22.22%% OVERALL PROFITABILITY Imported Material 694.71 (CIF + Duty) CNI Price to Government 2 $275 (10yr Cum.Totals Local Material E18.00 Local Labour E12.00 Ex Works price - JVC to EM 249.71 CN E4,948 Local Overhead $70.00 4 EM Markup ( % EW) 0.12% JVC Profit $55.00 EM Price to end user E250 (JVC) 6 E295 Total E249.71 EM E4,941 COMPONENT COSTS & PRICING OTHER KEY INPUTS: (Where applicable) ( Sourced from EM) Tot. Forex Balance: Total Material Cost / unit $100.00 5 Yr: -21,637 JVC Equity: CNI 50.00% Local material in Amazonia 20.0% E20.00 10yr -$2,273 JVC Equity: EM 50.00% (%% total Cost) E80.00 Royalty % (to EM) 11.00% 5 CIF charges 2.5% 2.00 Local Content % 62.1% EM Markup ( Total Cost) 10.00% E8.20 Rent of Premises E80 Total Margin ( % Revenue ) 9.09% (to CNI) CIF Price to JVC 190.20 Monetary figures in GBP '000s %% Import Duty on components 5.00% Capital Investment $1,000 EM Profitability Analysis : CNI Profitability Analysis: % Unit sales - exports 217 0.35% Unit Sales- Domestic $4,000 80.85%% Component Sales to JVC E1,148 23.23% Rent from JVC (800 16.17% Royalty from JVC E3,628 73.43%% JVC Profit Share E148 2.99% JVC Profit Share (148 2.98% Total 24,941 100.00% Total E4,948 100.00% Collaboration Exercise - Financial Planning