Please sign here: Part A: 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.Medical.Waste 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. A minimum cash balance of $25,000 myst 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. Please save your file as Allied Medical Waste Ex1 (FINA 3030). First. Last (i.e. just add your name to the end of the existing file name) and send it directly to my email at Carolyn kedersha@uml.edu Allied Medical Waste Cash Budget \begin{tabular}{|l|l|ll|} \hline Month & Sales & Other payments \\ \hline April & $327,031 & & \\ \hline May & $383,906 & & \\ \hline June & $489,125 & $ & 227,500 \\ July & $403,812 & $ & 213,281 \\ \hline August & $344,093 & $ & 199,062 \\ \hline September & $344,093 & $ & 142,188 \\ \cline { 2 - 5 } October & $216,125 & $ & 127,750 \\ \hline November & $230,343 & & \\ \hline \end{tabular} Please sign here: Part A: 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.Medical.Waste 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. A minimum cash balance of $25,000 myst 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. Please save your file as Allied Medical Waste Ex1 (FINA 3030). First. Last (i.e. just add your name to the end of the existing file name) and send it directly to my email at Carolyn kedersha@uml.edu Allied Medical Waste Cash Budget \begin{tabular}{|l|l|ll|} \hline Month & Sales & Other payments \\ \hline April & $327,031 & & \\ \hline May & $383,906 & & \\ \hline June & $489,125 & $ & 227,500 \\ July & $403,812 & $ & 213,281 \\ \hline August & $344,093 & $ & 199,062 \\ \hline September & $344,093 & $ & 142,188 \\ \cline { 2 - 5 } October & $216,125 & $ & 127,750 \\ \hline November & $230,343 & & \\ \hline \end{tabular}