SOLVE THE QUESTION USING EXCEL SOLVER PLEASE SHOW THE EQUATIONS USED

Nadia has a womens fashion wear shop on the high street in the West End of London. She expects to have 1,000 left in the bank at the end of May. She is concerned about her plans to purchase womens fashion wear over the summer and the effects it will have on her bank balance.

Thus she has forecast her volume and pattern of sales for the coming months of June, July, August and September. Nadia has also calculated the expenses incurred running the business for the three months of June July and August. Nadia's forecasts for the next few months are:

|------------ ------------- Pattern of ------------- ------------| sales

Product	Vol. of Sales for 4 months	Jun	Jul	Aug	Sept	Total
Women's fashion wear (units)	2,000	870	609	435	87	2000

Turn Over

Page 4 BUS239 (2021)

Opening stock of a month is 75% of that months sales. Purchase price is 9.20/unit and Selling price is 29.90/unit. Purchases and sales are both in cash.

Expenses

Rent Depreciation of fittings

Refurbishment of shop Total expenses

Required:

Cost for 3 months () 18,000 15,000

3,600

8,500 45,100

Pattern of expenses

Paid evenly throughout the period Paid quarterly in January, April, July and October

Charged equally throughout the period Paid quarterly in March, June, September and December

Wages

Prepare the following budgets:

1. a) The sales and purchases budget in units for the 3 months; (15 marks)

2. b) The cash budget, showing both the monthly and the total for the three months (June, July & August):

   (i) Receipts from customers and payments to suppliers from womens fashion wear trading;

   (ii) Expense payments; & (iii) Movements on cash balance. (20 marks)

3. c) Comment on your findings in a) and b) and advise Nadia (15 marks)