Scenarios Think of any retailer that operates many stores throughout the country. The retailer is often seeking to open new stores and needs to evaluate the profitability of a proposed location that would be leased for five years. An Excel model is provided in the New Store Financial Model spreadsheet. Use the Scenario Manager to evaluate the cumulative discounted cash flow for the fifth year under the scenarios. Click to view the scenarios. Click to view the New Store Financial Model Spreadsheet with numbers. Click to view the New Store Financial Model Spreadsheet with Excel Functions. Complete the table below. \begin{tabular}{|c|c|c|c|c|c|c|} \hline New Store Financial Model & & & & & & \\ \hline \multicolumn{7}{|l|}{ Data } \\ \hline Store Size (square feet) & 5000 & & & & & \\ \hline Total Fixed Assets & 300000 & & & & & \\ \hline Depreciation period (straight line) & 5 & & & & & \\ \hline Discount Rate & 10% & & & & & \\ \hline Tax Rate & 34% & & & & & \\ \hline Inflation Rate & 2% & & & & & \\ \hline Cost of Merchandise (\% of sales) & 30% & & & & & \\ \hline \multicolumn{7}{|l|}{ Operating Expenses } \\ \hline Labor Cost & 200000 & & & & & \\ \hline Rent Per Square Foot & 28 & & & & & \\ \hline Other Expenses & 325000 & & & & & \\ \hline \multirow[t]{2}{*}{ First Year Sales Revenue } & 800000 & & & & & \\ \hline & & & Year 2 & Year 3 & Year 4 & Year 5 \\ \hline Annual Growth Rate of Sales & & & 20% & 12% & 9% & 5% \\ \hline \multicolumn{7}{|l|}{ Model } \\ \hline & Year & 1 & 2 & 3 & 4 & 5 \\ \hline Sales Revenue & & 800000 & 960000 & 1075200 & 1171968 & 1230566 \\ \hline Cost of Merchandise & & 240000 & 288000 & 322560 & 351590 & 369170 \\ \hline \multicolumn{7}{|l|}{ Operating Expenses } \\ \hline Labor Cost & & 200000 & 204000 & 208080 & 212242 & 216486 \\ \hline Rent Per Square Foot & & 140000 & 142800 & 145656 & 148569 & 151541 \\ \hline Other Expenses & & 325000 & 331500 & 338130 & 344893 & 351790 \\ \hline Net Operating Income & & -105000 & -6300 & 60774 & 114674 & 141579 \\ \hline Depreciation Expense & & 60000 & 60000 & 60000 & 60000 & 60000 \\ \hline Net Income Before Tax & & -165000 & -66300 & 774 & 54674 & 81579 \\ \hline Income Tax & & -56100 & -22542 & 263 & 18589 & 27737 \\ \hline Net After Tax Income & & -108900 & -43758 & 511 & 36085 & 53842 \\ \hline Plus Depreciation Expense & & 60000 & 60000 & 60000 & 60000 & 60000 \\ \hline Annual Cash Flow & & -48900 & 16242 & 60511 & 96085 & 113842 \\ \hline Discounted Cash Flow & & -44455 & 13423 & 45463 & 65627 & 70687 \\ \hline Cumulative Discounted Cash Flow & & -44455 & -31031 & 14431 & 80059 & 150746 \\ \hline \end{tabular} New Store Financial Model Data Store Size (square feet) Total Fixed Assets Depreciation period (straight line) Discount Rate Tax Rate Inflation Rate Cost of Merchandise (\% of sales) Operating Expenses Labor Cost Rent Per Square Foot Other Expenses First Year Sales Revenue Annual Growth Rate of Sales Model Sales Revenue Cost of Merchandise Operating Expenses Labor Cost Rent Per Square Foot Other Expenses Net Operating Income Depreciation Expense Net Income Before Tax Income Tax Net After Tax Income Plus Depreciation Expense Annual Cash Flow Discounted Cash Flow Cumulative Discounted Cash Flow Year 28 5 \begin{tabular}{|l|r|} \hline 10% & \\ \hline 34% & \\ \hline 2% & \\ \hline 30% & \\ \hline & \\ \hline 200000 & \\ \hline 325000 & \\ \hline \end{tabular} 800000 \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & Year 2 & Year 3 \\ \hline & 20% & 12% \\ \hline 1 & & \\ \hline & & \\ \hline \end{tabular} =B18 =C24(1+D20) =D24(1+E20) =$B$11E24 =E24(1+F20) 4 5% =$B$11C24 =B14 =$B$15$B$5 =B16 =C24C25C27C28C29 =$B$6/$B$7 =C30C31 =C32$B$9 =C32C33 =C31 =C34+C35 =C36/(1+$B$8)C23 =D27(1+$B$10) =$B$11F24 =D28(1+$B$10) =E27(1+$B$10) =F27(1+$B$10) =F29(1+$B$10) =G24G25G27G28G29 \begin{tabular}{|l|l|} \hline=F24F25F27F28F29 & =G24G25G27G28G29 \\ \hline=$B$6/$B$7 & =$B$6/$B$7 \\ \hline=F30F31 & =G30G31 \\ \hline=F32$B$9 & =G32$B$9 \\ \hline=F32F33 & =G32G33 \\ \hline=F31 & =G31 \\ \hline=F34+F35 & =G34+G35 \\ \hline=F36/(1+$B$8)F23 & =G36/(1+$B$8)G23 \\ \hline=E38+F37 & =F38+G37 \\ \hline \end{tabular} =C37 =C38+D37 =E24E25E27E28E29 =$B$6/$B$7 =E30E31 =E32$B$9 =E32E33 =E31 =E34+E35 =E36/(1+$B$8)E23 =D38+E37 =F24(1+G20) =$B$11G24 5 \begin{tabular}{|c|c|c|c|} \hline C29(1+$B$10) & =D29(1+$B$10) & =E29(1+$B$10) & =F29(1+$B$10) \\ \hline=D24D25D27D28D29 & =E24E25E27E28E29 & =F24F25F27F28F29 & =G24G25G27G28G29 \\ \hline=$B$6/$B$7 & =$B$6/$B$7 & =$B$6/$B$7 & =$B$6/$B$7 \\ \hline=D30D31 & =E30E31 & =F30F31 & =G30G31 \\ \hline D32$B$9 & =E32$B$9 & =F32$B$9 & =G32$B$9 \\ \hline=D32D33 & =E32E33 & =F32F33 & =G32G33 \\ \hline =D31 & =E31 & =F31 & =G31 \\ \hline=D34+D35 & =E34+E35 & =F34+F35 & =G34+G35 \\ \hline=D36/(1+$B$8)D23 & =E36/(1+$B$8)E23 & =F36/(1+$B$8)F23 & =G36/(1+$B$8)G23 \\ \hline=C38+D37 & =D38+E37 & =E38+F37 & =F38+G37 \\ \hline \end{tabular} Scenarios Think of any retailer that operates many stores throughout the country. The retailer is often seeking to open new stores and needs to evaluate the profitability of a proposed location that would be leased for five years. An Excel model is provided in the New Store Financial Model spreadsheet. Use the Scenario Manager to evaluate the cumulative discounted cash flow for the fifth year under the scenarios. Click to view the scenarios. Click to view the New Store Financial Model Spreadsheet with numbers. Click to view the New Store Financial Model Spreadsheet with Excel Functions. Complete the table below. \begin{tabular}{|c|c|c|c|c|c|c|} \hline New Store Financial Model & & & & & & \\ \hline \multicolumn{7}{|l|}{ Data } \\ \hline Store Size (square feet) & 5000 & & & & & \\ \hline Total Fixed Assets & 300000 & & & & & \\ \hline Depreciation period (straight line) & 5 & & & & & \\ \hline Discount Rate & 10% & & & & & \\ \hline Tax Rate & 34% & & & & & \\ \hline Inflation Rate & 2% & & & & & \\ \hline Cost of Merchandise (\% of sales) & 30% & & & & & \\ \hline \multicolumn{7}{|l|}{ Operating Expenses } \\ \hline Labor Cost & 200000 & & & & & \\ \hline Rent Per Square Foot & 28 & & & & & \\ \hline Other Expenses & 325000 & & & & & \\ \hline \multirow[t]{2}{*}{ First Year Sales Revenue } & 800000 & & & & & \\ \hline & & & Year 2 & Year 3 & Year 4 & Year 5 \\ \hline Annual Growth Rate of Sales & & & 20% & 12% & 9% & 5% \\ \hline \multicolumn{7}{|l|}{ Model } \\ \hline & Year & 1 & 2 & 3 & 4 & 5 \\ \hline Sales Revenue & & 800000 & 960000 & 1075200 & 1171968 & 1230566 \\ \hline Cost of Merchandise & & 240000 & 288000 & 322560 & 351590 & 369170 \\ \hline \multicolumn{7}{|l|}{ Operating Expenses } \\ \hline Labor Cost & & 200000 & 204000 & 208080 & 212242 & 216486 \\ \hline Rent Per Square Foot & & 140000 & 142800 & 145656 & 148569 & 151541 \\ \hline Other Expenses & & 325000 & 331500 & 338130 & 344893 & 351790 \\ \hline Net Operating Income & & -105000 & -6300 & 60774 & 114674 & 141579 \\ \hline Depreciation Expense & & 60000 & 60000 & 60000 & 60000 & 60000 \\ \hline Net Income Before Tax & & -165000 & -66300 & 774 & 54674 & 81579 \\ \hline Income Tax & & -56100 & -22542 & 263 & 18589 & 27737 \\ \hline Net After Tax Income & & -108900 & -43758 & 511 & 36085 & 53842 \\ \hline Plus Depreciation Expense & & 60000 & 60000 & 60000 & 60000 & 60000 \\ \hline Annual Cash Flow & & -48900 & 16242 & 60511 & 96085 & 113842 \\ \hline Discounted Cash Flow & & -44455 & 13423 & 45463 & 65627 & 70687 \\ \hline Cumulative Discounted Cash Flow & & -44455 & -31031 & 14431 & 80059 & 150746 \\ \hline \end{tabular} New Store Financial Model Data Store Size (square feet) Total Fixed Assets Depreciation period (straight line) Discount Rate Tax Rate Inflation Rate Cost of Merchandise (\% of sales) Operating Expenses Labor Cost Rent Per Square Foot Other Expenses First Year Sales Revenue Annual Growth Rate of Sales Model Sales Revenue Cost of Merchandise Operating Expenses Labor Cost Rent Per Square Foot Other Expenses Net Operating Income Depreciation Expense Net Income Before Tax Income Tax Net After Tax Income Plus Depreciation Expense Annual Cash Flow Discounted Cash Flow Cumulative Discounted Cash Flow Year 28 5 \begin{tabular}{|l|r|} \hline 10% & \\ \hline 34% & \\ \hline 2% & \\ \hline 30% & \\ \hline & \\ \hline 200000 & \\ \hline 325000 & \\ \hline \end{tabular} 800000 \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & Year 2 & Year 3 \\ \hline & 20% & 12% \\ \hline 1 & & \\ \hline & & \\ \hline \end{tabular} =B18 =C24(1+D20) =D24(1+E20) =$B$11E24 =E24(1+F20) 4 5% =$B$11C24 =B14 =$B$15$B$5 =B16 =C24C25C27C28C29 =$B$6/$B$7 =C30C31 =C32$B$9 =C32C33 =C31 =C34+C35 =C36/(1+$B$8)C23 =D27(1+$B$10) =$B$11F24 =D28(1+$B$10) =E27(1+$B$10) =F27(1+$B$10) =F29(1+$B$10) =G24G25G27G28G29 \begin{tabular}{|l|l|} \hline=F24F25F27F28F29 & =G24G25G27G28G29 \\ \hline=$B$6/$B$7 & =$B$6/$B$7 \\ \hline=F30F31 & =G30G31 \\ \hline=F32$B$9 & =G32$B$9 \\ \hline=F32F33 & =G32G33 \\ \hline=F31 & =G31 \\ \hline=F34+F35 & =G34+G35 \\ \hline=F36/(1+$B$8)F23 & =G36/(1+$B$8)G23 \\ \hline=E38+F37 & =F38+G37 \\ \hline \end{tabular} =C37 =C38+D37 =E24E25E27E28E29 =$B$6/$B$7 =E30E31 =E32$B$9 =E32E33 =E31 =E34+E35 =E36/(1+$B$8)E23 =D38+E37 =F24(1+G20) =$B$11G24 5 \begin{tabular}{|c|c|c|c|} \hline C29(1+$B$10) & =D29(1+$B$10) & =E29(1+$B$10) & =F29(1+$B$10) \\ \hline=D24D25D27D28D29 & =E24E25E27E28E29 & =F24F25F27F28F29 & =G24G25G27G28G29 \\ \hline=$B$6/$B$7 & =$B$6/$B$7 & =$B$6/$B$7 & =$B$6/$B$7 \\ \hline=D30D31 & =E30E31 & =F30F31 & =G30G31 \\ \hline D32$B$9 & =E32$B$9 & =F32$B$9 & =G32$B$9 \\ \hline=D32D33 & =E32E33 & =F32F33 & =G32G33 \\ \hline =D31 & =E31 & =F31 & =G31 \\ \hline=D34+D35 & =E34+E35 & =F34+F35 & =G34+G35 \\ \hline=D36/(1+$B$8)D23 & =E36/(1+$B$8)E23 & =F36/(1+$B$8)F23 & =G36/(1+$B$8)G23 \\ \hline=C38+D37 & =D38+E37 & =E38+F37 & =F38+G37 \\ \hline \end{tabular}