Excel Assignment

Ch10 - S&F in the Int'l ET

Table 10.6 Pro Forma Income Statements								Table 10.8 Data Table Type I							Table 10.10 Data Table Type II
Pro Forma Income Statements								What if exchange rate changes? Assume the range is between 100 and 120							What if exchange rate AND growth rate change?
Sales and Profit Forecasting in the International Arena								Sensitivity Analysis: (What if Analysis)							Assume the exchange rate changes between 100 and 120 & sales grow between 1 and 10%
(For an American Firm Selling Products in Japan)								DATA TABLE TYPE I							Sensitivity Analysis: (What if Analysis)
								The effect of exchange rate volatility on estimated net profit in US$							DATA TABLE TYPE II (Year 4)
Total Sales for Year 0		¥20,000,000							Year 1	Year 2	Year 3	Year 4			The effect of exchange rate & sales volatility on estimated net profit in US$
Exchange Rate ($ vs. yen)		110							$ 36,082	$ 37,886	$ 39,780	$ 41,769			$41,769	1%	2%	3%	4%	5%	6%	7%	8%	9%	10%
Commission Rate		10%						100	$ 39,690	$ 41,675	$ 43,758	$ 45,946			100	$39,335	$40,916	$42,544	$44,221	$45,946	$47,722	$49,548	$51,426	$53,358	$55,343
Tax Rate		40%						101	$ 39,297	$ 41,262	$ 43,325	$ 45,491			101	38,945	40,511	42,123	43,783	45,491	47,249	49,058	50,917	52,829	54,795
Annual Growth Rate for Sales in Japan		5%						102	$ 38,912	$ 40,857	$ 42,900	$ 45,045			102	38,564	40,114	41,710	43,354	45,045	46,786	48,577	50,418	52,312	54,258
COGS (% of Net Sales)		65%						103	$ 38,534	$ 40,461	$ 42,484	$ 44,608			103	38,189	39,724	41,305	42,933	44,608	46,332	48,105	49,929	51,804	53,731
								104	$ 38,163	$ 40,072	$ 42,075	$ 44,179			104	37,822	39,342	40,908	42,520	44,179	45,886	47,642	49,449	51,306	53,214
Growth of Sales in Japan (in Yen)								105	$ 37,800	$ 39,690	$ 41,675	$ 43,758			105	37,462	38,968	40,518	42,115	43,758	45,449	47,189	48,978	50,817	52,708
	Year 0	Year 1	Year 2	Year 3	Year 4			106	$ 37,443	$ 39,316	$ 41,281	$ 43,345			106	37,108	38,600	40,136	41,718	43,345	45,020	46,743	48,516	50,338	52,210
Gross Sales	¥ 20,000,000	¥ 21,000,000	¥ 22,050,000	¥ 23,152,500	¥ 24,310,125			107	$ 37,093	$ 38,948	$ 40,896	$ 42,940			107	36,762	38,239	39,761	41,328	42,940	44,600	46,307	48,062	49,867	51,722
Commission Paid in Japan	¥ 2,000,000	¥ 2,100,000	¥ 2,205,000	¥ 2,315,250	¥ 2,431,013			108	$ 36,750	$ 38,588	$ 40,517	$ 42,543			108	36,421	37,885	39,393	40,945	42,543	44,187	45,878	47,617	49,405	51,244
Net Sales	¥ 18,000,000	¥ 18,900,000	¥ 19,845,000	¥ 20,837,250	¥ 21,879,113			109	$ 36,413	$ 38,233	$ 40,145	$ 42,152			109	36,087	37,538	39,031	40,569	42,152	43,781	45,457	47,180	48,952	50,773
COGS	¥ 11,700,000	¥ 12,285,000	¥ 12,899,250	¥ 13,544,213	¥ 14,221,423			110	$ 36,082	$ 37,886	$ 39,780	$ 41,769			110	35,759	37,196	38,677	40,201	41,769	43,383	45,044	46,751	48,507	50,312
Gross Profit	¥ 6,300,000	¥ 6,615,000	¥ 6,945,750	¥ 7,293,038	¥ 7,657,689			111	$ 35,757	$ 37,545	$ 39,422	$ 41,393			111	35,437	36,861	38,328	39,838	41,393	42,992	44,638	46,330	48,070	49,859
								112	$ 35,438	$ 37,209	$ 39,070	$ 41,023			112	35,120	36,532	37,986	39,483	41,023	42,609	44,239	45,917	47,641	49,413
Growth of Sales in Dollars								113	$ 35,124	$ 36,880	$ 38,724	$ 40,660			113	34,810	36,209	37,650	39,133	40,660	42,232	43,848	45,510	47,219	48,976
	Year 0	Year 1	Year 2	Year 3	Year 4			114	$ 34,816	$ 36,557	$ 38,384	$ 40,304			114	34,504	35,891	37,320	38,790	40,304	41,861	43,463	45,111	46,805	48,546
Gross Sales	$ 181,818	$ 190,909	$ 200,455	$ 210,477	$ 221,001			115	$ 34,513	$ 36,239	$ 38,051	$ 39,953			115	34,204	35,579	36,995	38,453	39,953	41,497	43,085	44,719	46,398	48,124
- Commission	$ 18,182	$ 19,091	$ 20,045	$ 21,048	$ 22,100			116	$ 34,216	$ 35,926	$ 37,723	$ 39,609			116	33,909	35,272	36,676	38,121	39,609	41,139	42,714	44,333	45,998	47,709
= Net Sales	$ 163,636	$ 171,818	$ 180,409	$ 189,430	$ 198,901			117	$ 33,923	$ 35,619	$ 37,400	$ 39,270			117	33,620	34,971	36,363	37,795	39,270	40,788	42,349	43,954	45,605	47,302
- COGS	$ 106,364	$ 111,682	$ 117,266	$ 123,129	$ 129,286			118	$ 33,636	$ 35,317	$ 37,083	$ 38,937			118	33,335	34,675	36,054	37,475	38,937	40,442	41,990	43,582	45,218	46,901
= Gross Profit	$ 57,273	$ 60,136	$ 63,143	$ 66,300	$ 69,615			119	$ 33,353	$ 35,021	$ 36,772	$ 38,610			119	33,054	34,383	35,751	37,160	38,610	40,102	41,637	43,216	44,838	46,507
- Tax	$ 22,909	$ 24,055	$ 25,257	$ 26,520	$ 27,846			120	$ 33,075	$ 34,729	$ 36,465	$ 38,288			120	32,779	34,097	35,454	36,851	38,288	39,768	41,290	42,855	44,465	46,119
= Net Profit	$ 34,364	$ 36,082	$ 37,886	$ 39,780	$ 41,769
Questions
1	Calculate the Japanese version of year on base and four years forecasted income statements based on the data provided in the above table							Line Graph for the Results of Data Table Type I							Line Graph for the Results of Data Table Type II
2	Convert the Japanese income statement to $ and include in the computation tax expense and net profit.
3	Perform sensitivity analysis, Data Table Type I, on the Pro Forma financial statements to examine how sensitive net profit is to the exchange rate assumption being violated.
4	Perform sensitivity analysis, Data Table Type II, on the Pro Forma financial statements to examine how sensitive net profit is to a simultaneous violation of both the 5% growth rate assumption and the110 exchange rate assumptions.
5	Produce line graphs for the results of the Data Tables Type I and Type II. Make sure you include the following: chart title, x and y axes titles, legend, and adjust the estimmated net profit bounds according to the figures in your book.

The Effect of Exchange Rate Volatility on Estimated Net Profits (Year 0 - Year 4)

Year 1 $36,082 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 39690 39297.0297029703 38911.7647058823 38533.9805825243 38163.4615384615 37800 37443.3962264151 37093.4579439252 36750 36412.8440366972 36081.8181818182 35756.7567567568 35437.5 35123.8938053097 34815.7894736842 34513.0434782609 34215.5172413793 33923.0769230769 33635.593220339 33352.9411764706 33075 Year 2 $37,886 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 41674.5 41261.8811881188 40857.3529411765 40460.6796116505 40071.6346153846 39690 39315.5660377358 38948.1308411215 38587.5 38233.4862385321 37885.9090909091 37544.5945945946 37209.375 36880.0884955752 36556.5789473684 36238.6956521739 35926.2931034483 35619.2307692308 35317.3728813559 35020.5882352941 34728.75 Year 3 $39,780 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 43758.225 43324.9752475248 42900.2205882353 42483.713592233 42075.2163461538 41674.5 41281.3443396226 40895.5373831776 40516.875 40145.1605504587 39780.2045454545 39421.8243243243 39069.84375 38724.092920354 38384.4078947368 38050.6304347826 37722.6077586207 37400.1923076923 37083.2415254237 36771.6176470588 36465.1875 Year 4 $41,769 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 45946.13625 45491.224009901 45045.2316176471 44607.8992718447 44178.9771634615 43758.225 43345.4115566038 42940.3142523364 42542.71875 42152.4185779816 41769.2147727273 41392.9155405405 41023.3359375 40660.2975663717 40303.6282894737 39953.1619565217 39608.7381465517 39270.2019230769 38937.4036016949 38610.1985294118 38288.446875

Exchange Rates

Estimated Net Profits (S)