Answers to Computer-Based Exercises

ANSWERS TO COMPUTER-BASED EXERCISES IN RETAIL MANAGEMENT

OVERVIEW

Each of the components of Computer-Based Exercises in Retail Managementare intended to reinforce material; to allow your students to manipulate controllable retailing factors and to see their impact on costs, sales, and profits; to have your students understand better the impact of uncontrollable factors; and to have your students gain experience in using a EXCEL to assess retailing opportunities and solve retailing problems. All 10 exercises are designed to be handed in for class assignments or for the instructor's own use. They are balanced in terms of subject and level.

These are some features of Computer-Based Exercises in Retail Management:

  • They are linked to major concepts discussed throughout Retail Management: A Strategic Approach, 12E. For example, text page references are provided for each of the computer-based exercises.
  • The software operates using EXCEL for both MAC and IBM compatibles.
  • Students are encouraged to manipulate data and compare results attained under different assumptions. This provides them with “hands-on” experience.
  • Each component is “real-world” based.
  • All of the exercises are user-friendly. Directions are self-prompting. All exercises can be accessed from a main menu. Students can easily refer to previous screens or skip screens. No knowledge of computers, programming, or spreadsheet software is required. All screens can be printed.
  • A “learn by doing” strategy is used. Students can alter store image characteristics, questionnaire responses, markups, operating expenses, square footage, travel time, net profit ratios, asset turnover ratios, financial ratios, purchase commitments, and other factors, and see the effect on sales, profits, trading area size, cash flow, and so on.
  • The techniques in the exercises vary widely to maximize student interest and diversify the learning environment. Included are trading-area charts, cash flow statements, accounting methods, financial statements, open-to-buy reports, tabulation and cross-tabulation charts, profit-and-loss statements, and ratios.
  • The exercises can be used to reinforce concepts in the text or further expand student knowledge.
  • After setting up this software, the student's name and class section will appear at the top of every screen and on any pages that are printed from the screen.

TEACHING HINTS

You should be aware of a number of important concepts in planning to use Computer-Based Exercises in Retail Management with your classes:

  • You need to decide the role of Computer-Based Exercises in Retail Management in your course: For example,

--What part will the exercises have in a student's grade?

--What will be the basis for grades (exercise answers, computer printouts, profitability, and/or team presentations)?

--Will the computer assignments be started at the beginning of the course? …towards the end of the course?

--How many exercises will be used? Will all questions be assigned?

--Will the assignments be rotated each semester to prevent student copying?

--How will the discussion of class topics be integrated with Computer-Based Exercises in Retail Management.

  • You should clearly list all course requirements relating to the computer assignments in the course outline--including assigned exercises, due dates, and acceptable formats (e.g., typewritten, double-spaced).
  • You should make sure that each student places his/her name, class, and section on his/ her disk using the Reset Name/Class option that is available when opening this program and on the Main Menu screen. This information will then be contained on each output screen. The listing of name, class, and section on the disk prevents students from submitting assignments done by others.
  • You should consider making provisions with your college’s university’s ComputerCenter to put the exercises on the network for students who do not have their own computer or access to a computer.
  • You can allow students to do the first exercise on a trial basis. This enables them to determine your expectations without affecting their grades.
  • Encourage students to work out practice decisions prior to handing in assignments.

HOW TO USE THE MAIN MENU

When using the diskette, all exercises can be accessed from the MAIN MENU screen. The menu is arranged in the same order as the topics appear in the text and shows page references so that students may review concepts before (or while) doing an exercise:

COMPUTER-BASED EXERCISES IN RETAIL MANAGEMENT MAIN MENU

Text Page

Reference

PART 2:SITUATION ANALYSIS

1. Franchising 98

2. Direct Marketing136

PART 3:TARGETING CUSTOMERS AND GATHERING

INFORMATION

3. Attitudinal Survey213

PART 4:STORE LOCATION

4. Reilly’s Law237

5. Trading Area Saturation247

PART 5:MANAGING A RETAIL BUSINESS

6. Key Business Ratios310

7. Budgeting and Cash Flow314

PART 6:MERCHANDISING PLANNING AND PRICING

8. Retail Method of Accounting402

9. Open-to-Buy411

PART 8:PUTTING IT ALL TOGETHER

10. Sales Opportunity Grid516

HOW TO PRINT FROM THE EXERCISE DISKETTE

While using the exercises, the student may print any screen for his/her own reference or for the submission of a class assignment. The student simply turns on the printer Then he/she simultaneously points and clicks at the Print Screen box that appears at the bottom of each exercise screen. The screen appearing on the computer monitor will then automatically be printed--including the student's name, class, and section. Questions for each exercise can be printed by clicking the Print Screen box.

TROUBLESHOOTING DIRECTIONS-- FOR EXCEL SOFTWARE FOR PC

The macros function must be set to "Enable All Macros" in EXCEL for the Menu, Reset Name/Class, Instructions, Print Screen, Questions, Next and Exit options to properly function. To enable all macros, open the EXCEL program, and then press the "Office Button" at the top left hand corner of your screen. Select the "Excel Options," and then click on the "Trust Center" option. Select "Trust Center Settings," and then select "Macro Settings." Select the "Enable All Macros" option and then click "OK." Close EXCEL and then open Computer Based Exercises. The macros at the bottom of the page will now properly function.

This operation must only be performed the first time this program is used.

TROUBLESHOOTING DIRECTIONS-- FOR EXCEL SOFTWARE FOR MACs

Evert time this program is accessed on the MAC, you will get the following statement: This workbook contains macros. Do you want to disable macros before opening this file? Choose the "Enable Macros" option each time you open this file.

PLACING STUDENT NAMES YOUR NAME AND CLASS SECTION ON EACH PRINTOUT

You should make sure that each student places his/her name, class, and section on his/ her disk using the Reset Name/Class option that is available when opening this program and on the Main Menu screen. This information will then be contained on each output screen. The listing of name, class, and section on the disk prevents students from submitting assignments done by others.

THE EXERCISES

In the following sections, each exercise is answered. For every exercise, we also present objectives, a list of the relevant key terms and concepts from the text, an explanation of the exercise, and questions/assignments to be answered or completed.

PART 2: SITUATION ANALYSIS (EXERCISES 1-2)

EXERCISE 1: FRANCHISING

Objectives

1.To review the factors that a prospective franchisee considers when choosing which franchised outlet to operate

2.To weigh the relative merits of operating a franchise versus independent ownership of a retail business

3.To consider that different types of franchises have different profit margins and operating costs

4.To determine a franchise's profitability under different sales levels

5.To analyze how costs change with sales revenues

Key Terms and Concepts

independentproduct/trademark franchising

franchisingbusiness format franchising

constrained decision making

Explanation of Exercise

As a prospective franchisee, the student is considering the purchase of one of several franchised outlets: fast food (F), used computer (U), and an ice cream shoppe (I). After choosing the type of franchise by clicking the appropriate letter (F, U, or I,), this exercise allows the student to vary sales levels and other factors, and determine their effect on the franchise's performance. Each type of franchise has different default values for each factor.

The mouse or trackball are used to choose the factor to change. After the chosen factor is highlighted, the screen prompts for an acceptable value:

FactorRange of Acceptable Values

Sales $500,000-$3,000,000

Cost of Goods Sold 15%-75% of sales

Salaries $100,000-$500,000 (includes franchisee’s salary)

Royalty Expense 0%-8% of sales

Utilities $10,000-$300,000

Accounting and Legal Services $10,000-$100,000

Ongoing Advertising Fee 0%-4%

Loan $100,000-$1,500,000 (interest is at 12 %)

Insurance $10,000-$200,000

Investment $50,000-$1,000,000

Yearly Hours Worked 1,000-3,000

Sales can be changed in selected $10,000 increments by clicking on the left and right arrow keys. The adjustment increments for the other factors are as follows: cost of goods sold: 1 percent, salaries $20,000, royalty expense 1 of 1 percent, utilities $2,000, accounting and legal expenses $1,000, ongoing advertising fee of 1 percent, loan $10,000; insurance $2,000; investment $10,000, and yearly hours worked 50 hours. The exercise automatically computes gross profit, total operating expenses, net profit before tax, return on investment, and profit per hour.

The exercise is keyed to pages 98-101in the text.

Questions/Assignments

1.Compare the financial characteristics of each type of franchising (fast food, used computer and ice cream shoppe) using the default values in this exercise.

Comparative Profit-and-Loss Statements

Fast foodUsed ComputerIce Cream

Franchise Franchise Shoppe Franch.

Sales$ 1,500,000$ 700,000$ 500,000

Cost of goods 525,000 280,000 150,000

Gross profit$ 975,000$ 420,000 350,000

Less operating expenses:

Salaries$ 300,000$ 250,000$ 140,000

Royalty expense 60,000 14,000 35,000

Utilities 110,000 12,000 17,000

Accounting and legal 45,000 11,000 12,000

Ongoing advtg. fee 60,000 14,000 10,000

Interest cost 144,000 26,400 25,200

Insurance 160,000 32,000 24,000

Total oper.exp. 879,000 359,400 263,200

Net profit before tax 96,000 60,600 86,800

Return on investment 13.71% 33.66% 72.33%

Profit per hour$ 32.00$ 30.30$ 37.74

Total investment$ 700,000$ 180,000$ 120,000

Ongoing advtg. fee 4.0% 2.0% 2.5%

Total hours 3,000 2,000 2,300

Gross profit as % of sales 65.0 60.0 70.0

Oper. exp. as % of sales 58.6 51.3 47.2

Net profit as % of sales 6.4 8.7 17.4

The above figure compares the profit-and-loss statement for each type of franchise. Gross profit as a percent of sales, net profit as a percent of sales, and return on investment, and profit per hour are highest for the ice cream shoppe.

2.Compute the profitability of afranchised ice cream shoppe with the following factor inputs: sales = $700,000, cost of goods sold = 20%, salaries = $200,000, royalty expense = 8% of sales, utilities = $30,000, accounting and legal services = $30,000, ongoing advertising fee = 4% of sales, loan = $400,000, insurance = $24,000, investment = $150,000, and yearly hours worked = 2,000. How would relative profitability change if sales were increased to $1,000,000? Explain your answer.

At the $700,000 sales level the ice cream shoppe would have a gross profit of $560,000, total operating expenses of $416,000, a net profit before tax of $144,000, a return on investment of 96.00%, and a profit per hour in addition to salary of $72.00.

At the $1,000,000 sales level the ice cream shoppe would have a gross profit of $800,000, total operating expenses of $452,000, a net profit before tax of $348,000, a return on investment of 232.0%, and a profit per hour in addition to salary of $174.00.

3.An independent ice cream store that has been established for twenty years can be purchased for $300,000 (with one-third down and two-thirds via a loan at 12 percent annual interest). This store is similar to the one in question 2 in every regard (but with $600,000 in annual sales), except that there are no royalty and advertising fees. Instead of a royalty fee, you would have to pay monthly rent of $7,500. Use the computer program and your own calculations to determine the profitability of buying the independent store. Contrast the results with the franchised outlet in question 2 having $700,000 in sales. Which store would you select? Why?

Comparative Profit-and-Loss Statements

Ice CreamIce Cream

Franchise Independent

Sales$ 700,000$ 600,000

Cost of goods 140,000 120,000

Gross profit$ 560,000$ 480,000

Less operating expenses:

Salaries$ 200,000$ 200,000

Royalty expense 56,000 --

Rent -- 90,000

Utilities 30,000 30,000

Accounting and legal 30,000 30,000

Ongoing advtg. fee 28,000 --

Interest cost 48,000 24,000

Insurance 24,000 24,000

Total oper.exp.$ 416,000$ 398,000

Net profit before tax$ 144,000$ 82,000

Return on investment 96.0% 82.0%

Profit per hour$ 72.00$ 41.00

Gross profit as % of sales 80.0 80.0

Oper. exp. as % of sales 59.4 66.3

Net profit as % of sales 20.5 13.6

The ice cream franchise shows much higher profitability due to larger sales as well as lower operating expenses as a percentage of sales.

In addition, the actual additional profitability of the independent may be somewhat overstated:

  • There was no advertising allowance budgeted for the independent store in this example.
  • It is probable that a franchised ice cream shoppe would have higher sales than a comparable independent shop at the same location.
  • The number of hours worked per year may be lower at the franchised shop due to centralized controls, standardized operating procedures, and franchisor assistance.
  • The franchised shoppe could have a lower cost of capital due to preferential arrangements with leading lendors.
  • Accounting and legal services could be less for the franchised unit due to the use of a standardized accounting system.

An excellent answer should reflect these points.

4. In deciding what retail business to purchase, what other factors would you consider besides those noted in the exercise? Explain your answer.

The student should examine his or her personal abilities, financial resources, the time demands of each business, the competitive environment of each business, the costs of purchasing a business versus establishing a business from scratch, whether an existing owner will stay on during a transition period (to train the new owner and to introduce the new owner to customers and vendors), the freshness of inventory, a franchisee's overall success rate, a franchisee's training program quality, the quality of a franchisee's overall assistance, and so on.

EXERCISE 2: DIRECT MARKETING

Objectives

1. To evaluate the use of the Web by an independent retailer

2. To study the different types of costs associated with developing and maintaining a web site

3. To determine the association between site development and maintenance and sales revenues

Key Terms and Concepts

direct marketingWorld Wide Web (Web)

Internet

Explanation of Exercise

As the proprietor of ABC PHOTO, an independent camera shop, the student can vary two important budget components relating to the firm’s Web site: initial site development costs and annual site maintenance and upgrading costs. The proprietor seeks to maximize profits from overall Web sales.

The mouse or trackball can be to select the marketing variable (planned sales, level of initial site development costs, and annual maintenance and upgrading costs to be changed. The planned sales can vary in $10,000 increments between $100,000 to $1,000,000 by clicking on the left and right arrow keys. There are three levels (low, medium, and high) for both initial site development and annual site maintenance and upgrading costs. The computer automatically calculates savings in direct mail expenses and the planned profit from the Web sales.

These are the default values for each level of expense:

Initial Site Development (A One-Time Cost)

LevelCostFeatures

Low:$ 8,000Low-level graphics

Medium:$12,000Medium-level graphics + E-mail capability

High:$20,000High-level graphics+ E-mail capability+ auto order tally + automatic order confirmation

Annual Site Maintenance and Upgrading (Annual Cost Including First Year)

LevelCostFeatures

Low:$ 4,000Update prices and product availability

Medium:$ 8,000Low level + replace server every two years

High:$10,000Medium level + upgrade graphics + add sound

This exercise assumes that the Web-based sales will have a 20 percent profit margin. In addition, web-based sales will reduce direct mail expenses (by 2 percent of total web-related sales).

The exercise is keyed to pages 136-143 in the text.

Questions/Assignments

1.Calculate the planned profit from Web sales based on $780,000 in additional sales over a five year period if the firm has high initial site development costs and high annual and upgrading costs.

Planned Sales Level$780,000

Less: Cost of Goods Sold (80 percent of sales) 624,000

Equals: Gross Profit 156,000

Less: Initial Site Development Costs$20,000

Less: Annual Maint. And Upgrading Costs 50,000

Plus: Savings in Direct Mail Expenses 15,600

Equals: Planned Additional Costs 54,400

Equals: Planned Profit from Web Sales$101,600

2.Calculate the planned profit from Web sales based on $500,000 in additional sales over a five-year period if the firm has high initial site development costs and high annual and upgrading costs.

Planned Sales Level$500,000

Less: Cost of Goods Sold (80 percent of sales) 400,000

Equals: Gross Profit 100,000

Less: Initial Site Development Costs$20,000

Less: Annual Maint. And Upgrading Costs 50,000

Plus: Savings in Direct mail Expenses 10,000

Equals: Planned Additional Costs 60,000

Equals: Planned Profit from Web Sales$ 40,000

3.Discuss the advantages of ABC Photo’s maintaining a site on the Web.

Among the advantages of ABC Photo’s maintaining a site on the Web are:

  • Access to an additional base of consumers (including international customers)
  • Ability to provide information to prospects
  • Access to time-pressed shoppers 24 hours per day, 365 days per week
  • Furnishing customer service in terms of hot links to vendor sites
  • Ability to use multimedia in sales presentations
  • Inability of customers to differentiate between a small and a large retailer based on web site
  • Ability to communicate with customers via E-mail

4.Discuss the disadvantages of ABC Photo’s maintaining a site on the Web.

Among the disadvantages of ABC Photo’s having a site on the Web are:

  • The need to constantly update web site data in terms of product availability, prices, and hot link addresses
  • The need to communicate ABC’s web site address to prospects
  • Concern among many web site shoppers with credit card security
  • Inability of the web to reach consumers who do not have access to a computer with a modem and an access provider.
  • Uneven demographics and life-styles (upscale, male, and computer literate) which may not match that of ABC’s target market.
  • Ready access to discounters on the web may promote price competition for ABC.

PART 3: TARGETING CUSTOMERS AND GATHERING INFORMATION (EXERCISE 3)