FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
10th Edition
ISBN: 9781259964947
Author: Libby
Publisher: MCG
Bartleby Related Questions Icon

Related questions

bartleby

Concept explainers

Question

4

### Goal Kick Sports New Store Financial Model

#### Parameters
- **Store Size (square feet):** 7,500
- **Total Fixed Assets:** $280,000
- **Straight Line Depreciation Period:** 10 years
- **Discount Rate:** 10%
- **Tax Rate:** 33%
- **Inflation Rate:** 3%
- **Cost of Merchandise (% of sales):** 28%
- **First Year Sales Revenue:** $950,000
- **Annual Growth Rate of Sales:** 15%

#### Operating Expenses
- **Rent Per Square Foot:** $30
- **Labor Cost:** $150,000
- **Other Expenses:** $235,000

---

### Model

| Year                         | 1         | 2         | 3         | 4         | 5         |
|------------------------------|-----------|-----------|-----------|-----------|-----------|
| **Sales Revenue**            | $950,000  | $1,092,500| $1,256,375| $1,444,831| $1,661,556|
| **Cost of Merchandise**      | $266,000  | $305,900  | $351,785  | $404,483  | $465,236  |
| **Labor Cost**               | $150,000  | $172,500  | $198,375  | $228,131  | $262,351  |
| **Rent**                     | $225,000  | $225,000  | $234,000  | $234,000  | $243,547  |
| **Other Expenses**           | $235,000  | $235,000  | $242,050  | $242,050  | $249,372  |
| **Net Operating Income**     | $74,000   | $164,100  | $269,946  | $392,942  | $536,037  |
| **Depreciation Expense**     | $28,000   | $28,000   | $28,000   | $28,000   | $28,000   |
| **Net Income Before Tax**    | $18,000   | $108,000  | $213,946  | $336,942  | $480,037  |
| **Income Tax**               | $
expand button
Transcribed Image Text:### Goal Kick Sports New Store Financial Model #### Parameters - **Store Size (square feet):** 7,500 - **Total Fixed Assets:** $280,000 - **Straight Line Depreciation Period:** 10 years - **Discount Rate:** 10% - **Tax Rate:** 33% - **Inflation Rate:** 3% - **Cost of Merchandise (% of sales):** 28% - **First Year Sales Revenue:** $950,000 - **Annual Growth Rate of Sales:** 15% #### Operating Expenses - **Rent Per Square Foot:** $30 - **Labor Cost:** $150,000 - **Other Expenses:** $235,000 --- ### Model | Year | 1 | 2 | 3 | 4 | 5 | |------------------------------|-----------|-----------|-----------|-----------|-----------| | **Sales Revenue** | $950,000 | $1,092,500| $1,256,375| $1,444,831| $1,661,556| | **Cost of Merchandise** | $266,000 | $305,900 | $351,785 | $404,483 | $465,236 | | **Labor Cost** | $150,000 | $172,500 | $198,375 | $228,131 | $262,351 | | **Rent** | $225,000 | $225,000 | $234,000 | $234,000 | $243,547 | | **Other Expenses** | $235,000 | $235,000 | $242,050 | $242,050 | $249,372 | | **Net Operating Income** | $74,000 | $164,100 | $269,946 | $392,942 | $536,037 | | **Depreciation Expense** | $28,000 | $28,000 | $28,000 | $28,000 | $28,000 | | **Net Income Before Tax** | $18,000 | $108,000 | $213,946 | $336,942 | $480,037 | | **Income Tax** | $
**DATAFILE: GoalKick**

**Gold Kick Sports (GKS)** is a retail chain that sells youth and adult soccer equipment. 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 assess new locations under consideration for expansion.

**(a)** Use Excel’s Formula Auditing tools to audit the model and correct any errors found. What is the total discounted cash flow (in dollars) calculated by the corrected spreadsheet? (Round your answer to the nearest dollar.)

   **Answer:** [Your Calculated Value]

**(b)** Once you are comfortable that the model is correct, use Scenario Manager to generate a Scenario Summary report that gives Total Discounted Cash Flow for the following scenarios:

|                  | Scenario       |
|------------------|----------------|
|                  | 1   | 2   | 3  | 4  |
| **Tax Rate**     | 32% | 24% | 37% | 24%|
| **Inflation Rate**| 3%  | 4%  | 2%  | 4% |
| **Annual Growth of Sales** | 26% | 26% | 26% | 24% |

What is the range of values for the Total Discounted Cash Flow (in dollars) for these scenarios? (Round your answer to the nearest dollar.)

**Need Help?**

- **Reset**

The table presented is a Scenario table, which includes four different scenarios with varying tax rates, inflation rates, and annual growth of sales. These variables are essential factors in calculating the net discounted cash flow, which helps in assessing the financial viability of opening new store locations.
expand button
Transcribed Image Text:**DATAFILE: GoalKick** **Gold Kick Sports (GKS)** is a retail chain that sells youth and adult soccer equipment. 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 assess new locations under consideration for expansion. **(a)** Use Excel’s Formula Auditing tools to audit the model and correct any errors found. What is the total discounted cash flow (in dollars) calculated by the corrected spreadsheet? (Round your answer to the nearest dollar.) **Answer:** [Your Calculated Value] **(b)** Once you are comfortable that the model is correct, use Scenario Manager to generate a Scenario Summary report that gives Total Discounted Cash Flow for the following scenarios: | | Scenario | |------------------|----------------| | | 1 | 2 | 3 | 4 | | **Tax Rate** | 32% | 24% | 37% | 24%| | **Inflation Rate**| 3% | 4% | 2% | 4% | | **Annual Growth of Sales** | 26% | 26% | 26% | 24% | What is the range of values for the Total Discounted Cash Flow (in dollars) for these scenarios? (Round your answer to the nearest dollar.) **Need Help?** - **Reset** The table presented is a Scenario table, which includes four different scenarios with varying tax rates, inflation rates, and annual growth of sales. These variables are essential factors in calculating the net discounted cash flow, which helps in assessing the financial viability of opening new store locations.
Expert Solution
Check Mark
Knowledge Booster
Background pattern image
Accounting
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.
Recommended textbooks for you
Text book image
FINANCIAL ACCOUNTING
Accounting
ISBN:9781259964947
Author:Libby
Publisher:MCG
Text book image
Accounting
Accounting
ISBN:9781337272094
Author:WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:Cengage Learning,
Text book image
Accounting Information Systems
Accounting
ISBN:9781337619202
Author:Hall, James A.
Publisher:Cengage Learning,
Text book image
Horngren's Cost Accounting: A Managerial Emphasis...
Accounting
ISBN:9780134475585
Author:Srikant M. Datar, Madhav V. Rajan
Publisher:PEARSON
Text book image
Intermediate Accounting
Accounting
ISBN:9781259722660
Author:J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:McGraw-Hill Education
Text book image
Financial and Managerial Accounting
Accounting
ISBN:9781259726705
Author:John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:McGraw-Hill Education