Cash Flow Budget

View the step-by-step video for the exercise

Download the Excel spreadsheet used for the assignment 

Download a PDF of the exercise – Excel for Mac

Download a PDF of the exercise – Excel for Windows

Download a PDF of this assignment description

Exercise Description

The Cash Flow Budget exercise is designed to simultaneously impart Excel skills and reinforce Introductory Managerial Accounting concepts. Students pay $9.95 for the exercise are guided through the following techniques by way of numbered steps onscreen and video demonstrations:

  • Assigning range names to input cells.
  • Exploring the Name Manager feature in Excel.
  • Crafting formulas to project monthly sales in units
  • Saving one’s work in Excel frequently by way of various methods.
  • Summing multiple cells at once with the AutoSum feature.
  • Establishing a table that shows percent of collections received each month.
  • Contrasting brute-force method of allocating collections across 3-month periods with using SUMIF.
  • Employing the SUMIF function for looking up amounts in a spreadsheet.

Students carry out these activities in Excel on their own:

  • Transcribing budget assumptions from narrative form into worksheet cells.
  • Crafting beginning and ending inventory formulas based on projected monthly sales in units.
  • Compute the number of inventory units to be purchased each month.
  • Using the Gross Margin and Sales Price assumptions to determine inventory purchase cost in dollars.
  • Calculating budgeted sales in dollars, along with sales commissions in dollars.
  • Recording payment of a cell phone bill.
  • Posting a cash prepayment from a customer.
  • Recognizing a portion of a prepayment as revenue.
  • Developing an operating budget based upon supporting schedules developed by the student.
  • Creating a cash-basis budget based upon supporting schedules developed by the student.
  • Adding totals to the operating budget and cash-basis budgets.
  • Utilizing the SUM function in Excel to total columns.

Each student is assigned a unique 4-digit number known as their Spreadsheet ID for use during the exercise. This ID number is incorporated several times into the exercise and helps ensure that each student turns in their own work:

  • Completion: This score indicates if the student attempted a given step.
  • Accuracy: This score indicates carried out the step accurately.

Within one week of an assignment’s due date we provide instructors with a spreadsheet showing all student results. Partial credit in our scoring is given if a student at least attempts a step. Instructors determine final credit for each student, and whether late submissions are allowed. Students receive written feedback confirming which steps were completed successfully, as well as providing guidance on steps that they did not attempt or did not complete correctly.

 

Please direct questions to David Ringstrom (david@studentsexcel.com) or 404-784-0275. Learn more about us at www.studentsexcel.com.