Understanding a Retail Enterprise Using

The House

“Understanding a Retail Enterprise Using

Spreadsheet Analysis”

Updated by

Paul T. McGurr

Fort Lewis College

Prepared to Accompany

Retailing, Sixth Edition

by

Robert F. Lusch

University of Oklahoma

Patrick Dunne

Texas Tech University

Copyright © 2008 by South-Western

All right reserved. No part of this product may be reproduced, transmitted or used in any form or by any means except as provided in the South-Western end-user license agreement found in the disk package.

Requests for permission to make copies of any part of the work should be mailed to the following address: Permissions Department, South-Western, 5191 Natorp Blvd, Mason, OH 45040.

Produced in the United States of America

0-03-032816-0

TABLE OF CONTENTS

Preface

Note to Instructor

Spreadsheet Analysis

Introduction

Phase One Spreadsheet Model: Basic Concepts

Phase One Exercises

Exercise One-A, “The Negative Impact of

External Forces on Store Performance”

Exercise One-B, “The Positive Impact of

External Forces on Store Performance”

Exercise Two-A, “The Impact of Different

Marketing and Financial Strategies on

Store Performance”

Exercise Two-B, “The Impact of Different

Marketing and Financial Strategies on

Store Performance”

Phase Two Spreadsheet Model: Basic Concepts

Phase Two Exercises

Exercise Three-A, “The Impact of Improved

Demographics on Store Performance”

Exercise Three-B, “The Impact of Altering the

Target Market on Store Performance”

Exercise Four-A, “The Impact of Increased

Competition from Dollar General and Wal*Mart

on Store Performance”

Exercise Four-B, “The Impact of Increased

Competition from Direct Mail Retailers on

Store Performance”

Exercise Five-A, “Evaluating Alternative

Distribution Channels”

Exercise Five-B, “The Impact of New Merchandise

Supply Sources”

Exercise Six-A, “The Impact of Changing Local

Legislation on Store Performance”

Exercise Six-B, “The Impact of a National Sales

Tax on Store Performance”

Phase Three Spreadsheet Model: Basic Concepts

Phase Three Exercises

Exercise Seven-A, “Evaluating a New Location

for The House”

Exercise Seven-B, “The Impact of a Second

Location on the Existing Location”

Phase Four Spreadsheet Model: Basic Concepts

Phase Four Exercises

Exercise Eight-A, “Developing a Six-Month

Merchandise Budget Using BOM Stock-to-Sales

Method”

Exercise Eight-B, “Developing a Six-Month

Merchandise Budget Using BOM Stock-to-Sales

Method”

Exercise Nine-A, “Developing a Six-Month

Merchandise Budget Using the Basic Stock Method”

Exercise Nine-B, “Developing a Six-Month Merchandise

Budget Using the Percentage Variation Method”

Phase Five Spreadsheet Model: Basic Concepts

Phase Five Exercises

Exercise Ten-A, “Evaluating the Impact of a

Price Increase”

Exercise Ten-B, “Evaluating the Impact of a

Price Decrease”

Exercise Eleven-A, “Evaluating the Impact of a

New Advertising Campaign”

Exercise Eleven-B, “Using Advertising to Expand

the Trade Area”

Exercise Twelve-A, “Evaluating the Impact

of a Personal Shopping Service”

Exercise Twelve-B, “Evaluating the Impact

of a Layaway Service”

Exercise Thirteen-A, “The Impact of a Store

Layout and Design Strategy”

Exercise Thirteen-B, “A Further Evaluation of a

Store Layout and Design Strategy”

Exercise Fourteen-A, “The Impact of a Change in

Compensation Policy”

Exercise Fourteen-B, “Evaluating a Salesforce

Training Program”

PREFACE

The House is a spreadsheet analysis of the financial performance of a family clothing store in a small college town. As you read and work with the material in this electronic text, you can answer the problems and, if necessary, print out your answers. The software used is Microsoft Office, which integrates word processing (Microsoft Word) and spreadsheet analysis (Excel). You will be able to work the problems as they are presented since the spreadsheet worksheets are embedded into this electronic text.

Although the spreadsheet problems are designed for computer computation, it is possible to do all the required computations with a calculator or by hand. Also, if you wish, you can create your own spreadsheet programs on software other than Excel, such as Lotus 1-2-3. If you are having problems setting up your spreadsheet please review a tutorial on spreadsheets, see a computer lab advisor, or seek help from your instructor who may be able to help you directly or refer you to someone that may be of help.

The spreadsheet model is introduced in five phases which increase in sophistication. Generally the spreadsheet exercises can be used with any of the many retail principles and retail management textbooks on the market. The five phases coincide with the contents of Retailing (2008) by Dunne and Lusch, however, as previously stated, the exercises are applicable to other books. Two exercises are developed to accompany each chapter of Retailing. In the first phase, one only needs to be familiar with a few basic accounting concepts. This phase is used to acquaint you with a basic return on asset model. Such a model is a good frame of reference for managing profit performance in retailing. The second phase introduces critical environmental forces such as consumer behavior, channel behavior, competitor behavior, legal constraints, and how changes in these environments influence store performance. In the third phase we incorporate certain location concepts which allow different location decisions to be assessed. The fourth phase introduces and develops a sixth-month merchandise budget. Finally, in phase five, certain retail decisions such as pricing, promotion, merchandising, and compensation are discussed and a model is developed to assess how different retail decisions will influence store performance.

Throughout the spreadsheet exercises you will be presented with problems that you can work to develop an understanding of important retail concepts. However, in all exercises you will be sensitized to the fact that everything that happens in retailing has a bottom line financial impact. The problems are presented in brief overview scenarios of a The House, which is a family clothing store. You will be provided with an electronic spreadsheet which you will use to simulate the effects of the decisions made or phenomena occurring in The House.

Once you become familiar with the spreadsheet models introduced in The House, you can use them to further your understanding and study of retailing. For example, the models presented can be used in a variety of ways.

·  The models can be used to further acquaint you with how financial performance of a store changes as a result of certain changes in the external environment and decisions that store managers make. This can be done by modifying some of the assumptions in the exercises you will be presented.

·  The models can be used to help you design and simulate a retail store that you might consider opening.

·  The models can be used to help you develop your own problems that allow you to explore the many interrelationships found in retailing.

NOTE TO THE INSTRUCTOR

The House is based on spreadsheet analysis. This electronic text is based on Microsoft Office and integrates word processing (Microsoft Word) and spreadsheet analysis (Excel). All the problems presented can be solved using any of the spreadsheet software that is on the market. In many cases, your students might find it helpful if you devote some time to reviewing the basic methodology of spreadsheet analysis and how to set up the basic commands to manipulate data in rows and columns.

The House is presented in five phases which increase in sophistication and follow the content of the first 13 chapters of Retailing (2008) by Dunne and Lusch. In phase one the student is acquainted with a basic return on asset model. They must manipulate sales and variable operating and fixed operating costs to arrive at net profit. Then the net profit margin (net profit/net sales) is multiplied by asset turnover (net sales/total assets) to arrive at return on assets (net profit/total assets).

During phase two of The House, the student is introduced to some determinants of net sales. They are shown that traffic (i.e. the number of people or households who visit the store annually) is equal to market coverage times penetration level times average shopping frequency.

Where:

·  market coverage is the number of households residing in the trade area of the retail store;

·  penetration level is the percent of households residing in the trade area of the retail store who visit the store within a year;

·  average shopping frequency is the average number of times a year that a household that is a customer visits the store.

Next they are introduced to the concept of closure. The closure rate is a measure of how well retailers do in converting traffic into customers. It measures the percent of the people who visit the store that purchase something. When traffic is multiplied by closure the result is transactions. Finally they learn that net sales is equal to transactions multiplied by average transaction size. The problems the students are presented in this phase allow them to manipulate these determinants of sales in order to examine the impact on the financial performance of the store. This is possible because the variables in phase two are added to the basic model presented in phase one.

In phase three we introduce two additional variables to the model that help the student understand location decisions. We assume that the trade area of the store is a circle that surrounds the store with the store at the center. The radius of this circle we refer to as the trade radius. When one computes the area of the circle they get the square miles in the trade area. This area is then multiplied by the population density (people or households per square mile) of the trade area to obtain market coverage. With this knowledge the student is able to manipulate the size of the trade area to assess the impact on the financial performance of the store. This is possible because these new variables are added to those which are used to build the spreadsheet models in phases one and two. In short, the phase three spreadsheet model incorporates the prior two models into a more sophisticated model of store performance.

In phase four we show the student how to develop a six-month merchandise budget using spreadsheet analysis. They are instructed how to develop this budget using different dollar stock planning methods such as the stock-to-sales method, the basic stock method, and the percentage variation method.

Finally, in phase five, some additional refinements are made to help the student better understand the typical transaction in a store. We introduce the student to the idea that the average transaction size in the store is equal to the average number of items purchased multiplied by average item price. This allows the student to see how variations in the average number of items purchased and the price point of these items can influence net sales and thus profit performance of the store. These new variables are also linked to the variables introduced in phases one, two, and three.

The instructor is encouraged to add to the questions presented for each of the 26 exercises. Many questions of a “what-if” nature are good for classroom discussion. For example, ask the students to determine what would happen if certain parameters in the different models presented in the four phases would change. Students find it very educational to experience firsthand the impact of a change in average transaction size, market coverage, traffic, trade radius, variable operating, and fixed operating costs. The instructor might also have the students graph the financial performance of the store under different scenarios. For example, the student could be asked to plot net profit margin, asset turnover, and return on assets under the different scenarios that are simulated in each exercise. This can help the student visualize the impact better and can also provide a good platform for classroom discussion. All spreadsheet software programs have convenient and easy-to-use graphing options.

SPREADSHEET ANALYSIS

Spreadsheet analysis, often referred to as worksheet analysis, is a computerized version of a tablet of paper with columns and rows. Historically, accountants or business people, when analyzing financial data and other numerical data, would work with pads of paper and an adding machine or calculator. Today such a cumbersome way of working and computing is not necessary. This is because many brands of computer software can provide an electronic pad of paper with columns and rows in which data can be input and easily manipulated. This brief overview of spreadsheet software is not intended to be comprehensive in scope. If you are not familiar with spreadsheet software you should consult your instructor.

In an electronic worksheet or spreadsheet there is the potential for hundreds or thousands of rows and columns. The intersection of each row and column is referred to as a cell. You should check the specific brand of software you are using to determine how rows and columns can be handled. However, even the simplest of spreadsheet programs will be able to handle the problems presented in The House. Most spreadsheet or worksheet software have analysis and presentation features. The analysis portion of the software deals with how to manipulate numerical data in the various cells of the worksheet. The presentation features deal with how to display the results of your analysis. Our brief overview of spreadsheet analysis will discuss some fundamentals of analysis. Since the presentation features vary substantially with each brand of software, you should consult the instruction manual for the software you are using. It will be helpful for you to learn how to print tables of data and also to present the data in a graphical format.

Fundamental Concepts

·  A cell is the intersection of a column and row.

·  Columns are defined by letters and rows are defined by numbers. For example the first cell in the worksheet is defined as A1. If we want the cell that is the intersection of the third column and tenth row it would be labeled C10.

·  The title is the description you give the worksheet. This can be composed of both alphabetical and numerical symbols.

·  Mathematical symbols often used are: + for addition, - for subtraction, * for multiplication, and / for division.

Entering Data

Data is entered into a spreadsheet by highlighting the cell the data will be entered into and then typing in the numerical data. Only numerical data can be input.

Entering Formulas

Typically a formula is entered by highlighting the cell in the spreadsheet where the formula is applicable. For example if we want to subtract the value in C3 from the value in C1, the formula would be: = C1 - C3. If we add C1 and C3 the formula would be: = C1 + C3; if we multiply C1 by C3 we would have: = C1*C3; if we divide C1 by C3 we would have: = C1/C3.