DATAFile: Goal kick Goal Kick Sports (CS) is a real chain that seis youth and adult soccer cuipment. The GKS financial planning group has developed a spreadsheet model to calculate the net discounted cash flow of the first five years of operations for a new store. This model is used to a wacations under consideration for expansion a) w cel's Tom Auditipa tools to udt is moon and correct any errors found. What is the total discounted can now in dollars) calculated by the corrected spreadsheet. (Round you answer to the nearest dolar) ID) Once you are comfortable that the model is correct, use Scenario Menager to gewrate a Scenato Summary report that gives Total Discounted Cash row for the following scenarios Scenario 3 1 4 Tax Rate 322293921 Inflation Rate 12 Annual Growth of Sale 241 0913 warna was the total bouwted Cow can the scenarie? Round your answer to the nearest autor) Need Help? B G D C E A Goal Kick Sports New Store Financial Model Parameters 7,500 $ 280,000 5 10% 33% 2% 28% $ 950,000 15% Store Size (square feet) 5 Total Fixed Assets 6 Straight Line Depreciation period 7 Discount Rate 8 Tax Rate 9 Inflation Rate 10 Cost of Merchandise (% of sales) 11 12 First Year Sales Revenue 13 Annual Growth Rate of Sales 14 15 Operating Expenses 16 17 18 19 20 Model 21 22 23 24 25 26 27 25 29 30 31 32 Rent Per Square Foot $ Labor Costs Other Expenses S 30 150,000 235,000 Year Sales Revenue 1 $950,000 2 $1,092,500 3 $1,256,375 4 $1,444,831 5 $1,661,556 Cost of Merchandise Labor Cost Rent Other Expenses $266,000 $150,000 $225,000 $235,000 $305,900 $153,000 $299,250 $239,700 $351,785 $156,060 $398,003 $244.494 $404,553 $159,181 $529,343 $249,384 $465,236 $162,365 $704,027 $254,372 Net Operating Income Depreciation Expense $74,000 $56,000 $94,650 $56,000 $106,034 $56,000 $102,370 $56,000 $75,557 $56,000 Net Income Before Tax Income Tax $18,000 $5,940 $38,650 $12,755 $50,034 $16,511 $46,370 $15,302 $19,557 $6,454 35 30 37 Net After Tax Income Plus Depreciation Expense Annual Cash Flow $12,060 $56,000 $68,060 $25,896 $56,000 $81,896 $33,522 $56,000 $89,522 $31,068 556,000 $87,068 $13,103 $56,000 $69,103 30 40 41 Discounted Cash Flow $61,873 567,682 $67,260 Total Discounted Cash Flow 559,469 $42.908 52 $299,191 DATAFile: Goal kick Goal Kick Sports (CS) is a real chain that seis youth and adult soccer cuipment. The GKS financial planning group has developed a spreadsheet model to calculate the net discounted cash flow of the first five years of operations for a new store. This model is used to a wacations under consideration for expansion a) w cel's Tom Auditipa tools to udt is moon and correct any errors found. What is the total discounted can now in dollars) calculated by the corrected spreadsheet. (Round you answer to the nearest dolar) ID) Once you are comfortable that the model is correct, use Scenario Menager to gewrate a Scenato Summary report that gives Total Discounted Cash row for the following scenarios Scenario 3 1 4 Tax Rate 322293921 Inflation Rate 12 Annual Growth of Sale 241 0913 warna was the total bouwted Cow can the scenarie? Round your answer to the nearest autor) Need Help? B G D C E A Goal Kick Sports New Store Financial Model Parameters 7,500 $ 280,000 5 10% 33% 2% 28% $ 950,000 15% Store Size (square feet) 5 Total Fixed Assets 6 Straight Line Depreciation period 7 Discount Rate 8 Tax Rate 9 Inflation Rate 10 Cost of Merchandise (% of sales) 11 12 First Year Sales Revenue 13 Annual Growth Rate of Sales 14 15 Operating Expenses 16 17 18 19 20 Model 21 22 23 24 25 26 27 25 29 30 31 32 Rent Per Square Foot $ Labor Costs Other Expenses S 30 150,000 235,000 Year Sales Revenue 1 $950,000 2 $1,092,500 3 $1,256,375 4 $1,444,831 5 $1,661,556 Cost of Merchandise Labor Cost Rent Other Expenses $266,000 $150,000 $225,000 $235,000 $305,900 $153,000 $299,250 $239,700 $351,785 $156,060 $398,003 $244.494 $404,553 $159,181 $529,343 $249,384 $465,236 $162,365 $704,027 $254,372 Net Operating Income Depreciation Expense $74,000 $56,000 $94,650 $56,000 $106,034 $56,000 $102,370 $56,000 $75,557 $56,000 Net Income Before Tax Income Tax $18,000 $5,940 $38,650 $12,755 $50,034 $16,511 $46,370 $15,302 $19,557 $6,454 35 30 37 Net After Tax Income Plus Depreciation Expense Annual Cash Flow $12,060 $56,000 $68,060 $25,896 $56,000 $81,896 $33,522 $56,000 $89,522 $31,068 556,000 $87,068 $13,103 $56,000 $69,103 30 40 41 Discounted Cash Flow $61,873 567,682 $67,260 Total Discounted Cash Flow 559,469 $42.908 52 $299,191