Overview
Boston is an up-and-coming sporting goods store! It is the end of the year and the CEO, Austin Church, has asked you to develop a cash budget for January and February of next year. Austin has provided you with some forecasted data that includes projected sales, purchases, wage expenses and other operating expenses. Additional information is provided below to help you prepare the cash budget for February:
February sales and purchases are projected to be 30% more than the January sales and purchases data you are given. The sales price and purchase price given is the total revenue and expense for that transaction. Sales are 30% cash and 70% credit. All credit sales are billed at the end of the month. The term of the credit sales is 2/10, n/30. The credit sales pattern is 70% in the month following the sale, of which 75% are collected within 10 days and 30% in the month thereafter. Total sales in December of the current year are expected to be 50,000.
Purchases are all on credit with 60% paid in the month of purchase and 40% the next month.
Operating expenses are paid in the month they were incurred.
Boston desires to keep its cash balance between $7,500 and $10,000 at the end of each month.
Loans are used to maintain the minimum cash balance. Repayments of the outstanding loan balance are made whenever the cash balance exceeds $10,000. Boston has a bank loan of $5,000 outstanding.
Find the total cash available before making any repayments, and then find the amount of the loan repayment (if any) and the ending cash and loan balances for the month of February.
Instructions
Upload the data into Alteryx. Clean the sales and purchases data so that the Product column is in title case and find the total amount of sales and purchases for both January and February. Calculate the wage expense for both January and February. Get all of the data (Sales, Purchases, Wage Expense and Other Expenses) into one table. Output to a browse tool with the following headers: Feb Cash Balance, Loan Repayment, Loan Balance End of Month, Ending Cash Balance. Good luck!
Challenge95_Data.yxzp (16.1 KB)
Threw me through a loop for quite some time and wound up just having to look at the solution to understand process to sum everything in the right order.
I feel like there is some data missing from this scenario. January starting cash balance, purchases in December, credit sales November, etc. Won’t those affect January cash flows? Maybe I missed those figures.
I don’t have a file to upload on this one. I snagged a screenshot before closing the file (my usual precaution with Alteryx…), after which the file was pulled into the Aether, converted to dark matter, and shot off into space.
I did the first part of the work flow the same until I got to the append fields section. The video was helpful in helping me understand how to use that tool. Overall, a good challenge, but I’m not sure that the formulas for calculating the total cash used for the month are correct or fully match the instructions given for the challenge. At first, I got significantly different cash flows. For example, 30% of the sales weren’t on credit (cash sales), but the video instruction never accounted for that cash. Additionally, the instructions say 60% of purchases are paid for in the month they are incurred, but it looks like the video instruction reversed that and had us apply the 60% in February. Perhaps I didn’t fully understand the instructions, but it might be helpful to clarify more in the instructions. I ended up having to go and watch the step-by-step instructions in the video. Overall, really good practice, but it may be helpful for future students to clarify some of the calculations. Thanks so much for putting this challenge together!
