MGT$105-$Final Case Study

Vintage Ltd$.$ owns a chain of eight stores selling professional audio equipment. In the past, the

business maintained a healthy cash balance. However, this has fallen in recent months, and at the

end of September $2024,$ the company had an overdraft $($negative cash balance$)$ of $$70,000.$ In view

of this, Vintage Ltd$.\text{'}$s president has asked you to prepare a cash forecast for the next six months.

You have collected the following forecast data for the six months ended March $31,2025$:

Other pieces of information you were able to obtain included the following:

a$)100\%$ of sales are on credit. $40\%$ of sales are collected in the month of sale; $60\%$ are

collected in the month following sale.

b$)$ Inventory balance at October $1$ amounted to $$230,000$ and accounts payable was

$$110,000.$

c$)$ The purchases in October, November and December are contractually committed, and

those in January, February and March are the minimum necessary to restock the expected

depleted inventories. Note that the cost to break the purchase commitment contract is

$10\%$ of the value of the contract.

d$)$ Cost of goods sold is $50\%$ of sales and suppliers allow one month's credit on purchases.

e$)$ Taxes of $$90,000$ are due in January.

f$)$ Dividend payments of $$45,000$ are due on Feb ${15}^{th}.$

g$)$ "Other expenses" include depreciation of $$10,000$ per month.

h$)$ Rent expense is paid six months in advance, in June and December.

Required:

Using the MS Excel concepts learned in this course, complete the following analyses:

Calculate the Cashflow Forecast for the end of each month for the six months ended

March $31,2025.$ Include a projection as to whether loans will be required, and how much.

Calculate the projected inventory levels at the end of each month for the six months

ended March $31,2025.$

Put your insights into a PPT presentation to communicate the above analyses as well as a

slide discussing the problems you anticipate the company having in the next six months,

plus how you suggest overcoming them.

Both your Excel and PPT files should be submitted via Dropbox in iLearn by $4$ pm $.$

Work on your own $-$ submissions should be your original work $($I can tell.$)$

GOOD LUCK!