Giselle Schmidt is an analyst for Allied Medical Waste and she needs to prepare a cash budget in order to determine the optimal time to purchase new Medical Waste Transport Vehicles costing $63,000 each.

April and May sales were $327,031 and $383,906 dollars respectively. The firm collects 20% of its sales during the month, 65% of sales in the following month, and 15% two months after the initial sale.

Each month, Allied purchases inventory equal to 55% of the next month’s expected sales. The company pays for 40% of its inventory purchases in the same month and 60% in the following month; however, the firm suppliers give it a 2% discount if it is paid during the same month as the purchase.

Other payments include:

June: $227,500 July: $213,281 August: $199,062 September: $142,188 October: $127,750

A minimum cash balance of $25,000 must be maintained each month and the firm pays 4% annually for short-term borrowing from the bank.

Create a cash budget for June to October 2022. The cash budget should account for short-term borrowing and pay back of outstanding loans as well any interest expense incurred. Allied ended May with a $30,000 unadjusted cash balance. Include a Sparkline to highlight Sales, Total Collections and Total Disbursements.

Part B:

Giselle’s manager is considering stretching out Allied’s payment for purchases as she believes that it may be less expensive to borrow from suppliers then from the bank. She has instructed Giselle to use the scenario manager to see what the total cumulative interest cost would be

if the company paid for 0%, 30%, or 40% of its inventory purchases in the same month while the remainder would be paid in the following month. Giselle does not agree. Create a scenario summary and describe whether the results support her manager’s thinking or Giselle’s and explain why?

Please remember to use IF statements, MIN and MAX when appropriate. Extra points are available for outlining and using TEXT and Date joins

Extra Credit – How many trucks could Giselle order while still keeping her minimum cash constraint in place and assuming the original 40%/60% payment for inventory payments? Assume debt cannot exceed $75,000. Please Show work for the extra credit.

Answer rating: 100% (QA)

To create a cash budget for Allied Medical Waste from June to October 2022 we will follow the given information and calculate the total collections total disbursements shortterm borrowing and interest ... View the full answer