Pathway to Excel Certification Part 2 – Cash Flow Budget

The Cash Flow Budget exercise is designed to simultaneously impart Excel skills and reinforce Introductory Managerial Accounting concepts. The exercise covers 48 Excel concepts:

• Using the Go To command to navigate to specific locations
• Using the Ctrl-Home keyboard shortcut to return to cell A1
• Using the FIND function to locate information within a worksheet cell
• Using the LEFT function to extract characters from the left side of a cell
• Applying number formats, including Currency, Pecent, Comma, and Decrease Decimal
• Using the TRIM function to remove extraneous spaces from text
• Using the SUBSTITUTE function to remove or replace text in a cell
• Using the CONCATENATE function to combine pieces of text together
• Using the RIGHT function to extract characters from the right side of a cell
• Using the UPPER function to convert text to all upper-case
• Using the LOWER function to convert text to all lower-case
• Assigning range names to cells to document inputs
• Exploring Excel’s Name Manager
• Using the MID function to extract text from the middle of a cell
• Employing the Table feature to improve spreadsheet integrity
• Freezing panes to keep key columns and rows always visible
• Generating a series of all 12 months with Excel’s Fill Handle
• Applying absolute and relative references within formulas
• Understanding why a formula may return #VALUE!
• Saving time with Excel’s AutoSum feature
• Understanding the order of operations in Excel
• Understanding why a formula may return ######### in a cell
• Looking up data in a spreadsheet with the SUMIF function
• Multiple techniques for saving one’s work in Excel
• Copying formulas from one part of a spreadsheet and pasting elsewhere
• Utilizing the Find and Replace feature
• Changing page orientation from portrait to landscape for printing
• Using Page Break Preview and returning to Normal View
• Managing page breaks in a worksheet
• Using VLOOKUP to look up information in a spreadsheet
• Using the Ctrl key to select non-contiguous cells
• Applying cell borders, bolding, and underlining
• Centering text within a worksheet cell
• Indenting text within a worksheet cell
• Changing font size within a worksheet cell
• Creating hyperlinks as navigation aides within workbooks
• Setting the print area for a worksheet
• Inserting an image into a workbook to use as a watermark on printouts
• Outlining a worksheet with the Group feature to manage information overload
• Changing margins for a printout
• Inserting rows into a worksheet
• Verifying the integrity of SUM functions

You’ll be asked to carry out 9 actions on your 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.
• Calculating budgeted sales in dollars, along with sales commissions in dollars.
• Adding a new expense to a budget
• 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.

Rest assured, support is available if you get stuck by emailing ask@davidringstrom.com.