The House - Cengage
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.
Entering Percents
In The House whenever you have a percent to enter into the spreadsheet, such as the gross margin percent, it is entered in decimal form. For instance a 38% gross margin is entered as .38. You should also keep this in mind when you view your results. For example, if you obtain a return on assets of .12 you need to mentally move the decimal place two positions to the right to obtain a 12% return on assets.
CONTRIBUTION MARGIN INCOME STATEMENT
The basic format of The House spreadsheet analysis is the contribution margin income statement. In this format every operating expense of a firm is classified either as a variable expense or as a fixed expense.
Variable expenses, such as cost of merchandise sold, commissions, and advertising increase or decrease as sales of product rise and fall. They are estimated by determining the relationship of the cost to total sales. This relationship is stated as a proportion or percentage of net sales. Thus the formula for variable costs is entered into the spreadsheet as a percentage multiplied by the amount of net sales. For most retail firms variable costs are separated into two categories. Cost of merchandise sold represents the amount paid specifically for the merchandise that is sold in the store. The cost of merchandise sold percentage is subtracted from 100% to arrive at the gross margin (or gross profit) percentage. The gross profit percentage represents the percentage of each net sales dollar available to pay operating expenses after the cost of the product sold is subtracted. The second category of retail variable expenses includes those operating expenses which tend to rise and fall with the amount of sales. These include sales commissions and some of the advertising costs and some of the payroll costs, depending on the operation of the store. These variable operating expenses are calculated by multiplying net sales by the estimated percentage of these costs in relationship to net sales. For reporting purposes these variable operating expenses are added to the fixed operating expenses to arrive at total operating expenses.
Fixed operating expenses are those expenses that stay the same regardless of the amount of sales that are made. These include costs such as lease payments, depreciation of store furniture and fixtures, utility costs, interest costs, managerial payroll, and other similar costs that must be paid regardless of the amount of sales during a particular month. These costs are not calculated as a proportion of net sales; rather, the total amount of these costs for each month is determined and multiplied by 12 to arrive at an annual total. The fixed operating costs are added to the variable operating costs to arrive at the total operating costs.
Total operating costs are subtracted from the gross margin to arrive at the store’s net profit (or net operating profit). Net profit is the primary measurement of the store’s operating success. A store’s income taxes (not covered in this analysis) are calculated based upon the net profit reported.
The contribution margin income statement is a common tool used for analysis of operating activities because of the ease by which “what-if” scenarios can be entered to determine their impact on operating profit. Changes in the proportion of variable costs to net sales (such as those related to price increases or decreases) can be analyzed after a simple change to the cost of merchandise sold percentage or to the variable operating cost percentage. Changes in fixed costs are entered directly into the fixed cost calculation.
When the contribution margin income statement has been correctly entered into a spreadsheet analysis, these simple adjustments to the formula will very quickly determine the potential impact of the scenario on the net operating profit of the store.
INTRODUCTION
Anne and Fred Harriot are owners and operators of The House. The House is a family apparel store in Hamilton, a small midwestern community of 6,860 households (excluding college students) comprising 18,180 permanent residents. Hamilton is located at the crossroads of State Highway #43 and #62. In addition to the permanent residents, there are 2,800 full-time students who attend a small private college. These college students, many of whom have roommates, occupy 1,650 separate apartments, dormitory rooms, or other residences that are separate from the permanent resident population. Thus, the total households consist of 6,860 permanent and 1,650 nonpermanent, or college student households, for a total of 8,510 households. On the other hand, the total population is 20,980, which consists of 18,180 permanent residents and 2,800 college students.
Background
Hamilton is centrally located near several other communities. The next largest town is Troy, which is 39 miles to the east and has a population of 44,675. However, there are five smaller towns within a 15-mile radius and range in size from 1,400 to 4,100 and have a total population of 17,900. Anne and Fred took over the management of The House after Ann’s father, the founder of the store, retired in 2007. Anne’s father, Bill Henderson, started The House in 1970 after he graduated from the local college. Initially, The House was a menswear store with college men as its target market. However, over the years, Bill Henderson found that the college men’s market was too thin to support a store and thus in 1973 Bill added women’s apparel targeted at college females. In 1979 Bill, once again, broadened The House’s target market and added apparel to appeal to all age groups including children.
Anne and Fred had been married for five years when they moved to Hamilton in late October 2007 to takeover management of The House. At the time, Fred was an assistant store manager at a JC Penney store in Cleveland and Anne was a registered nurse at a Cleveland hospital. Anne found the pressure of nursing in a big city hospital too demanding and she looked favorably upon the idea of returning to Hamilton. She recalled fondly where she grew up and the idea of managing the family business sounded appealing. Fred was also excited about managing and owning his own store.
Purchase of Business
Mr. Henderson offered to sell The House to Anne and Bill for the book value of its cash, receivables, fixtures, equipment, and inventory or $700,000. The fixtures and equipment were valued at $90,000. The House occupied a 7,000-square-foot building in downtown Hamilton. The building was owned by Mr. Henderson who agreed to lease the building to Anne and Bill for $3,500 per month. In addition, he agreed to take a downpayment of $100,000 on the $700,000 purchase price and to finance the remaining $600,000 on a three-year, 6% interest-only note with interest of $3,000 due the first of each month. At the end of three years they would need to obtain permanent financing from a source other than Mr. Henderson.
After some serious thought Anne and Fred agreed to purchase The House from Anne’s father. They took possession of the store on November 1, 2007 just in time for the Christmas season. In 2007 sales were $1,590,040 and of this amount $417,840 occurred during November and December. Total assets at year-end 2007 were $685,100, which included $98,700 in cash, $45,200 in accounts receivable, $451,200 in inventory (at cost) and the $90,000 worth of fixtures.
The House is open Monday through Wednesday from 10:00 a.m. to 6:00 p.m. and on Thursday through Saturday from 10:00 a.m. to 9:00 p.m. The House is closed on Sunday and also on Thanksgiving Day, Christmas, and New Year’s Day. Fred and Anne work in the store daily. However, Friday and Saturday are their busiest days. They have four full-time salespeople, four part-time salespeople, one full-time janitor, one bookkeeper, an accounts payable and accounts receivable clerk, a secretary who also serves as a receptionist, and a full-time buyer who buys women’s and children's apparel. Mr. Henderson, in addition to running the store for the last 36 years, also served as the menswear buyer. He has agreed to continue to perform this activity for the next 12 months for a retainer of $15,000 and to take Fred along on three buying trips. After the 12-month transition, Fred will perform the buying function for the menswear line.
Operating Characteristics
The House has 40% of its sales in menswear, 35% in womenswear, and 25% in children apparel. In total, The House has 1,765 stock keeping units (SKUs). Due to the variety of merchandise, prices vary considerably. In 2007, the average transaction size was $59.44 (before sales tax) which included on average 5.0 items. However, prices for any single item can vary from $1.79 to $459.00. The permanent residents of Hamilton seem to enjoy shopping at The House as evidenced by the fact that 65% of Hamilton’s total households visited The House at least once during 2007. These households visited the store an average of 7.8 times in 2007. Importantly, not all store visits result in a purchase. In 2007 only 62% of store visits resulted in a purchase.
The House has been operating on a 38% gross margin and Fred and Anne expect this to continue for 2008. They have carefully analyzed their expected costs for 2008 and estimate fixed operating costs per month at $31,550 and variable operating costs at 11.2% of sales. The fixed operating costs include a $1,500 monthly salary each for Fred and Anne. Variable operating costs include advertising costs of 3.2% of sales.
Retail Environment
The retail environment of Hamilton is typical of small college towns. There are five gasoline stations, two pharmacies, two supermarkets, a bakery, a butcher shop, a womenswear store, a Dollar General department store, a jewelry and gift store, a television and home appliance store, a furniture store, an office supply and equipment store, a True Value Hardware store, and a Wal*Mart. There is also a dry cleaner, two Laundromats, three banks, a travel agency, six churches, one funeral home, two used car dealers, a Chevrolet Dealership, a Ford Dealership, a farm and feed supply store, a movie theater, two real estate agencies, three insurance agencies, five barbers/beauticians, six fast-food restaurants, two grills that cater to college students, two family-style cafes, an optometrist, four family physicians, a pediatrician, and three dentists. The House can be found on the town square where a bank, county government offices, one of the family-style cafes, the womenswear store, and the Family Dollar department store are located. There are also two vacant stores on the Town Square, which have been vacant for 18–24 months. Also on the town square is an insurance agent, a real estate agent, a barber, a travel agent, and two law offices. At the eastern edge of town there is an eight-year-old, 85,000-square-foot Wal*Mart discount department store. The local college is a half-mile north of downtown Hamilton.
Special Note
You will probably find it helpful to reread this brief description of The House before you begin each exercise. Data is provided in this description which can be used to help solve some of the exercises.
PHASE ONE SPREADSHEET MODEL:
BASIC CONCEPTS
The problems you will work in phase one relate to part one—“Introduction to Retailing” of Retailing (2008) by Dunne and Lusch. For phase one you need to understand the following concepts:
Net Sales is the dollar value of annual sales.
Cost of Merchandise Sold is the monetary amount owed or paid suppliers (including freight) for the merchandise the retailer sells during the period. Cost of merchandise as a percent of sales is 100% minus the gross margin percent.
Gross Margin is the Net Sales minus the Cost of Merchandise Sold. Gross margin percent is the Gross Margin as a percent of sales.
Variable Operating Costs are costs that increase proportionately with the sales volume of the store. For example if variable costs are 30% of sales then for each one-dollar increase in sales the variable costs rise 30 cents. Common examples of variable costs in retailing are sales commissions and advertising.
Fixed Operating Costs are the costs the retailer incurs regardless of the level of sales. Common fixed costs in retailing are insurance, interest on debt, certain salaries, and rent.
Total Operating Cost is the sum of fixed operating and variable operating costs.
Net Profit is the amount remaining after total operating costs are subtracted from gross margin.
Total Assets is the value of the things the retailer owns, which it uses to operate its business. Examples of assets include fixtures, equipment, cash, and inventory.
Also you will need to know that the notation used is as follows: (+) designates addition, (-) designates subtraction, (/) designates division, (*) designates multiplication. Incidentally, this is the only level of math you will need to work the simulation exercises. When multiplication occurs you can round off your answer to the nearest one-hundredth of a decimal (i.e. have two digits to the right of the decimal point) or you should feel free to use higher precision if you desire. Naturally, when a series of multiplications occur a rounding error will occur. You should not be worried about such minor differences in computations.
In addition to the preceding, you need to understand some important relationships between the preceding concepts. These relationships are best understood in terms of three basic financial ratios. Repeatedly throughout the simulations you will see that the impact of changes in the retail business have an ultimate effect on these three financial ratios.
Net Profit Margin is the ratio of net profits divided by net sales. If the net profit margin is 3%, it says that on each dollar of sales the retailer has earned 3 cents of net profit.
Asset Turnover is the ratio of net sales divided by total assets. If this ratio is 3 times, it says that the retailer generates $3 in sales for each dollar invested in assets.
Return on Assets is net profit divided by total assets. If the return on assets is 8%, it says that the retailer has earned 12 cents on each dollar invested in assets.
You also need to know that the net profit margin multiplied by the rate of asset turnover yields return on assets. This basic relationship is illustrated with the following equation:
(net profit/net sales) * (net sales/total assets) = (net profit/total assets)
OR
(net profit margin) * (asset turnover) = (return on assets)
The simulation model you will be working with in phase one will look like the following:
SAMPLE SPREADSHEET
PHASE ONE
[pic]
If you double-click on this spreadsheet you will bring up the Excel software. You can click on the different cells in this spreadsheet and see how each of the elements is defined. Note that for each cell we either entered a number or a formula. For example for net sales we entered $1,590,040, however, for cost of merchandise sold we entered the formula: Net Sales * (1 - gross margin %) or B2 * (1 - .38). B2 is the location of net sales and .38 represents the 38% gross margin percent The House experienced.
PHASE ONE
EXERCISE ONE-A
The Negative Impact of External Forces on
Store Performance
Anne and Fred just learned that Creative Ceramics, a producer of ceramic giftware, has announced it will close its plant which employs 220 people. The employees of Creative Ceramics represent 180 households with a total population of 495. It is estimated that two-thirds of these households will leave Hamilton for other employment. Anne and Fred estimate that the effect of this will be to lower sales between 3 and 5% for 2008 from their 2007 level. Please simulate the impact of a 3%, 4%, and 5% decline in 2008 sales on the net profit margin, asset turnover, and return on assets.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet, which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the three scenarios (3%, 4%, and 5% sales declines) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. HINT--one way to do this is to simply copy all of the data or formulas in column one (baseline model) into the following three columns. Once this is done you can go into the sales cells (B2, C2, D2) and alter the level of sales. All of the remaining changes will be made automatically due to the simulation nature of this software. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE ONE-A
[pic]
QUESTIONS
1. Which financial ratio was most impacted by the decline in population? Why?
2. Which costs are affected by the sales decline? Why?
3. What was the impact of the sales decrease on fixed costs? Why?
4. Why does the net profit margin drop at a faster rate than does sales?
PHASE ONE
EXERCISE ONE-B
The Positive Impact of External Forces on
Store Performance
Anne and Fred just learned that the local gift and jewelry store is cutting back its line of women’s apparel and giftware. The apparel consisted largely of scarves, silk blouses, nightwear, and sweaters. As a result, they expect that sales at The House will rise 1% or 2% in 2008 compared to 2007.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the three scenarios (1%, 1.5%, and 2% sales increase) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. HINT--one way to do this is to simply copy all of the data or formulas in column one (baseline model) into the following three columns. Once this is done you can go into the sales cells (B2, C2, D2) and alter the level of sales. All of the remaining changes will be made automatically due to the simulation nature of this software. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE ONE-B
[pic]
QUESTIONS
1. Which financial ratio was most impacted by the reduction in competition? Why?
2. Which costs are affected by the sales increase? Why?
3. What was the impact of the sales increase on fixed costs? Why?
4. Why does the net profit margin rise at a faster rate than does sales?
PHASE ONE
EXERCISE TWO-A
The Impact of Different Marketing and Financial
Strategies on Store Performance
Anne and Fred Harriot have decided that in 2008 they will pursue a new marketing and financial strategy in which their goal will be a higher market share. They have noted that many households have been shopping for apparel out of town, especially in Troy, which has a 400,000-square-foot covered mall that includes two department stores and 18 specialty stores. Anne and Fred have developed two alternative strategies: (a) they would increase store hours by being open seven days a week and thus fixed costs would rise from $31,550 monthly to $35,000 per month, but as a result they expect a 15% sales increase; (b) they would keep store hours the same but would introduce a mail order catalog to be mailed four times a year to all households in town and to the five outlying towns. Under this alternative the fixed operating costs would rise to $40,000 per month and variable operating costs would increase by 1.2% of sales. Sales would be projected to increase 36%. Under either strategy they do not expect a rise in total assets. With the increased store hours Fred and Anne Harriot have no need to invest in additional inventory, store fixtures, or other assets. In the case of the direct mail program the production and mailing of the catalog will be contracted out, therefore no additional investment in assets will be required. Which alternative should they pursue?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (expanded store hours or direct mail catalog) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE TWO-A
[pic]
QUESTIONS
1. Should The House change its financial and marketing strategy? Why?
2. What are some of the reasons that the projections made for either the expanded store hours scenario or the direct mail catalog scenario may not materialize as projected? Is one option more risky?
3. How does the expansion of the store hours or the development of a direct mail catalog change the nature of the retailing business for the Harriots?
4. What disadvantages do you foresee if the Harriots implement either of these two scenarios? Why?
PHASE ONE
EXERCISE TWO-B
The Impact of Different Marketing and Financial
Strategies on Store Performance
The House is considering handling a line of higher priced women’s dresses. They believe that these higher priced dresses ($95–$295) will result in a sales increase of 7%. To provide a proper merchandise assortment, an additional investment in inventory of $42,000 would be required. It is expected that as a result of this 7% sales increase, due to the sale of higher priced dresses, the storewide gross margin percent would rise by 1%.
Total assets at year-end 2007 were $685,100, which included $98,700 in cash and $451,200 in inventory (at cost). The House could finance the additional inventory with a combination of cash and trade credit. If this strategy is pursued then total assets will remain at $685,100 but cash on hand will decline. However, Anne and Fred are concerned that the company have an adequate level of cash on hand for emergencies or opportunistic buys. As a result, they are considering investing another $12,000 in cash in the business. If this strategy is pursued then total assets would rise to $697,100. What will happen to financial performance if they invest the additional $12,000 vs. financing the inventory from existing resources in the business?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (finance internally or finance externally) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE TWO-B
[pic]
QUESTIONS
1. Which alternative should be used to finance the increased inventory investment? Why?
2. What are some of the other factors that need to be considered when making this decision? Please explain.
3. What are the risks to a small business in having a minimal amount of cash on hand? What are the benefits?
4. What are the risks to the Harriots in investing additional funds in The oH House? What are the benefits?
PHASE TWO SPREADSHEET MODEL:
BASIC CONCEPTS
The problems you will work in phase two relate to Part Two—“The Retailing Environment” of Retailing (2007) by Dunne and Lusch. For Phase Two of The House you need to understand concepts related to the retailing environment. These concepts deal with the customer, competition, channel, and the legal environment. The following concepts are important to understand:
market coverage is the number of households residing in the trade area of the retail store; this is also often called the number of prospects.
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 visits the store.
traffic is the total number of household visits to the store annually.
closure is the percent of households who visit the store that end up purchasing.
transactions are the annual number of customers who make a purchase.
average transaction size is the average dollar value of a transaction
You also need to understand the following relationships between the preceding concepts:
traffic = (market coverage) * (penetration level) * (average shopping frequency)
transactions = (traffic) * (closure)
net sales = (transactions) * (average transaction size)
For example, if a retailer has a market coverage of 20,000 households, has a penetration level of 60%, and the typical household visits the store six times annually, then the traffic will be (20,000)*(60%)*(6) or 72,000. In brief, the store has 72,000 household visits a year. Now consider that the closure on these visits is 50% then the number of transactions on an annual basis is (72,000)*(50%) or 36,000. Finally, if the average transaction size is $10 then the annual net sales would be (36,000)*($10) or $360,000.
SAMPLE SPREADSHEET
PHASE TWO
[pic]
PHASE TWO
EXERCISE THREE-A
The Impact of Improved Demographics on
Store Performance
Anne and Fred have merchandised The House to appeal to a broad spectrum of households. At the local college, enrollments are projected to rise by 600 students next year and 20 additional faculty will be recruited. In the past, Anne and Fred have found that both faculty and college students spend more on clothing and shop often at The House. However, students tend to spend mostly on fill-in items, while purchasing their more complete wardrobe needs elsewhere, usually at their permanent home. On the other hand, faculty tends to purchase most of their clothing needs at The House.
Based on these improved demographics, Anne and Fred are projecting that for 2008 that the market coverage would rise to 8,725 from 8,510. For 2007, the average shopping frequency was 7.8 times per year, average transaction size was $59.44, and closure was 62%. They do not expect a change in these statistics. What would happen to the financial performance of The House under this scenario?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the scenario (8,725 market coverage) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy of it once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE THREE-A
[pic]
QUESTIONS
1. Why did a modest increase in market coverage have such a significant influence on store performance?
2. Should The House attempt to appeal more to college students and faculty? Why or why not?
3. Do you think the Anne and Fred are correct in their assumption that the increase in the student population will not change shopping frequency, closure and average transaction size? Why?
4. What specific costs will increase if The House begins to have more college customers than in the past? Why?
PHASE TWO
EXERCISE THREE-B
The Impact of Altering the
Target Market
on Store Performance
Anne and Fred have noted that many of their customers are middle age households in the community. These are good loyal customers; however, The House could do a better job of attracting college students and encouraging them to purchase their entire, or most, of their college wardrobe needs at The House. Fred decided to invite to lunch five college males who were active in fraternities. Similarly, Anne invited to lunch five coeds who were active in their sorority. At the lunch both Fred and Anne asked them why they don’t purchase more of their wardrobe needs at The House. Both groups felt The House was a good choice for fill in and staple purchases such as underwear and hose and an occasional sport shirt or casual dress. However, the students (both male and female) believed that The House had a very poor selection of casual clothing for college students. In addition, they did not like shopping at a store that catered to all family members.
That evening Anne and Fred brainstormed while they had dinner. They decided that one possible way to serve the college market would be to develop a special area within their store called the College Shop. In this shop within the store they would feature cotton twill slacks and dresses, polo style sport shirts, belts, Bass loafers, and moderately priced tweed and navy blazers for men, white oxford dress shirts, and a selection of ties.
To help promote the College Shop they would also appoint a fashion advisory board of three college males and three college females, meet with them monthly, and pay them $60 each per meeting. In addition, they would commit to advertising weekly in the college newspaper, but this cost would be reallocated from their existing advertising budget. Finally, it was estimated that a total inventory investment of $95,000 would be needed for the College Shop, however, $35,000 of this amount could be reallocated from current merchandise lines that would be phased out. The additional $60,000 inventory investment would be financed with existing cash balances and trade payables. Anne and Fred expected that penetration among college students would rise to 55%, thus increasing the market penetration to 70% from 65%. To be conservative in their estimates they assumed that closure and average transaction size would be unaffected but that average shopping frequency would rise to eight times per year.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (70% penetration & average shopping frequency 7.8 or 70% penetration & average shopping frequency 8.0) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE THREE-B
[pic]
QUESTIONS
1. What is the impact of the College Shop strategy on traffic and transactions? Why is traffic so important in retailing?
2. Why does the net profit margin improve so dramatically?
3. Do you think the initiation of a College Shop will have any impact on the other areas of The House? Why?
4. What else can Anne and Fred do to ensure that the College Shop is a success?
PHASE TWO
EXERCISE FOUR-A
The Impact of Increased Competition from Dollar General
and Wal*Mart on Store Performance
Anne’s father, the prior owner of The House, left a complete set of books for 20 years. Fred was able to enter the annual sales data into his personal computer and graph annual sales over time. What became very clear was that sales increases would almost identically match the general rate of inflation. This suggested that The House probably had not experienced any real growth in sales since 1985. During this period the competitive situation has also been quite stable. However, recently the local Dollar General store reconfigured its store layout to devote more space to womenswear. At the same time the local Wal*Mart has devoted more space to womenswear. As a consequence, more women are shopping locally for clothing at these two stores. Fred and Anne estimate that average shopping frequency could drop to 7.5 or possibly as low as 7.0 times a year. What would be the impact on store performance?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (average shopping frequency 7.5 or average shopping frequency 7.0) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE FOUR-A
[pic]
QUESTIONS
1. Why did the drop in average shopping frequency have such a dramatic impact on store performance?
2. Should The House attempt to take a counter competitive action? If so what do you recommend?
3. Are the steps The House should take in response to Dollar General the same as they should take in response to Wal*Mart? Why or why not?
4. What are the risks of responding too strongly to the competition? What are the risks of not responding strongly enough?
PHASE TWO
EXERCISE FOUR-B
The Impact of Increased Competition from
Direct Mail Retailers on Store Performance
Recently the residents of Hamilton have been receiving an increasing number of direct mail catalogs from retailers such as L.L. Bean, Lands’ End, Eddie Bauer, and J. Peterman. These direct marketers offer good quality apparel for a fair price and provide 2-5 day delivery of merchandise via Federal Express. Fred estimates that this increased competition will have two impacts in 2008: lower average shopper frequency and reduced average transaction size. If each of these dropped 5%, what would happen to store performance? If each dropped 10%, what would happen to store performance?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (average shopping frequency and average transaction size drop 5% or 10%) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE FOUR-B
[pic]
QUESTIONS
1. Which financial ratios are affected by this increased competition? Why?
2. What actions might The House take to combat this direct mail competition?
3. Is combating direct mail (or Internet) competition more difficult or less difficult than combating competition from physical stores? Explain.
4. Why do you think the Harriots believe the competition from direct mail retailers will reduce shopping frequency and transaction size?
PHASE TWO
EXERCISE FIVE-A
Evaluating Alternative Distribution Channels
Anne and Fred have been traveling twice a year to the Apparel Market in New York City. Recently they learned of an apparel wholesaler who sells primarily via an electronic catalog. Customers of this wholesaler can use their personal computer and their telephone to access this catalog and receive color pictures of all merchandise. All orders can be placed and paid electronically. To purchase the proper modem and software will cost $700 and telephone charges would be $60 monthly. This $700 for software and modem should be treated as an increased cost vs. an asset since it is likely that regular software updates and new modems that are faster will be purchased annually. The wholesaler sells apparel at slightly higher prices as could be obtained by purchasing direct from manufacturers so that cost of merchandise is expected to increase by 1.5% of sales. However, this wholesaler pays all freight charges on orders over $5,500. Currently Fred and Anne are paying all freight charges; therefore they expect to save 2% of sales on the reduced freight cost of merchandise. Since Anne and Fred will only need to take one trip a year to New York City to observe apparel trends, they will also save $5,850 per year in travel costs. What would be the impact on store performance of switching to this wholesaler?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the new wholesaler scenario in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the new scenario. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE FIVE-A
[pic]
QUESTIONS
1. What is the financial impact of switching to the new wholesaler?
2. What other advantages might accrue to The House as a result of dealing with this wholesaler? How might these impact other variables in the simulation model?
3. Why is the question “who will pay the freight?” so important to a retailer?
4. What type of retailer is able to deal directly with a manufacturer? What type of retailer is more likely to deal with a wholesaler? Why?
PHASE TWO
EXERCISE FIVE-B
The Impact of New Merchandise Supply Sources
Recently, while attending a state-wide Chamber of Commerce meeting, Fred met Carolyn Neal, who was the owner of a cut-and-sew apparel manufacturing facility in a small town in the southeastern part of the state. Carolyn was primarily making apparel for large retailers such as Wal*Mart, however, she felt she was becoming too dependent on only a few large customers and wanted to diversify her customer base. Fred obtained her business card. When he returned home he called Carolyn and arranged for Anne and himself to have dinner with her to discuss some business possibilities. Carolyn suggested she manufacture a line of private label women’s and girls’ apparel. If The House could guarantee to purchase $30,000 per quarter with a minimum order of $20,000, and pay within 30 days of shipment, then Carolyn would be willing to private label merchandise for The House. Both Fred and Anne believe that their own store apparel label will help enhance the image of The House and, consequently, attract more business.
After discussing the possibility for a few weeks, Fred and Anne decided to move forward on contracting for the development of their own store brand of women's and girl's apparel. They negotiated a price that would essentially increase their storewide gross margin to 40%, however, with the high minimum order size, their inventory investment would rise by $24,000, and thus they expect total assets to rise a similar amount. The House will spend an additional $28,000 a year on advertising this new line of merchandise. It is estimated that the new private label will result in penetration increasing to 67% or 68%.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (penetration 67% or 68%) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE FIVE-B
[pic]
QUESTIONS
1. What is the financial impact of developing and marketing the private brand of women's and girl's apparel?
2. What other advantages might accrue to The House as a result of developing a private brand? How might these impact other variables in the simulation model?
3. There is usually a significant price savings to the customer when she buys a private brand product. Why is this so?
4. What type of customer will usually shop for private brand products? What type of customer seeks name brand products?
PHASE TWO
EXERCISE SIX-A
The Impact of Changing Local Legislation
on Store Performance
Recently, legislation was passed and will take effect in early 2008 that will require commercial buildings to have sprinkler systems for fire control purposes. The building The House occupies is 45 years old and is not equipped with a sprinkler system. Anne and Fred recently received a letter from Bill Henderson (Anne’s father) informing them that he can't afford the $62,000 to install the sprinkler at the current lease rate of $3,500 monthly. The Harriots’ lease expires at the end of 2007 and Bill Henderson has presented them two options. Option one is to extend their lease until 2012 and have The House pay the leasehold improvement costs, and then write these leasehold improvements off over the five years of the new lease. Under this option their rent would remain at $3,500 monthly. The House would use its line of credit at the bank to borrow $62,000 at 10% interest. The 60 monthly principal and interest payments on this loan would be $1,317.32 and the entire amount could be written off as a business expense. Option two would be to enter into a new annual lease at $5,000 per month. Which option should they take?
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (increased rent vs. making leasehold improvements) in the simulation. HINT: For the leasehold improvement option, assume that total assets rise by $62,000, which is the amount of leasehold improvements. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE SIX-A
[pic]
QUESTIONS
1. What is the financial impact of the different lease options?
2. If the leasehold option is pursued, what other considerations need to be made?
3. The major difference in the two scenarios is found in the asset turnover and the return on assets? Why is this true?
4. To whom is the return on assets most important? Why is it important to Anne and Fred as owners of The House?
PHASE TWO
EXERCISE SIX-B
The Impact of a National Sales Tax on
Store Performance
Anne and Fred just learned that the federal government is considering enacting a national sales tax of 1% or 2% on all non-food purchases. So that customers of The House will not feel this impact if the federal government proceeds with this tax, they have decided to absorb the costs of this federal legislation. Please simulate the impact of this action on the net profit margin, asset turnover, and return on assets.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (1% national sales tax or 2% national sales tax) in the simulation. HINT: For the absorption of the rise in sales tax, increase the variable operating expenses. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are two questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE SIX-B
[pic]
QUESTIONS
1. Which financial ratio was most impacted by the national sales tax? Why?
2. Should The House attempt to absorb all of the national sales tax into its cost of doing business and not pass on any increase to the customer? Why or why not?
3. The local Hamilton Chamber of Commerce has asked Anne and Fred to donate funds to a campaign fighting the national sales tax. Should they contribute? Why?
4. Do you believe consumers consider the local sales tax rate when they determine where they plan on shopping? Would you change your answer if you considered internet shopping in the mix of potential shopping locations?
PHASE THREE SPREADSHEET MODEL:
BASIC CONCEPTS
The problems you will work in phase three relate to Part Three—“Market Selection and Location Analysis” of Retailing (2007) by Dunne and Lusch. The new concepts you need to be familiar with concern the trade area of a retail store. Previously we discussed market coverage. In this phase we learn about the determinants of market coverage. An understanding of the following terms is necessary:
trade area is the geographic area from which a retailer draws its customers.
trade radius is the number of miles from the store from which customers are attracted. This concept assumes that a store's trade area is represented by a circle surrounding the store. The trade radius would be the radius of that circle.
population density is the number of people or households per square mile within the store’s trade area. In this simulation we use households as the measure of population density.
If one combines the concept of market coverage, which we introduced in phase two, with the preceding concepts, one can obtain the following relationship.
market coverage = (22/7) * (trade radius squared) * (population or household density)
To understand the preceding you need to recall that the formula for the area of a circle is pi times the square of the radius of the circle. Where pi is the mathematical constant of 22/7 or 3.142857. Recall that we are assuming that the trade area is circular. If we take pi times the radius squared we get the number of square miles in a circle or the number of square miles in the trade area. If we then multiply this by the population or household density we obtain the total number of people or households in the trade area, or what we refer to as market coverage.
The spreadsheet model you will be working with in the phase three exercises is as follows:
SAMPLE SPREADSHEET
PHASE THREE
[pic]
PHASE THREE
EXERCISE SEVEN-A
Evaluating a New Location for The House
Currently The House is located on the Town Square and draws customers from a three-mile radius that encompasses the entire community.
Fred and Anne are considering opening a second store at the edge of town across the street from the Wal*Mart Discount Department Store. The Wal*Mart attracts people from the local community and the surrounding five smaller towns which are within a 15-mile radius of the Wal*Mart. The population density within this 15-mile trade area averages 24.6 households per square mile.
The building Fred and Anne are considering leasing is 4,500 square feet. Hours of operation would be 10:00 a.m. to 8:00 p.m., Monday-Saturday.
They expect monthly fixed operating costs (which include rent) to be $14,600 and variable operating costs to be 12.4% of sales. They expect that the average transaction size will be $47.24. This is considerably lower than their main store because they believe that most customers will be attracted to shop at the store while they are making a trip to Wal*Mart. Thus, they believe most purchases will be fill-in purchases and not as a result of a destination visit to the store to purchase specific merchandise. The gross margin percent is expected to be the same as for the main store of The House.
Virtually all of the visits will be due to intercepting traffic from the Wal*Mart across the street. They estimate that the Wal*Mart has a 90% penetration level and an average shopping frequency of 14. Fred and Anne estimate that their penetration the first year will be 36% and the average shopping frequency will be 2.5 times per year. In the second year they expect the penetration to rise to 42% and average shopping frequency to rise to 3.0 times per year. For both years they estimate closure will be 65%.
Approximately $88,000 will need to be invested in leasehold improvements and fixtures and $164,000 in inventory. They will also plan to have $48,000 in cash available when they start business for unexpected expenses or losses. Thus, they expect their total assets to be $300,000.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (year one and year two of the new retail store) in the simulation. Note that there are no baseline formulas for this problem. Since you are starting a new store there is no base of comparison. Thus for the first year you need to enter the formula and/or data for each row. HINT: You can refer back the phase three prototype model to see how some of the formulas are set up. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required
EXERCISE SEVEN-A
[pic]
QUESTIONS
1. Is the proposed new location for The House economically viable? Explain.
2. Do you have any suggested strategies that would make The House more profitable in the first year of operation?
3. Should a decision on opening a new store location be based on the projected results of the first two years? Why or why not?
4. Opening a new store is fraught with risk and needs to be considered in great depth before the decision is made. What are some of the things that could go wrong and cause a new store location to fail?
PHASE THREE
EXERCISE SEVEN-B
The Impact of a Second Location on the Existing Location
Fred and Anne are concerned with how their proposed new store (see exercise 7-A) may impact the performance of their existing store. Both believe their trade area will continue to be a three-mile radius within which the population density is 300.859 households per square mile. However, because their new store will somewhat cannibalize their existing store, they have predicted that their penetration would drop to 62% and average shopping frequency would decline to 7.5 times. On the other hand, they believe that they can partially counteract this cannibalization by working more diligently to improve closure. They believe that closure can rise from 62% to 65% with more selling effort.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the cannibalization scenario. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the cannibalization scenario. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE SEVEN-B
[pic]
QUESTIONS
1. What is the financial impact of the proposed new store on the existing store?
2. Can you think of any other things that might be done to minimize the cannibalization effect of the new store?
3. While the new store location may cause some cannibalization, there are many benefits to the opening of a second store. What are some of these benefits and how will they serve to keep costs down?
4. Why is the new store location near Wal*Mart, a major competitor, a desirable location?
PHASE FOUR SPREADSHEET MODEL:
BASIC CONCEPTS
The problems you will work in phase four relate to chapters 8 and 9 in Part Four—“Managing Retail Operations” of Retailing (2007) by Dunne and Lusch.
Chapters 8 and 9 deal with financial planning but focus primarily on developing a six-month merchandise budget. For the problems in this section you need to understand the following concepts which will allow you to develop a six-month merchandise budget.
planned sales for the month = (planned sales percentage for the month)*(planned total sales)
planned BOM stock for the month. There are three methods we will deal with:
1. the stock-to-sales method. The planned BOM stock for the month = (planned sales for the month)*(planned BOM Stock-to-Sales Ratio for the month)
2. the basic stock method. The planned BOM stock for the month = basic stock + planned monthly sales. Where the
basic stock = average stock for the season - average monthly sales for the season
3. the percentage variation method. The planned BOM stock for the month = (average stock for season)*(1/2)[1+(planned sales for the month/average monthly sales)]
planned retail reductions for the month = (planned sales for the month)*(planned retail reduction percentage for the month)
planned EOM stock for the month = (planned BOM stock for the following month)
planned purchases at retail for the month = (planned sales for the month) + (planned retail reductions for the month) + (planned EOM stock for the month) - (planned BOM stock for the month)
planned purchases at cost for the month = (planned purchases at retail for the month) * (100% - planned initial mark up percentage)
planned initial markup for the month = (planned purchases at retail for the month) - (planned purchases at cost for the month)
planned gross margin for the month = (planned initial markup for the month) - (planned retail reductions for the month)
The merchandise budget for exercises 8a and 8b will be as follows. For exercises 9a and 9b the framework will be slightly modified and this will be explained shortly. Please note that this sample budget is not yet set up as a spreadsheet, it is simply a table which represents what a six-month merchandise budget should look like.
SAMPLE
SIX-MONTH MERCHANDISE BUDGET
| |Feb |March |April |May |June |July |Total |
|Planned | | | | | | | |
|BOM | | | | | | | |
|Stock | | | | | | | |
|Planned | | | | | | | |
|Sales | | | | | | | |
|Planned | | | | | | | |
|Retail Re- | | | | | | | |
|ductions | | | | | | | |
|Planned | | | | | | | |
|EOM | | | | | | | |
|Stock | | | | | | | |
|Planned | | | | | | | |
|Purchases | | | | | | | |
|at Retail | | | | | | | |
|Planned | | | | | | | |
|Purchases | | | | | | | |
|at Cost | | | | | | | |
|Planned | | | | | | | |
|Initial | | | | | | | |
|Markup | | | | | | | |
|Planned | | | | | | | |
|Gross | | | | | | | |
|Margin | | | | | | | |
|Planned | | | | | | | |
|BOM Stock | | | | | | | |
|-to-Sales | | | | | | | |
|Ratio | | | | | | | |
|Planned | | | | | | | |
|Sales | | | | | | | |
|Percentage | | | | | | | |
|Planned | | | | | | | |
|Retail | | | | | | | |
|Reduction | | | | | | | |
|Percentage | | | | | | | |
PHASE FOUR
EXERCISE EIGHT-A
Developing a Six-Month Merchandise Budget
Using BOM Stock to Sales Method
Anne Harriot is in the process of developing the merchandise budget for The House for the first six months of 2008. Planned sales for the first half of 2008 are $740,000 and this is divided as follows: February = 9%, March = 11%, April = 14%, May = 21%; June = 22%; July = 23%. Planned total retail reductions are 5% for February and March, 8% for April and May, and 12% for June and July. The planned initial markup percentage is 47%. The planned BOM Stock-to-Sales ratio for each month is as follows: February = 5.9, March = 5.2, April = 5.0, May = 5.0, June = 4.0, July = 5.0. Also they want to begin the second half of the year with $710,000 in inventory at retail.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled. You will need to study the formulas below and enter them into the spreadsheet. Be sure to save your work and print a copy of it once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
The formula for each row is as follows:
planned BOM stock for the month = (planned sales for the month)*(planned BOM Stock-to-Sales Ratio for the month)
planned sales for the month = (planned sales percentage for the month)*(planned total sales or $740,000)
planned retail reductions for the month = (planned sales for the month)*(planned retail reduction percentage for the month)
planned EOM stock for the month = (planned BOM stock for the following month)
planned purchases at retail for the month = (planned sales for the month) + (planned retail reductions for the month) + (planned EOM stock for the month) - (planned BOM stock for the month)
planned purchases at cost for the month = (planned purchases at retail for the month) * (1.0 - planned initial mark up percentage)
planned initial markup for the month = (planned purchases at retail for the month) - (planned purchases at cost for the month)
planned gross margin for the month = (planned initial markup for the month) - (planned retail reductions for the month)
planned BOM stock-to-sales ratio = 5.9 for February, 5.2 for March, 5.0 for April and May, 4.0 for June and 5.0 for July.
planned sales percentage by month = February 9%, March 11%, April 14%, May 21%, June 22%, July 23%.
planned retail reduction percentage by month = 5% for February and March, 8% for April and May, and 12% for June and July.
EXERCISE EIGHT-A
[pic]
QUESTIONS
1. Why is it important to schedule sales and purchases six months in advance?
2. What are the reasons for planned retail reductions? How should The House estimate the percentage to be deducted?OuHouse
PHASE FOUR
EXERCISE EIGHT-B
Developing a Six-Month Merchandise Budget
BOM Stock-to-Sales Method
Anne Harriot is in the process of developing the merchandise budget for The House for the first six months of 2008. Planned sales for the first half of 2008 are $740,000 and this is divided as follows: February = 9%, March = 11%, April = 14%, May = 21%, June = 22%, July = 23%. Planned total retail reductions are 8% for February and March, 5% for April and May, and 10% for June and July. The planned initial markup percentage is 47%. The planned BOM Stock-to-Sales ratio for each month is as follows: February = 6.1, March = 5.3, April and May = 5.0, and June = 4.1, July = 5.2. Also they want to begin the second half of the year with $710,000 in inventory at retail.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled. You will need to study the formulas below and enter them into the spreadsheet. Be sure to save your work and print a copy of it once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
The formula for each row is as follows:
planned BOM stock for the month = (planned sales for the month)*(planned BOM Stock-to-Sales Ratio for the month)
planned sales for the month = (planned sales percentage for the month)*(planned total sales or $740,000)
planned retail reductions for the month = (planned sales for the month)*(planned retail reduction percentage for the month)
planned EOM stock for the month = (planned BOM stock for the following month)
planned purchases at retail for the month = (planned sales for the month) + (planned retail reductions for the month) + (planned EOM stock for the month) - (planned BOM stock for the month)
planned purchases at cost for the month = (planned purchases at retail for the month) * (1.0 - planned initial mark up percentage)
planned initial markup for the month = (planned purchases at retail for the month) - (planned purchases at cost for the month)
planned gross margin for the month = (planned initial markup for the month) - (planned retail reductions for the month)
planned BOM stock-to-sales ratio = 6.1 for February, 5.3 for March, 5.0 for April and May, 4.1 for June, and 5.2 for July.
planned sales percentage by month = February 9%, March 11%, April 14%, May 21%, June 22%, July 23%.
planned retail reduction percentage by month = 8% for February and March, 5% for April and May, and 10% for June and July.
EXERCISE EIGHT-B
[pic]
QUESTIONS
1. Why do most retailers begin their six month merchandise budget with February, the second month of the year?
2. Why is the initial markup so much higher than the ending gross margin percentage?OuHouse
PHASE FOUR
EXERCISE NINE-A
Developing a Six-Month Merchandise Budget
Using the Basic Stock Method
Anne Harriot is in the process of developing the merchandise budget for The House for the first six months of 2008. They have decided to utilize the basic stock method of merchandise budgeting. Planned sales for the first half of 2008 are $740,000 and this is divided as follows: February = 9%, March = 11%, April = 14%, May = 21%, June = 22%, and July = 23%. Planned total retail reductions are 8% for February and March, 5% for April and May, and 12% for June and July. The planned initial markup percentage is 47%. They desire the rate of inventory turnover for the season to be two times. Also they want to begin the second half of the year with $410,000 in inventory at retail.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled. You will need to study the formulas below and enter them into the spreadsheet. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
The formula for each row is as follows:
planned BOM stock for the month = basic stock + planned monthly sales
basic stock = average stock for the season - average monthly sales for the season
average monthly sales for the season = total planned sales for the season/number of months in the season
average stock for the season = total planned sales for the season/estimated inventory turnover rate for the season
planned sales for the month = (planned sales percentage for the month)*(planned total sales or $740,000)
planned retail reductions for the month = (planned sales for the month)*(planned retail reduction percentage for the month)
planned EOM stock for the month = (planned BOM stock for the following month)
planned purchases at retail for the month = (planned sales for the month) + (planned retail reductions for the month) + (planned EOM stock for the month) - (planned BOM stock for the month)
planned purchases at cost for the month = (planned purchases at retail for the month) * (1.0 - planned initial mark up percentage)
planned initial markup for the month = (planned purchases at retail for the month) - (planned purchases at cost for the month)
planned gross margin for the month = (planned initial markup for the month) - (planned retail reductions for the month)
planned retail reduction percentage by month = 8% for February and March, 5% for April and May, and 12% for June and July.
EXERCISE NINE-A
[pic]
QUESTIONS
1. Why does the basic stock method result in less fluctuation in inventory levels than does the stock to sales method?
2. Why is it easier for The House to keep track of their in-store inventory at retail prices even though the inventory must be accounted for at cost?OuHouse
PHASE FOUR
EXERCISE NINE-B
Developing a Six-Month Merchandise Budget
Using the Percentage Variation Method
Anne Harriot is in the process of developing the merchandise budget for The House for the first six months of 2008. They have decided to utilize the percentage variation method of merchandise budgeting. Planned sales for the first half of 2008 are $740,000 and this is divided as follows: February = 9%, March = 11%, April = 14%, May = 21%, June = 22%, July = 23%. Planned total retail reductions are 8% for February and March, 5% for April and May, and 12% for June and July. The planned initial markup percentage is 47%. They desire the rate of inventory turnover for the season to be two times. Also they want to begin the second half of the year with $410,000 in inventory at retail.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled. You will need to study the formulas below and enter them into the spreadsheet. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
The formula for each row is as follows:
planned BOM stock for the month = average stock for season *(1/2)[1+(planned sales for the month/average monthly sales)]
average monthly sales for the season = total planned sales for the season/number of months in the season
average stock for the season = total planned sales for the season/estimated inventory turnover rate for the season
planned sales for the month = (planned sales percentage for the month)*(planned total sales or $740,000)
planned retail reductions for the month = (planned sales for the month)*(planned retail reduction percentage for the month)
planned EOM stock for the month = (planned BOM stock for the following month)
planned purchases at retail for the month = (planned sales for the month) + (planned retail reductions for the month) + (planned EOM stock for the month) - (planned BOM stock for the month)
planned purchases at cost for the month = (planned purchases at retail for the month) * (1.0 - planned initial mark up percentage)
planned initial markup for the month = (planned purchases at retail for the month) - (planned purchases at cost for the month)
planned gross margin for the month = (planned initial markup for the month) - (planned retail reductions for the month)
planned retail reduction percentage by month = 8% for February and March, 5% for April and May, and 12% for June and July.
EXERCISE NINE-B
[pic]
QUESTIONS
1. Why does the percentage variation method result in less fluctuation in inventory levels than does the stock to sales method?
2. Why does The House need to maintain inventory levels so much higher than the expected sales for any one month?OuHouse
PHASE FIVE SPREADSHEET MODEL:
BASIC CONCEPTS
The problems you will work in phase five relate to chapters 9-14 in Part Four--"Managing Retail Operations" and Part Five – “Retail Administration” of Retailing (2007) by Dunne and Lusch. These decisions are primarily merchandising, pricing, advertising and promotion, store design and layout, personal selling, and retail administration.
To complete the problems in this section you need to understand the following concepts:
average number of items purchased is the number of items the typical customer purchases on a visit to the store
average item price is the average price which a customer pays for an item purchased at the store
total items purchased is the total number of items that are sold by the store over a one year period
Utilizing the preceding concepts coupled with the concept of total transactions we can establish the following relationships.
average transaction size = (average number of items purchased) * ( average item price)
average number of items purchased = (total items purchased)/(total transactions)
The spreadsheet model you will be working with in the phase five exercises is as follows:
SAMPLE SPREADSHEET
PHASE FIVE
[pic]
PHASE FIVE
EXERCISE TEN-A
Evaluating the Impact of a Price Increase
Fred and Anne have built a very loyal group of customers that patronize The House. Although only 62% of visitors actually make a purchase, they purchase an average of five items. These items have an average item price of $11.89, which results in an average transaction size of $59.44. The cost of goods is 62% of sales and the gross margin return on sales is 38%. Anne has argued that they should be less concerned with closure and more concerned with higher prices and thus a higher gross margin percent. She has proposed that they raise prices by 10%. Under this scenario the cost of goods sold as a percent of sales would be (.62/1.1) or 56.36% and the gross margin return on sales would be 43.64% (100% - 56.36%).
Fred has argued that if they raise prices by 10% that average items sold per customer would drop 20%. On the other hand, Anne argues that their customers are not that aware of prices and that although some customers will decide not to buy due to the higher prices, that this will not be large. She believes that a 10% price increase will result in a 12% drop in average items purchased.
The spreadsheet you will need to run this simulation is attached. You will need to double click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (10% price increase and 12% or 20% reduction in average number of items purchased) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE TEN-A
[pic]
QUESTIONS
1. Should prices be raised by 10%? Explain.
2. Are there other parameters in the model that might be influenced by the price increase? Explain.
3. What risk does The House take by raising prices in order to increase profits?
4. What steps must The House take to change its image if it takes the strategy of raising prices?
PHASE FIVE
EXERCISE TEN-B
Evaluating the Impact of a Price Decrease
Fred mentioned to Anne if a price increase of 10% were to be profitable (see exercise 10-A) then shouldn't they also consider what might happen if they dropped prices by 10%? He argued that with a 10% price decrease that the average number of items purchased would go up by at least 20%. Anne disagreed because she felt that customers would not notice a 10% price decrease. She felt that if prices went down 10% that at most the average number of items purchased would rise by 12%. Under this scenario the cost of goods sold as a percent of sales would be (.62/.9) or 68.89% and the gross margin percent would be 31.11% (100% - 68.89%).
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (10% price reduction and 12% or 20% increase in average number of items purchased) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy and handing it in to the instructor if required.
EXERCISE TEN-B
[pic]
QUESTIONS
1. Evaluate the financial impact of the proposed price decrease.
2. What other considerations should be taken into account other than the impact on closure of the proposed price decrease? Why are these other considerations important?
3. Why does a seemingly logical strategy, like lowering prices to increase sales, have such a negative impact on profitability?
4. What risk does The House take by lowering prices in an attempt to increase profits?
PHASE FIVE
EXERCISE ELEVEN-A
Evaluating the Impact of a New Advertising Campaign
The advertising and promotion for The House consists of a half-page advertisement in the Saturday newspaper, a weekly one-inch advertisement in two church bulletins, a full-page advertisement in the high school yearbook, sponsorship of a float in the Fourth of July parade, and sponsorship of the local high school football and basketball teams. The local newspaper advertising is $400 per week, the advertisements in the church bulletins is $2000 per year, the ad in the yearbook is $750, the parade float is $3,250, and the team sponsorships are $3000 each. In addition, there are a variety of other advertising and promotions that vary each year but generally total less than $20,000 annually. Advertising has been approximately 3.2% of sales.
Currently all of the advertising/promotion has a long-run objective of improving store performance by promoting the store image. Anne believes that the advertising expenditures need to have a short-term objective such as attracting new customers and increasing patronage from existing customers. She also believes that The House needs to spend a minimum of $2,500 per month with expenditures concentrated during periods of peak demand (March–May and November–December).
Anne has recommended the following advertising expenditures: January and February = $2,500, March = $3,000, April and May = $3,500, June = $3,000, July-September = $2,500, October = $5,000, November - December = $9,000. Furthermore, 40% would be spent on the local AM Radio station and 60% on the local newspaper replacing the current newspaper ads. All advertising will feature items on sale. The House will continue its public service sponsorships, and its church bulletins and yearbook ads. As a result, Anne believes that penetration will rise to 68% and the average number of items purchased will rise from 5.0 to 5.2 or 5.3. Since advertising will be directed at the local trade area, she does not expect the trade radius to increase.
The spreadsheet you will need to run this simulation is attached. You will need to double click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (average items 5.2 or 5.3) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE ELEVEN-A
[pic]
QUESTIONS
1. Should The House change the advertising strategy? Explain.
2. Are there any problems in switching the advertising program from the long run objective of store image enhancement to short-term sales objectives? How might these problems be overcome?
3. Given the nature of the business of The House, which type of advertising – image enhancement or sales objectives – do you think is most appropriate? Why?
4. What other considerations should be taken into account other than the impact on average number of items purchased due to the increased advertising? What other variables in the simulation may change?
PHASE FIVE
EXERCISE ELEVEN-B
Using Advertising to Expand the Trade Area
There are five smaller towns that surround the community of Hamilton where The House is located. Generally people in these towns travel regularly to Hamilton to shop at Wal*Mart. These towns are within a 15-mile radius and range in size from 520 to 1650 households. However, five miles to the east is a town of 640 households and five miles to the west is a town of 703 households. Fred believes that The House should begin an advertising program in each of these towns. Each town has a small weekly newspaper which could be used to reach these geographic markets. With this expanded trade area of five miles, the total households of the trade area would be 9,853 (8,510 + 640 + 703). The density of households in this five-mile circular trade area is 125.402 households per square mile.
The annual cost of advertising twice a month in these two weekly newspapers would be $9,600. With this advertising expenditure, Fred estimates that penetration would decline to 62% and average shopping frequency would drop to 7.5 times. This would occur because of the larger size of the trade area and the inherent increased travel time for the typical customer. However, with the larger number of households to serve, he believes that such an advertising strategy would be profitable. Anne also believes that such an approach would be valuable but wonders if they shouldn't consider what would happen if average shopping frequency declined to 7.2 times.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (average shopping frequency = 7.5 or 7.2) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE ELEVEN-B
[pic]QUESTIONS
1. What is the financial impact of the proposed advertising strategy?
2. How might other promotional efforts be tied into this advertising strategy
3. The additional advertising is seeking customers from a further distance to make The House a destination for shopping. How could The House join with other stores on the town square to increase the penetration level?
4. What additional steps could The House take in its advertising in the weekly newspapers to increase the shopping frequency of the customers from the expanded trade area? OUseHH
PHASE FIVE
EXERCISE TWELVE-A
Evaluating the Impact of a Personal Shopping Service
Several years before Bill Henderson sold The House to Anne and Fred he started maintaining a database on regular customers. This database revealed that about 60% of the business is from a core of 900 households. Many of these households are two income households where both husband and wife work full time. These individuals are always pressed for time and put off shopping until the last moment, however, when they purchase, their average transaction size is $207. Fred and Anne both believe that these customers would be very supportive of a personal shopping service.
Fred and Anne believe that as part of their database they could record the birth dates, anniversaries, and other significant dates for all family members, relatives, and significant friends. They could then offer to customers a personal shopping service where gifts for friends could be purchased and the apparel needs of the household could be handled.
Fred and Anne are quite uncertain about the impact of this personal shopping service. They believe that it might increase total transactions by 1,000 to 1,600 per year and that the average transaction size for these transactions will be $85 (excluding delivery charges). To do a promotional mailing to the 900 target households with an enclosed questionnaire and participation form would cost $2,700. They anticipate sending out this questionnaire annually so customers can provide information to update the database. Furthermore, they plan to spend $7,000 annually promoting this new shopping service.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (1,000 or 1,600 personal shopping transactions) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE TWELVE-A
[pic]
QUESTIONS
1. Should The House institute a personal shopping service?
2. Do you have any suggestions on how the personal shopping service might become more profitable?
3. What impact might the personal shopping service have on the regular business of The House? Why?
4. Does The House incur any risk in implementing the personal shopping service?
PHASE FIVE
EXERCISE TWELVE-B
Evaluating the Impact of a Layaway Service
The House carries a number of high priced items such as men’s tuxedos and prom dresses. These items have a very high gross margin and Anne and Fred would like to increase the volume of their sales. Anne asked her sales clerks where the college and high school students get their formalwear for the homecoming dances and spring formals. Anne was told that when the dances occur most students cannot afford to buy their formalwear because of the other costs of the dances. They indicated that most students rent their formalwear from a rental store in Troy which they say is inconvenient and of questionable quality. Over dinner that evening Anne and Fred discussed what they might do to increase the sales of formalwear and meet the needs of the students. Anne thought that much of the problem was that all of the costs of the dances arose at the same time. She suggested that The House might reduce this constraint and convince the students to buy their formalwear instead of renting if The House implemented a layaway service. She also believed that other customers might utilize the service to spread payments of quality merchandise over time. Anne believed that such a service would increase the average item price by 1% and increase the gross margin percentage by 0.5%
Fred was skeptical of the viability of a layaway service. He pointed out that such a service would require an additional half-time clerk at a cost of $10,000 to keep track of the open layaway accounts. In addition, he pointed out that an increase in focus on formalwear would require an additional shopping trip to purchase the formalwear at a cost of $4,500 and a further investment in inventory of $25,000. Anne pointed out that this additional inventory could be financed out of cash and accounts payable and should not increase total assets.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the layaway scenario in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE TWELVE-B
[pic]
QUESTIONS
1. Should Fred and Anne follow through with the layaway service? Why?
2. How might a layaway service impact the other variables in the simulation model?
3. How would the use of a layaway service differentiate The House from other retailers of clothing? What benefits would The House accrue from such service?
4. What risks does The House incur if it decides to offer a layaway service?
uH
PHASE FIVE
EXERCISE THIRTEEN-A
The Impact of a Store Layout and Design Strategy
Anne just returned from attending a three-day executive development program on high performance retailing. One of the topics she was especially interested in was how to design a store's environment to enhance the store's image. She was considering a major remodeling of The House. Anne envisioned three major changes. First she felt that the orange carpeting was inconsistent with the store's image and usually conflicted with the apparel being displayed. Therefore, she was considering installing new flooring and freshly painting the walls and ceiling. The cost of the flooring and painting would be $14,000. Next, she wanted to install a C.D. sound system which consisted of a six-disc changer, amplifier, and four speakers in each of the four corners of the store. She also wanted to purchase 30 recordings of classical and big band music. The cost of this system would be $3800. Third, she wanted an entire new set of fixtures and tables upon which to display the merchandise. A local carpenter has given her an estimate to custom build these fixtures. The cost would be $28,000. These improvements would be expected to have a five-year life and, thus, the $45,800 written off over the next five years would be $9,160 per year.
When Anne told Fred of her planned changes he balked at the $45,800 in expenditures. He couldn't understand how such changes would have any influence on store performance. He was very clear that these changes would not bring more customers into the store. Anne conceded on this point but argued that the impact would be felt through a higher closure. Because people would feel more comfortable in the store and probably browse more, she felt that closure would rise. The instructor in the course she attended mentioned that a remodeling effort, if properly conducted, would increase sales by 20–30%. Nonetheless, Anne was very uncertain on what the impact on closure would be and thus her conservative estimate was that it might only increase to 65%, however, her optimistic estimate was that closure would rise to 70%.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (closure = 65% or 70%) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. HINT—Treat the amount of the remodeling as a permanent increase in assets even though these assets will be depreciated over five years. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE THIRTEEN-A
[pic]
QUESTIONS
1. What is the financial impact of the store layout and design strategy?
2. Do you see any ways that The House could reduce the risk of this store layout and design strategy? Please explain.
3. Why do even successful stores perform major changes in store layout and design every few years?
4. Why do store remodelings tend to increase sales? Does this sales increase tend to last or is the increase primarily short-term only?
PHASE FIVE
EXERCISE THIRTEEN-B
A Further Evaluation of a Store Layout and Design Strategy
Anne contacted Larry Jones, the instructor she had in the “High Performance Retailing” executive development seminar. Larry lived 150 miles north and agreed to drive down to visit Anne and Fred on Friday morning to hear about their plans (see exercise 13-A). He said he would like to visit with them for about an hour and also spend some time visiting other stores on the Town Square and driving around town. He hoped to accomplish all of this in 4-5 hours so he could leave town by 4 p.m. Anne and Fred agreed to pay him $2,400 for his services and billed this expense to the year end 2008 financial statements.
Larry was very impressed with the plans that Anne had developed. Based on his prior experience and the target market of The House, he estimated that closure would rise to 70%. He explained that they were correct in assuming that all of the benefit of the remodeling would occur once people had entered the store. However, he did believe, based on the large number of people visiting the Town Square to go to the U.S. Post Office, local government offices, and obtain other services, that if they could also remodel the exterior of the store that penetration could be expected to rise. Also he felt the dark brown wooden storefront projected an image that was inconsistent with the remodeled interior. He suggested that they use a red brick front, utilizing old or used brick, and that a new sign be installed to call attention to the store. With these additional changes he felt that not only would closure rise, but that penetration would easily rise to 67% or 68%. Larry also mentioned that if other businesses on the town square could remodel their store or building exteriors, that more people might find it pleasant to visit the Town Square.
After Larry left town, Anne called their landlord, Bill Henderson, who was also Anne’s father. She told him of her plans to remodel the store and suggested that if Fred and she do the interior remodeling, that Bill should pay for the exterior remodeling. Anne had obtained a cost estimate of $18,000 which she mentioned to her dad. After some negotiating, Bill Henderson and Anne decided to split the cost of the exterior remodeling. Like the interior remodeling, they decided to write this expenditure off over 60 months.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (penetration = 67% or 68%) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. HINT—Treat the amount of the exterior remodeling as a permanent increase in assets even though these assets will be depreciated over five years. Also be sure to include the interior remodeling into the simulation model (see exercise 13-A). Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE THIRTEEN-B
[pic]
QUESTIONS
1. Should the recommendations of Larry Jones be acted upon?
2. Are there any other things that could be done with The House to further enhance the store's atmosphere?
3. What will be the impact of The House’s exterior remodeling if the other stores on the town square do not upgrade their storefronts? What if they do upgrade their storefronts?
4. How does a store remodeling effort change the image of a store? What other changes need to be made to ensure the remodeling effort is as successful as possible?
PHASE FIVE
EXERCISE FOURTEEN-A
The Impact of a Change in Compensation Method
The House employs several clerks that work full- or part-time while either Anne or Fred are in the store. Neither Fred, Anne, nor the clerks attempt to do any selling. If someone needs help, they are there to assist but otherwise no type of selling occurs. Fred recently read an article in a trade publication that emphasized the role of order getters vs. order takers. Fred initially thought that given the low average item price in the store that any selling effort would not be worth the effort or would be ineffective. However, Anne pointed out that there were a number of higher priced items in the store and, in addition, a number of customers consistently make multiple purchases that total significantly more than the average sale of $59.44. Anne and Fred decided that they would like to provide extra service to those customers who made large purchases both because the higher-priced goods carry a higher gross margin and also to ensure these big spenders enjoy their shopping experience at The House.
To change the approach the clerks take towards customers, Anne and Fred purchased an online sales training program for all the clerks to take. This training will cost $3,000 every year. To provide an incentive to the clerks to provide personal service to these big spenders, Anne and Fred are considering providing a 6% sales commission to the clerks on all sales greater than $100. Anne and Fred believe about 20% of all sales (in dollars) are at this level.
Because of the increased attention that will be given to these sales Anne and Fred expect the average item price to increase by $0.20 causing the average transaction to increase by $1.00 to $60.44. Because of the improved salesmanship they expect closure to increase to 64% or possibly 66%.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (closure = 64% and 66%) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE FOURTEEN-A
[pic]
QUESTIONS
1. What is the financial impact of the change in compensation policy?
2. Might any other variables change with the change in compensation policy.
3. Are there any risks that are incurred by changing the compensation policy? Explain.
4. Will The House find it easier or harder to retain employees under the new policy? Explain.
PHASE FIVE
EXERCISE FOURTEEN-B
Evaluating a Salesforce Training Program
The House employs several clerks that work full- or part-time, while either Anne or Fred are in the store. Neither Fred, Anne, nor the clerks attempt to do any selling. If someone needs help, they are there to assist but otherwise no type of selling occurs. Fred recently read an article in a trade publication that emphasized the role of order getters vs. order takers. Fred initially thought that given the low average item price in the store that any selling effort would not be worth the effort or would be ineffective. However, one of the things the article emphasized was the role of suggestion selling. He thought this had some real possibility. For example, if a customer bought a shirt the clerk could suggest a tie or slacks. After Fred visited with Anne about his ideas she mentioned that one of the reasons that closure might be low (62%) was because of virtually no selling effort in the store. She argued that by being a bit more aggressive, but still polite and courteous, that the closure rate would rise.
Fred became aware of a retail personal selling seminar being offered in New York. This course was tied in with one of the major apparel shows and was a day in length and cost $595 per person. For an additional $150 each participant would receive a weekly newsletter with helpful selling tips. The course was taught by Martha Hernandez, who had 20 years of experience as an apparel buyer, salesperson, and business owner. Martha had launched a chain of 12 womenswear stores in the late 1970s, which she subsequently sold for $6.5 million.
Fred and Anne decided to both attend and to take their four full-time clerks. The total cost including travel, registration, lodging, and the one-year subscription for the weekly newsletter was $16,000. Based on the brochures promoting the program and some of his own analysis and projections, Bill set a goal of increasing closure to 68% and the average number of items purchased to 5.3. He also thought that a possibility existed of closure reaching 70%.
The spreadsheet you will need to run this simulation is attached. You will need to double-click on this spreadsheet which will bring up the Excel software. Please note that all rows and columns have been labeled, however, you need to enter the formula for each row item that occurs under the two scenarios (closure = 68% and 70%) in the simulation. If the formula or number does not change from the baseline, you can simply copy these formulas or numbers into the empty cells under the various scenarios. Be sure to save your work and print a copy once you are satisfied with its correctness. After you complete your simulation there are questions you need to answer. These can be answered by typing your responses below the questions, saving your work, printing a copy, and handing it in to the instructor if required.
EXERCISE FOURTEEN-B
[pic]
QUESTIONS
1. Should Fred and Anne follow through with the personal selling seminar as planned? Why?
2. Are there any other suggestions you have to improve the personal selling strategy of The House?
3. How would the use of personal selling differentiate The House from other retailers of clothing? What benefits would The House accrue from such service?
4. What incentives should The House offer to its associates if they are successful in the personal selling effort?uH
South-Western items and derived items copyright © 2005 by South-Western
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- message from the src chair california department of
- home department of general services
- research the cash management services available at three
- 2019 2020 bill 1222 text of previous version may 13 2020
- 2015 2016 bill 3983 chavis ame church south carolina
- alabama american forum
- 2017 2018 bill 3516 sc infrastructure and economic
- the house cengage