[SOLVED] Alan Chadwick is the Chairman and Managing

Alan Chadwick is the Chairman and Managing Director of Chadwick’s Department Store Ltd. This is the company that operates Chadwick’s, a large independent department store that has been family owned and run for over 100 years. It was started in 1906 by Sir Geoffrey Chadwick, great grandfather of Alan. Recently, sales in the store have been rather disappointing, which has put a strain on company finances. Alan has therefore asked your firm of business consultants for some business planning advice. Towards the end of January, you visit Alan at his office on the top floor of the department store building in order to learn about the business and try to understand his requirements. After the opening pleasantries have been exchanged, this is what he tells you:
‘Retail is a difficult business, you know. There’s so many details you have to get right – the right type of stock, the right colours and styles, the right prices, the right amount of stock, decent standards of service, the right environment and so on. Everything matters. We’ve been struggling a bit recently, and I think we need to be a bit more scientific about our planning and decision making,’ he begins. ‘I think if you could produce some sort of computer model of our business it would help a lot. We have a few ideas that we are considering for the business, and I think a model could help us test which ones might be worth doing.
So, I guess you’ll need to know all about us. Well, as you see, our store is a large, old five-storey building. We trade from four floors – here on the top floor we do all the buying, admin, accounts and so forth. The selling area of the four floors totals roughly 155,000 square feet. We’re in a reasonable location in the city, although it could be better. There’s not much we can do about that now. Anyway, it sounds a bit confusing, but we have three very different types of income in the store:’
Concessions
‘The second type of sales is the "shops within shops" inside the store, called "concessions". We have quite a good range of different outlets trading from our premises. We do not own the merchandise, and the staff are not employed by us. We just get a cut of the sales they make. The concession agreements vary, but on average we get about 16% of the sales revenue (before any credit card commissions) on these sales.
The way it works is that we bank all the money, basically because all the tills belong to us. After a short time, we forward their 84% share of the sales revenue to the various concession companies. Concession sales in the year just ending were some £11.479m. I would expect them to reach £12m for this coming year.' ‘Concessions occupy about 65,000 square feet in the store,’ Alan adds.
Restaurant
‘The final source of sales is a large café and restaurant on the third floor, which is pretty popular. Revenue generated this year was some £1.145m, and I would hope for at least £1.2m in the year ahead. The average margin we make on food and drink is pretty steady at about 31% each month.’ Receipts from Sales
‘The good thing about retail is, of course, you get the cash in straight away. Credit and debit card payments account for about half of total sales in the store. These companies charge an extortionate commission, of on average 2% on every transaction. So, for every £100 we sell, we only see £98 in our bank the next day. Only 10% of total sales are made via our own store card. We don’t run the store card – it’s administered externally – but the commission we suffer is only 1.25%. We’d like more people to sign up for the store card.
As I mentioned earlier, we bear the full cost of these card commissions when it comes to concessions sales, which is a pain. Anyway, the remaining 40% of sales are made with traditional cash and cheques.’
Stock-holding Policy
‘On average, at any point in time, we try to keep own-bought stock equivalent to the own-bought sales for the next two months. We call this "two months forward cover". If we held only 1.5 months cover, I think own-bought sales would fall by about 5% due to the reduction in range and choice. Likewise, if we held 2.5 months forward cover, the range and choice would improve, and sales would go up by about 5%. We couldn’t possibly hold much more than 2.5 months forward cover, or much less than 1.5 months cover.
As for food, at any time we keep total stock equivalent to food sales for the next month. Much of the food is frozen or tinned, some is bought fresh daily or weekly, but, as I say, on average we keep one month’s worth ofsales in stock at any time.’
Payment Policies
‘About 50% of our own-bought merchandise purchases are paid for in the month following purchase, 30% in the month after that and 20% in the month after that. It’s a bit naughty really, buying stuff in February and not paying for it sometimes till May, but cash flow can be tight, and everyone does it. Some of our suppliers will give us quite decent discounts for payment within seven days, and I really would like to take advantage of these one day.
The concession sales revenue is forwarded to the concessionaire minus our commission. At the moment, 80% of concessionaires are paid in the month of sale, and 20% are paid in the month after. I think that we could get away with holding on to the money for a little longer, but I’m worried about damaging relationships.
Food purchases must be paid for on delivery, or after a few days at the most. It is what nearly all food suppliers insist upon.’
Financing
‘We currently have an overdraft facility of £1m. We could do with a little more, to be honest. I don’t think the bank understand the seasonal nature of a business like ours. We’re always waiting for Christmas to make some money. The interest is at 7.5%, I think. It hasn’t changed much recently.’
Estimated Opening Balances
You ascertain that the opening balance of cash this year is expected to be about £50,000. According to the fixed asset register, the opening cost value of fixed assets will be £3,571,251 with associated accumulated depreciation of £1,810,131. The share capital of the company is 400,000 ordinary shares of £1 each fully paid. Various Chadwick- family members own the shares. Conclusion
‘So, I’d like you to produce a model for me that will simulate our business and let me try out some of the ideas we are considering. The bank would like to see such a model as well – and I think it would help me explain our business to them,’ says Alan.
‘Sales are always difficult to predict. I need to know the effect of changes in sales, or anything else for that matter, on profits and cash flows, and on the balance sheet as well. For example, I’m particularly keen to know the impact ofsales being 5% higher or 5% lower than expected for next year.
Required:
1. Produce a model of the business using a spreadsheet.
2. Prepare an ‘Executive Summary’ of your model and its findings. This should identify and highlight the key results of your model. Your conclusions regarding the viability of the particular decision scenarios that your client has identified should be included.
3. Submit a ‘Test File’ that evidences all the tests that you have carried out on your model to check that it is robust and reliable.
4. Give a 15-minute presentation to ‘your client’ in which you should demonstrate the flexibility, reliability, and ease of use of your model, and how ‘what-if?’ analysis can quickly be conducted.

Question: Alan Chadwick is the Chairman and Managing