Question

The worksheet MarketSize contains information on market size, market share, product price, profit margin, and market growth. Write formulas that compute each year’s profit and the total 10–year profit.

I G Year 1 Market size Market share Price Profit margin Market size growth Years of growth 1000 0.4 100 0.2 0.08 H I J K L M N O P R S D2 gives year 1 market size D3 gives market share (same each year) D4 gives product price (same each year) D5 gives Profit margin (same each year). For example a 20% profit margin on a $100 product means unit cost is $80. D6 gives growth rate of market size D7 gives years of market growth. For example, the market grew at the rate of 0.08 in year 2-4, and remains the same thereafter Fill in the spreadsheet below to determine each year's profit and the total 10 year profit. Your results should change if any of the inputs in D2:07 change. 2 3 4 5 Year Size Share Units sold Revenue Cost Profit Total Profit

Answer 1

0.4 Year 1 Market Size 1000 Market Share Price 100 Profit Margin Market Size Growth 0.08 Years of Growth 3 0.2 1 -C3 14 Year Size Share Units sold Revenue Cost Profit =C12*C13 =C14*$C$4 =C15-C17 =C15*$C$5 =IF(D11>($C$7+1),C12,(C12*(1+$C$6))) =C13 =D12*D13 =D14*$C$4 =D15-D17 =D15*$C$5 =IF(E11>($C$7+1), D12,(D12*(1+$C$6))) =D13 =E12*E13 =E14*$C$4 =E15-E17 =E15*$C$5 =IF(F11>($C$7+1), E12,(E12*(1+$C$6))) =E13 =F12*F13 =F14*$C$4 =F15-F17 F15*$C$5 =IF(G11>($C$7+1),F12,(F12*(1+$C$6))) =F13 =G12*G13 =G14*$C$4 =G15-G17 =G15*$C$5 16 17 19 20 Total Profit =SUM(C17:L17)

H13 Year Size Share Units sold Revenue Cost Profit =IF(H11>{$C$7+1),G12, (G12*(1+$C$6))) =G13 =H12*H13 =H14*$C$4 =H15-H17 =H15*$C$5 =1F(111>{$C$7+1),H12, (H12*(1+$C$6))) =IF(J11>/($C$7+1),112,112*(1+$C$6))) =IF(K11>{$C$7+1),J12, (J12*(1+$C$6))) =IF(L11>{$C$7+1),K12,(K12*(1+$C$6))) =113 =J13 =K13 =112*113 =J12*J13 =K12*K13 L12*L13 1=114*$C$4 =J14*$C$4 =K14*$C$4 =L14*$C$4 =115-117 =J15-J17 =K15-K17 =L15-L17 =115*$C$5 =J15*$C$5 =K15*$C$5 =L15*$C$5

1000 Year 1 Market Size Market Share Price Profit Margin Market Size Growth Years of Growth 0.2 0.08 Year 1,260 1,260 1,260 0.4 0.4 0.4 Size Share Units sold Revenue Cost Profit 1,000 0.4 400 40,000 32,000 8,000 1,080 0.4 432 43,200 34,560 8,640 1,166 0.4 467 46,656 37,325 9,331 504 50,388 40,311 10,078 504 50,388 40,311 10,078 1,260 0.41 504 50,388 40,311 10,078 1,260 0.4 504 50,388 40,311 10,078 1,260 0.4 504 50,388 40,311 10,078 504 50,388 40,311 10,078 10 1,260 0.4 504 50,388 40,311 10,078 Total Profit 96,515