Archimedes Training – Sample PBL Solved Problem

Archimedes Training – Sample PBL Solved Problem



Company X has problems with cash flow.  Some months there is a shortfall and they find it difficult to pay suppliers, other months there is no problem and there are sufficient funds to pay suppliers and other debts. It is becoming more difficult to receive overdrafts from the banks due to new financial rules.  In the past 12 months our overdraft has reduced by 20%.  In the past year they have had to rely on overdrafts for 8 out of the 12 months. As a result of the problem based learning process we would like to:

  1.        Reduce our dependence on overdraft facilities from 8 months to 2 months


Step 1: Clarify Terms
  • Cash flow – this is the money flowing into and going out of the business. It depends on income and expenditure. When a deficit occurs there is more money going out (expenditure) than coming in (income)
  • Income – our income comes from business and domestic customers
  • Expenditure - Our main costs are rent, light, heat and we have a large amount of staff costs, the main staff costs are sales representatives. The main non staff costs are stock.


Step 2: Define Problem

The problem could be occurring for one of five reasons (or a combination of these)

  • Sales – our sales from the domestic market are only 20%  of our business. Our competitors receive 40% of their sales from the domestic market.  Therefore there is a potential to attract more domestic customers boosting our overall sales. Domestic customers do not require credit and so would bring in immediate cash to the business
  • Carrying excess stock when the demand is not there in a particular period – each month we carry the same stock despite the fact that sales can vary from month to month. This is because it is difficult to estimate sales as they can vary.  Some months we have  a short fall and have to order stock from the warehouse because these are in small batches we cannot avail of discounting.
  • Credit terms operated for our business customers – we offer our business customers a 35 day credit. This is why we have such a high volume of business customers compared to our competitors.  However we pay our suppliers every 30 days.
  •  Excess costs in certain parts of the business (perhaps sales) – due to the high volume of business customers we have a large number of sales representatives to allow us to foster a close relationship with our customers.  This is an extra cost
  • Poor sales forecasting which leads to excess stock


Steps 3 and 4: Brainstorm solutions and review results

Proposed solutions

  • Identify new approaches to forecasting sales to reduce excess stock
  • Collaborate with customers to determine potential orders
  •  Reduce costs (implement a sales management system to allow customers to self order and reduce sales staff)
  • Increase the number of domestic customers which do not require credit
  • Apply to the bank for an overdraft
  • Reduce credit offered to business customers


Review of results

The main reason for the company’s success in attracting business customers are the close relationships between sales representatives and their customers and the credit rate offered to the business customers thus the solution to reduce credit terms and sales staff is not feasible.
The bank cannot increase its credit terms more than it has and this avenue has already been pursued.
Increasing domestic customers takes a significant amount of marketing the company has no marketing expertise and marketing consultants can be extremely expensive.
The company has an excellent relationship with business customers and so collaborating with these customers to forecast potential orders is a feasible solution and should be pursued.

Step 5: Learning objectives
  • Identify approaches to forecasting  both from other companies ( our customers use it) and textbooks/internet
  • Identify the forecasting approaches the company uses
  • Identify what data is required to forecast orders
  • Determine what kind of sales data our customers have
  • Determine if our customers will share this data
  • Examine the tools used for forecasting, how expensive they are and how they can be used.


Step 6: Self study

Learning objective one and two

Forecasting used in other companies
I spoke to three customers to find out what forecasting approaches they use. One customer does not use any.
A second customer uses an excel spreadsheet to track the forecasted sales and actual sales for the period. Where there is a shortfall the customer adjusts the forecasted sales for the following month. The company does not find it reliable
A third company tracks its forecasted sales and actual sales over 12 months. They noticed that in some months there is more of a demand for the product than in others so they used a seasonal indices and find it is more accurate than the previous approach.

Forecasting approches our company uses
I spoke to the procurement team and they said that they use the previous months order to make the next order. This is naive forecasting.
Forecasting approaches from textbook/internet
From my research forecasting is based on statistics there are some very mathematical resources but others that explain them in a simplified format.  I found that there are four main forecasting methods:

  • Naive – basically where you use the previous months sales to make an order for the next month
  • Weighted average moving – where you weight the most recent sales higher than earlier sales and get an average for these
  • Exponential smoothing – where a smoothing factor is applied to most recent sales to make a more accurate estimation. It is useful where changes in sales are not random but dependant on factors such as seasons
  • Seasonal indices – an index is calculated for each month and applied when calcluating the sales for future business periods


In addition there is a number of methods of measuring the accuracy of these forecasts.  This will allow us to evaluate how accurate the forecast is. The main methods are mean average percentage error and mean absolute deviation.
Learning objectives three, four and five
What data is required
The only data that seems to be required is forecasted demand and actual demand for a 12 month period to increase the accuracy of the forecast. Other information can be derived from formula.
What sales data do our customers have and will they share it (how can it be shared)
All customers have data on their sales. Most customers have a database of the orders received each month, customer data of who made the order, number of sales returns and the reason they returned these. Three customers have given us a sample of their records and this data can be used. (see attached)
Sharing of data
The companies cannot share the data from their own customers due to data protection laws, however they are prepared to share the number of our products they sell every month over the past year. However it is not in excel format but can

  • Be entered manually by a member of the PBL team. This will be time consuming.
  • Some companies have said that they can export the information from their database to a pdf file.


 Learning objective 6

Examine the tools used for forecasting, how expensive they are and how they can be used.
Excel can be used as a simple tool for forecasting. It is cheap and easy to use.  This is what customer 3 uses.  The Analysis tool pack in excel contains functions for exponential smoothing and moving average. A number of example templates are available online
Exponential smoothing -
Seasonal indices -

Step 7: Synthesis

As forecasting requires very little data and once the data is entered the formula can be applied to several periods.  Initially two types of forecasting will be used exponential smoothing and seasonal indices. We will measure the forecast using MAPE (as it is the easier of the two to calculate).

Sales and order (from procurement) data for the past year will be entered into an excel spreadsheet and will be used to calculate the forecast for the next period.

Also the number of products sold by each of our customers will gathered from the relevant customers to get a picture of how much of each product was sold over the 12 month period and we can derive seasonal indices.  Exponential smoothing and seasonal indices will be used again to predict how much each customer will order and we will speak to our customers to determine if this is accurate or not. Sales people will be responsible for contacting customers a month in advance to determine if they expect the forecast to be accurate or not.

The MAPE will be measured for each forecasting approach and the one with the lowest MAPE will be chosen for the primary forecasting method of the company.


Step 8: Reflection

What I learned specific to the problem

  • The different forecasting methods
  • What data is required for forecasting
  • How to calculate a forecast using these methods
  • How to use excel to implement these forecasting methods

What I learned which was not specific to the problem

  • How to collaborate with customers
  • How to find information to help me solve a problem
  • How to apply this information to a business problem

Rationale for decisions made
I chose two methods of forecasting as once the data is gathered and input into excel the formual are fairly easy to apply. Also the accuracy of the forecasting techniques can vary depending on the company. As we are not sure if the change in sales is due to seasons or other factors I decided to use two approaches and measure the accuracy of the most appropriate one.
I chose excel as there is no cost associated with it as we have it here in our company. Also there are a lot of templates and guides in how to use this software for forecasting. There would be no benefit in purchasing specific software for this function.
I decided to include collaboration with the customers in addition to the quantitative methods of forecasting as there may be other factors that quantitative data may not be able to capture which may influence the forecast.

What I would do differently
If I was to do this process again. I think I would approach the customers differently. I only managed to get a response from three customers. I think I would perhaps accompany one of the sales people as they have more experience in dealing with the customers. I would have all of my information gathered before I would speak to anyone externally so I appear more organised and professional.


Step 9: Reapplication

I found a number of self-study resources that provided a lot of learning material that could be applied in other areas. For example, the SME toolkit contained a lot of resources. For example I found some resources on low cost market research that might enable us to increase our domestic customers fairly easily. Perhaps this could be the next PBL project!