Pleasance House Consulting - Projects - Klick Photopoint

The Client Organisation

Bowie Castlebank trades under the Klick Photopoint, Max Spielmann and Supasnaps photo-processing fascias, and runs the William Munro dry cleaning operation in Scotland. In addition to these retail operations, there is a large mail order business and development and processing work is performed for other companies that do not have the capacity themselves. The company has interests in many more properties than they actually trade in themselves. There are a number of freehold properties let out and many sub-lets.

When this project started in May 2003 Bowie Castlebank had over 650 retail outlets offering photo-processing services and widely variable ranges of associated retail products. Over 100 shops also offered dry cleaning services and a small range of associated products. Dry cleaning work was undertaken in one of three factories in central Scotland and returned to shops in specialist vans owned and operated by Bowie Castlebank. Photo-processing work was similarly picked up from shops and taken to one of three central laboratories in Wishaw, Leeds and Birmingham. Every shop had at least one pick-up/delivery per day and most had two. There were only a few shops with local photo-processing facilities and some of these were purely analogue. Retail products were 100% centrally distributed from a new warehouse near Chester. Automated replenishment ran on the basis of “every shop, every product, every day”. Stock was picked (in single units) and packaged in the central warehouse and trunked overnight by an external carrier to each of the three labs. It was then distributed to the shops in the same vehicles carrying the developed photographs and dry cleaning. The same supply chain was also used to distribute many consumables used by the shops, particularly envelopes used to ship photo-processing work, cleaning materials, small fixtures and fittings and POS material. The Klick and Max Spielmann brands carried their own label films and had different POS materials.

Shops had Anker Powertills (usually just one) connected to the central systems by dial-up. Large Oracle production systems ran locally in each of the laboratories. The Island Pacific merchandising system ran on an AS400 in Leeds. Other systems like financials, payroll etc. were based in head office in Glasgow and management reporting was based on a suite of Excel spreadsheets that produced printed reports after being loaded with data extracts from the other systems. This was a time-consuming, largely manual process.

The main business emphasis was on the provision of services rather than the sale of retail products and the change in technology from analogue to digital photography (“a revolution, not an evolution”) was having a huge impact on the business. The following major changes occurred during the two and a half years of the total project. The systems had to be flexible enough to cope with these transformations (often at very short notice) and guarantee continuity of functionality.

· Digital Revolution

o Digital minilabs and digi-pods were installed in almost all shops, reducing the space for retail product

o The range of development services on offer increased dramatically particularly the higher-priced, more immediate, local production of prints

o Sales of film (and the demand for conventional services) reduced sharply

o Camera ranges changed dramatically

o Digital accessories (media cards etc.) became a significant part of the business

· Trading Estate

o Normal organic openings and closures averaged about 3 per month

o 75 shops were acquired from SupaSnaps in a single deal and were set up with the same range of merchandise and systems as Klick shops

o The estate was reduced to about 430 shops by closing a couple of hundred in one week in early 2005

o A second batch of closures in late 2005 saw the estate further reduced to about 340 outlets

· Buying and Merchandising

o A complete new department was staffed up in Glasgow, replacing people who had been based in Chester and Leeds

o Ranges of digital cameras, own label media cards and gifts were introduced

o A total range review was undertaken, resulting in unusually high numbers of changes in established categories like albums and frames

o A supplier manual was produced and issued, causing goods in and quality control procedures to be enhanced

o All retail product was packaged and moved around in meaningful pack sizes

o Ticketing and labelling of all products, including promotional items, was standardised throughout the company

o Shop-departments were graded based on space available rather than on turnover

o Ranges were controlled centrally based on these gradings

o Display instructions were issued for the resultant product ranges

o Replenishment schedules were completely reworked twice to better balance workloads throughout the supply chain

· Operational Changes

o Two of the dry cleaning facilities were closed, leaving only the main centre in Wishaw

o The Leeds and Birmingham laboratories have been closed down, leaving only Wishaw operational

o Transport was sold off and replaced with a third party operation on a “once every two days” schedule

· Management Changes

o There was one major restructuring of board-level responsibilities during the project

o There were four different heads of retail operations

o The shop operations hierarchy was cut down from three-tier to two-tier

o There were at least two major reshuffles within the new structure

o There were three different IT managers during the project

· Reporting Changes

o The financial calendar ran from April to April with 4-4-5 weeks per period in each quarter, 4-4-6 in Q4 when a 53-week year was required – and one was well overdue, so the calendar was changed in the first full year of the project

Project Summary

Allan Wilkie was introduced to the account by the head merchandiser who had been recruited from Texstyle World to set up a completely new merchandising function in Glasgow. The biggest problem was with the quality of management reporting. This project therefore started with the implementation of the PHC Management Information System – supplied on a fixed price basis.

After the initial four-month implementation of the PHC MIS, work began on the second phase to replace the central merchandising system. This was subject to severe timescale constraints as the new system had to be implemented by a fixed date to prevent having to re-license the legacy system. Allan Wilkie project managed the implementation, putting together a well-qualified team from his contacts in the retail systems industry. PHC involvement continued until the end of 2005 when both new systems were well bedded in and Bowie Castlebank staff were self-sufficient in terms of being able to run the systems on a day-to-day basis and adapt them to cater for changes in their business.

Management Information System

After completing the sale and convincing the technical team of the suitability of the Cognos platform, the first component of this phase was to agree a completely new reporting hierarchy for the broad range of products, services and consumables. This had to be used concurrently with the old structures (which were rooted in the POS) until the start of the next financial year, so the standard PHC MIS database design was extended to incorporate a second product hierarchy. The same system was therefore able to meet the new needs of the merchandising department and replace the legacy reporting mechanisms immediately.

A range of multi-dimensional cubes were implemented to provide the necessary levels of reporting to different sets of users. Operations used only daily sales by shop-department, reporting on performance in the current week compared to the previous week and the same week last year. The central merchandising function had a complete view of stock movements at SKU-week level going back three complete years and as far forward as sales and stock plans existed.

Previous Weeks	Current Week	Future Weeks
· Sales o Sales o Deposits o Returns/Refunds	· WTD Sales o Sales o Deposits o Returns/Refunds
· Intake o Deliveries into WH o Direct deliveries to shops/labs o Returns to supplier	· WTD Intake o Deliveries into WH o Direct deliveries to shops/labs o Returns to supplier
	· “Forward” Commitment o Outstanding POs o Outstanding Call-offs	· Forward Commitment o Purchase orders o Call offs o Contracts (averaged by week)
· Internal Movements o Warehouse deliveries to shops/labs o Returns to warehouse o Shop transfers in o Shop transfers out	· WTD Internal Movements o Warehouse deliveries to shops/labs o Returns to warehouse o Shop transfers in o Shop transfers out
· Adjustments o Perpetual inventory adjustments o Stock write-offs	· WTD Adjustments o Perpetual inventory adjustments o Stock write-offs
· Closing Stock o Full price stock o Reduced price stock
	· Current Stock (this morning) o Current stock o In transit § WH to shop § Shop transfers in § Shop transfers out
· Replenishment o Ideal stock (at EOW)	· Replenishment o Ideal stock (today)
· Plan (Original, closing) o Sales o Stock	· Plan (Original, Closing) o Sales o Stock	· Plan (Closing) o Sales o Stock
· Forecast (Adjusted, closing) o Sales o Stock	· Forecast (Adjusted, closing) o Sales o Stock	· Forecast (Adjusted, closing) o Sales o Stock

Each of these movement types had the fundamental measures of unit count, cost and retail value plus the calculated measures of average cost, average retail, sales ex VAT, gross profit (£) and gross margin % at location-SKU-week level. Every one of these measures was correctly summed/averaged/calculated up the reporting hierarchies in every dimension. Additionally, on the product dimension only, the merchandising cubes held the following details of price and sales history by SKU:

· Original retail price

· Start date of original price

· Date of first sale

· Quantity sold at original price

· % sold at original price

· Previous retail

· Start date of previous retail

· Quantity sold at previous price

· % sold at previous price

· Current retail

· Start date of current retail

· Current cost per unit

· Quantity sold at current price

· % sold at current price

Even more spectacularly, by product by week the merchandising cubes held a count of trading shops that carried stock and a count of those that actually made sales.

Outside of the main Cognos reporting structures, but still within the PHC MIS database, additional daily reports were created to alert shop operations to:

· anomalies in shop trading patterns

o polling failures

o shops not trading when expected

o shops opening later than expected

o first recorded sales significantly after opening time

o last recorded sales significantly before closing time

o shops closing earlier than expected

· returns/refunds of items not sold recently

Subsequent to the initial implementation, Allan Wilkie managed several major changes to the MIS:

· The main data feeds were switched from the legacy systems to the new merchandising system

· Additional feeds were added as they were deemed necessary or became available for the first time

· All reporting based on the old product hierarchy was updated

· Several major restructurings of the corporate hierarchy were dealt with

· Weekly updates from a spreadsheet planning system were added

· Reporting was radically enhanced in terms of both report content and delivery methods

Merchandising System

Pleasance House Consulting assisted with the entire purchasing process, from identifying potential suppliers, organising demonstrations, validating that required functionality existed (or could be provided) right through to contract negotiation. The system selected was Supply Chain from Retail Business Solutions. The deal excluded the MicroStrategy-based data warehouse as the PHC MIS was already in place.

The implementation included the following main functional features:

· Product maintenance (and retail pricing) of all goods, services and consumables

· Contract/call-off purchasing in addition to normal purchase ordering

· Merchandising based on shop grades by department

· Central warehousing

· Automated replenishment of shops from warehouse

· Automated replenishment of warehouse from suppliers

· Perpetual inventory in all stock-holding locations

· Stock ledger

· POS interfaces (both directions)

· Financial systems interface (to Sun accounts)

· Automated feeds to the PHC MIS

The elements of the project that had the most significant impact on the implementation (and how they were dealt with) are listed below:

1. The legacy merchandising system (Island Pacific) had been acquired as part of the take-over of another company several years before. It was based on an IBM AS400 located in Leeds – as were the people who used it. Initially the distribution had been done from Leeds but the system had been modified to handle the much greater number of shops run by Klick and to perform the warehousing in Chester. The communications links and internal data structures were quirky. The Leeds people were leaving, there was no suitably qualified resource in Glasgow to maintain it satisfactorily and the new users (the merchandising team in Glasgow and the warehouse team in Chester) were struggling. There was therefore severe pressure to implement the replacement system before the next annual license fee had to be paid.
Allan Wilkie had to teach himself how to extract the relevant data from the AS400 in a hurry. Programs were written to reload the extracted data into Supply Chain by complete subsystem e.g. Purchase Ordering, so that total take-on could be well tested and then run very quickly to ensure an immediate change-over.

2. The corporate hierarchy could not be maintained sensibly using the standard on-line transaction processing offered in Supply Chain. There was far too much volatility.
The initial take-on process was enhanced to be run as required, rebuilding the entire shop management structure from a spreadsheet maintained by the accounts department. This process also fed these changes straight through to the PHC MIS.

3. Shop numbers were 4-digit codes in the financial systems, but the POS estate could only handle 3-digit codes and the legacy merchandising system used a mixture of both – and its own internal references.
Supply Chain was configured to use the new 4-digit standard, but also to make access possible by the old codes. Data take-on from Island Pacific used its internal references and the interfaces to/from POS and Sun Accounts used the appropriate 3 or 4-digit codes.

4. Supplier codes in Island Pacific were not the same as had been implemented in Sun Accounts. This made the work of the accounts payable function extremely difficult as invoice matching had to be a completely manual process.
Supply Chain was implemented using the Sun Accounts supplier codes – with the Island Pacific codes cross-referenced to allow access. Accounts Payable was given spreadsheet queries against the Supply Chain database to assist its invoice matching and reporting processes.

5. In Island Pacific each SKU had a single product record. There was no easy way to accumulate data for all the colours and sizes of a given style of frame for instance. It was virtually impossible to analyse sales data by key product features within a department.
The variant coding structures in Supply Chain were used extensively to correct this. Different departments used different coding structures - batteries were classified as battery size and quantity in pack, film was broken down by shutter speed and number of exposures, media cards by type and memory size, frames and albums were by colour and an appropriate set of sizes. These structures were fed through to the MIS so all new corporate reporting could use them.

6. Bowie Castlebank historically used short numeric codes for all products, using the smallest numbers for services that were keyed into tills instead of physical products that were bar-coded. Codes for other products and consumables were meant to be maintained in larger fixed ranges, but these had fallen into disrepair despite a facility on the corporate intranet that allowed people to check which codes were available. The new merchandiser wanted to introduce a new coding structure for retail product, emphasising the new merchandise hierarchy and variant coding and tying in with product labelling and ticketing instructions issued to suppliers.
Supply chain was tailored to auto-generate new structured product codes by department for physical merchandise. Data was cleansed during take-on and users could distinguish between services, consumables, old discontinued products, products generated in the old system but still in the business, and new products simply by their codes. Staff who maintained services were trained to configure product maintenance so they could input the new codes they wanted, having checked current ranges/availability using standard search facilities provided by Supply Chain and its SQL Server platform. The intranet facility was therefore made redundant. New PLU files were downloaded to all shops when the new codes became active, Supply Chain having supported the old codes till then.

7. The sales history in the legacy systems was error-prone. The main reasons for this were that all product codes existed in all tills, the search facilities were poor and there was no sales audit function.
The Supply Chain download subsystem was implemented with controls to send tailored lists of PLU records to the shops. Shops with no minilabs and no dry cleaning did not have these records in their files. Old discontinued and cleared products were deleted, as were thousands of unnecessary consumables. Incorrect sales history was corrected.

8. The retail pricing regime was very primitive. The bespoke download could only handle variable prices for the two brands Max Spielmann and Klick Photopoint.
Retail pricing was radically enhanced so that local/regional price lists could be used to maximise profits where the market could stand it. Price point tables and a fixed set of price change reason codes and types were introduced, reducing errors and inconsistencies and allowing analysis of new stock items versus reduced price items. Where the price differed from what the supplier had labelled originally, shops were given appropriate re-ticketing instructions on their delivery notes from the central warehouse.

9. The true forward commitment was not visible because contracted-for purchase quantities that were not yet called-off were not held in the existing systems.
Contract/call-off purchasing was implemented and interfaced to the MIS. In addition, supplier availability schedules were set up, making it impossible to call-off contract quantities earlier than they could be supplied.

10. The merchandising department wanted to grade shops based on the amount of display space dedicated to each department and the package only dealt with total size when re-grading.
Internal shop fittings were defined by department and a couple of bespoke processes were generated to run before and after package programs to set the “shop” sizes correctly for the department being re-graded.

11. Customer special orders (where a local golf club might purchase 100 frames to put handicap certificates in) could cause problems in the replenishment system.
Customer special orders were input by the merchandising function as they could check stock levels. They also put the basic order into a spreadsheet so that a permanent record could be maintained. A single program was specified to run before the main package replenishment run each morning, updating both the spreadsheet (with dates of delivery and sale) and the replenishment system (temporarily recalculating correct replenishment levels excluding the special orders).

12. Deliveries of stock from warehouse to shop were made on exactly the same demand-driven basis as the return of centrally-processed development work from the laboratories to the shops. This ‘every shop – every day’ strategy made it impossible to balance daily workloads in the warehousing and transport functions.
Stock replenishment schedules were revised so that standard stock deliveries were on a balanced schedule. This was managed using cyclical plans set up in Supply Chain. In addition to the management of the replenishment and stock allocation cycles, consignment building plans were used to group shop orders by laboratory and van route, providing useful control documentation on total sizes of distributions.

13. Major shop stock counts were supplied by an external agency. These had to find their way into the Supply Chain stock counting module.
A suite of programs were developed to validate and load external counts. This included a routine to deduct stock in transit (at the time of the count) from the original stock snapshot.

14. The Bowie Castlebank stock department were used to working directly on the stock ledger. The Supply Chain stock ledger had no front end to supply this user interface so a complete set of spreadsheet queries were developed to provide full functionality in this area.

	Ý
	Top
Ü Back		Next Þ
	Pleasance House Consulting
	Home