Access: master budget project: schedule of cash collections.
The first calculation we need to complete is the cash collections from customers. Your company may have a variety of ways to calculate cash collections, so our model will have a place to enter these values on the Reports Menu form. As usual, we'll use the values from the 2010 Excel-Based Budgeting series by Teresa Stephenson and Jason Porter.
Update the Report Menu
Open the Reports Menu form in Design view and add a text box in the Cash Budget section of the form. Name the text box "BegCashBal," label it "Beginning Cash Balance," and set the default value to 30,176. The format should be Currency with zero decimal places.
Next to the four text boxes we added in March for "Planned Expansion of Equipment," add four additional text boxes (one for each quarter) for "Cash Collections." Set their formats to Currency, with zero decimal places, and assign them the following names and default values:
* CCQtr1: 1,174,411
* CCQtr2: 1,299,625
* CCQtr3: 1,369,883
* CCQtr4: 1,419,904
Save the form and change to Form view (see Figure 1).
Cash Budget Table
Create a table with six fields: Subtotal, DetailLine, Q1, Q2, Q3, and Q4. Save it as "Cash Budget." This is the table we'll use to build the data for the Cash Budget Report.
Load Cash Available
It will take two queries to get data from the Reports Menu form and load the first two lines into the Cash Budget table. The first query is an append query named "Beginning Cash Balance" that uses the Beginning Cash Balance we put in the Reports Menu form and appends Subtotal, DetailLine, and Qtr1 to the Cash Budget table. The first field--Subtotal:"Total Cash Available"--should append to Subtotal. The second is DetailLine:"Cash Balance, Beginning" and should append to DetailLine. The third is Qtr1:[Forms]![Reports Menu]! [BegCashBal] and should append to Q1.
The second query, "Cash Collections," appends Subtotal, DetailLine, and Qtr1-Qtr4 to the Cash Budget table. It uses the Cash Collection amounts from the four text boxes we added to the Reports Menu form. The Subtotal field should be Subtotal:"Total Cash Available"; DetailLine should be DetailLine:"Add Receipts: Cash Collected from Customers"; and Q1-Q4 should be Qtr1: [Forms]![Reports Menu]![CCQtr1], adjusted for each respective quarter.
Run, save, and close both queries.
Cash Budget Report
Open the Cash Budget Report in Design view. In the Property Sheet, change the report's record source to the Cash Budget table instead of DM Cash Payments. Next, select the Description text box and change its Control Source to DetailLine. Delete the Total Cash Paid for Materials label from the Report Footer. Copy the DetailLine text box we just adjusted and paste it where the Total Cash Paid for Materials label was. Change its Control Source to Subtotal. Use the Group and Sort button so that you can add a sort by Subtotal and Detail, as shown in Figure 2. Sort by DetailLine with Z on top.
Change the Can Grow property on the DetailLine text box to Yes so that it can expand with the table contents. Preview and save the report.
Build in processes whenever they will be consistent. Otherwise, build in flexibility like we did by allowing entry of the Cash Collections to accommodate a wide variety of ways of calculating this outside the system.
Next month we'll continue our work developing the Cash Budget report. SF
Patricia Cox has taught Excel and Access to management accounting students and other college majors and has consulted with local area businesses to create database reporting systems since 1998. She also is a member of IMA's Madison Chapter. To send Patricia a question to address in the Access column, e-mail her at email@example.com.
By Patricia Cox
|Printer friendly Cite/link Email Feedback|
|Date:||Apr 1, 2014|
|Previous Article:||Excel: data validation in excel.|
|Next Article:||Tech Practices: the rise of crowdfunding social media, big data, cloud technologies.|