Create an ATM Application
We have employees who have check-out cashier checks to take to auctions. Sometimes those checks are used to purchase things and sometimes they are returned. Sometimes we just need a way for them to record a deposit.
I've been tasked with creating an application to track these withdrawals and then if something is purchased they come back and enter what was purchased with which check and if the who amount was used. Or they bring it back and indicate they are putting the unused money back into the system. Other times they will simply need to record a deposit that is not related to a withdrawal.
Is something like this possible in Smartsheet? It should be a running balance sheet, but also a way to put checks back into the system and link it to the withdrawal. Other times they will need to enter the inventory data into the sheet wich is easy. The rest I'm not sure the best way to do it.
I appreciate any guidance and especially examples.
Answers
-
If they are taking out checks, can I ask how many and the denominations on a single line of the sheet or will they have to make multiple entries for different checks and amounts? Is it possible to ask number of checks and denominations in a single line? What is they need 2 checks for 300K and 1 for 100K and one for 250k as an example?
-
I would recommend one-row per transaction (a single line each time). This will make it much easier to do formulas, summaries, averages, etc.
I would personally use a Form with Conditional Logic to record your different types of entries. For example, the first question can be a dropdown clarifying what it is they're looking to record: Record a Deposit, Withdraw Check, Check Used, Return Check. Then depending on what's selected you can surface different fields (columns).
This would enable you to gather returned & deposited money in one column (incoming) and withdrawn money in another (outgoing).
You could use a formula to auto-fill a column letting you know if a check has simply been Withdrawn, or if there's a new submission with the same check number noting that it has either been Used or Returned.
=IF(COUNTIFS([Check Number]:[Check Number], [Check Number]@row) = 1, [Reason Column]@row, IF(COUNTIFS([Check Number]:[Check Number], [Check Number]@row, [Reason Column]:[Reason Column], "Returned") > 1, "Returned Check"... and so on.
There may be some templates in the Solution Center that could give you a starting point as well (such as the Expense Tracking and Rollup)
Cheers,
Genevieve
-
@Genevieve P., I love this! I feel good that I had everything except the last column and formula you mentioned. What format is that column and I'm having trouble understanding exactly what it will do. Can you walk me through it? What is being counted and what is the result?
Could I create a column that looks at the Check# column if there is a matching one, it gives a check that it was returned. That might be hard given a check could be taken out and returned multiple times, but it's an idea.
-
@Genevieve P. did you happen to see my question on this? I sincerely value your input and hope you have time to reply. Thank you!!
-
Hey @Andrea Westrich
Thanks for the tag & notification! 🙂
The last column I mentioned would be a Text/Number column to show the most recent "status" for that Check Number... just in case there have been multiple row submissions with the same number.
=IF(COUNTIFS([Check Number]:[Check Number], [Check Number]@row) = 1, [Reason Column]@row,
^This looks in the [Check Number] column and counts how many times the check in this row is listed in the sheet. If it = 1, meaning it's the 1st and only time this specific check number is listed, it simply returns the same "Reason" as what's in your Reason column for that same row.
But what if there are multiple rows with the same check number?
Determine what's the most important value to see. In my example, I then search to see if there are rows that have the reason "Returned" associated with the number from that row... if so, the output of the formula is "Returned Check":
IF(COUNTIFS([Check Number]:[Check Number], [Check Number]@row, [Reason Column]:[Reason Column], "Returned") > 1, "Returned Check"
If you need help with the formula, we would just need to know all your possible reasons and what's the most important one to surface when there are multiple rows. 🙂
