BYU Student Author: @Boston
Reviewers: @DylanKing, @Marco
Estimated Time to Solve: 60 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
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.
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!
Suggestions and Hints
- Making an “Account” column with the text input tool and appending it could be helpful to union all of the data.
- Transposing the January and February data separately could be helpful.
- Remember which expenses touch cash and which do not!
This was an exciting challenge. I took a few moments to understand how to calculate the cash balance for each month, but I enjoyed the challenge.
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.
Challenge95AmyB.yxmd (36.3 KB)
This was the most fun of all the challenges I’ve done on Techhub! Thanks!
I learned so much, but it didn’t feel overwhelming.
This was interesting. I did all the calculations for cashflow in one line instead of two, but it all worked out in the end!
Challenge95MX_Bostons_Big_Budget_Bash.yxmd (37.7 KB)
This was incredibly hard for me. I had to look at the video for help.
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.
This was a hard challenge for me. Had to look at the video to help me understand what to do. Good practice for what more advanced scenarios.
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!
Great challenge, my computer crashed the first I was trying to solve it but I went through it a lot faster the second time!