How to Prepare a Broad Working Capital or Cash Flows Budget

Step-by-Step Guide

1. 1

   Step 1: Open a new workbook in Excel from the desktop

   Double click on Excel (either the green X on the dock or the app title in the folder) and select File New Workbook. ,, Doing so will select the entire worksheet.
   
   Format the number of cells to decimal places 0, show comma.
   
   Format Cells Font size to 9,10 or 12, bold.
   
   Color the cells the lightest sky blue.
   
   Title the worksheet, "BudgetWrkgCapital" and save the workbook as "BudgWkgCap" into an appropriate folder such as 'LifeGuide Hub Articles'.,,,,,,,,,,,,,,,,,,,,,, Your cash flows budget should resemble the one pictured here if you apply a light blue to all the cells. , For more art charts and graphs, you might also want to click on Category:
   Microsoft Excel Imagery, Category:
   Mathematics, Category:
   Spreadsheets or Category:
   Graphics to view many Excel worksheets and charts where trigonometry, geometry and calculus have been turned into art, or simply click on the category as it appears in the upper right white portion of this page, or at the bottom left of the page.
2. 2

   Step 2: from the dock

3. 3

   Step 3: or from your applications folder inside the Microsoft folder.

4. 4

   Step 4: In Preferences

5. 5

   Step 5: in General

6. 6

   Step 6: set R1C1 to unchecked or Off; in Ribbon

7. 7

   Step 7: set Ribbon to checked or On; and in View

8. 8

   Step 8: set Show Formula Bar by Default to checked or On.

9. 9

   Step 9: Click in the far-upper-left corner above the 1 of row 1 and to the left of column A.

10. 10

    Step 10: Select columns A:C and Format Column Width .3"; select column D and Format Column Width 4.2"

11. 11

    Step 11: select column E:F and Format Column Width 1.75" and Format Cells Number Custom $#

12. 12

    Step 12: ###;$(#

13. 13

    Step 13: ###);$0

14. 14

    Step 14: Enter the Statement Header in column E:

15. 15

    Step 15: Enter (Amounts in Thousands) in cell F1 (if and as applicable)

16. 16

    Step 16: and Format Cells Alignment Center

17. 17

    Step 17: Enter the Company Name in cell A2: XYZ Corporation

18. 18

    Step 18: Enter in cell A3: Budget of Working Capital - broadly defined

19. 19

    Step 19: Enter in cell A4: for the year ending

20. 20

    Step 20: December 31

21. 21

    Step 21: 2015 (or as applicable for the current fiscal planning year.)

22. 22

    Step 22: Enter the line titles and line amounts

23. 23

    Step 23: Enter in cell B6: Funds will be provided by:

24. 24

    Step 24: and Format Cells

25. 25

    Step 25: Font bold and underlined Enter in cell C8: Operations Enter in cell C9: Net Income Enter in cell C10: Plus deductions not requiring working capital Enter in cell D11: Depreciation Enter in cell D12: Amortization of patents Enter in cell D13: Loss on sale of equipment Enter in cell F9: 153000 Enter in cell E11: 110000 Enter in cell E12: 25000 Enter in cell E13: 4000 and Format Cells Border underline Enter in cell F13: the formula =SUM(E11:E13) (

26. 26

    Step 26: result = $139

27. 27

    Step 27: 000) and Format Cells Border underline Enter in cell F14: the formula =SUM(F9:F13) (

28. 28

    Step 28: result = $292

29. 29

    Step 29: 000) Enter in cell C15: Less amounts not providing working capital: Enter in cell D16: Sinking fund earnings Enter in cell D17: Amortization of bond premium Enter in cell C18: Total funds from Operations Enter in cell E16: -14000 Enter in cell E17: -1000 and Format Cells Border underline Enter in cell F17: the formula =SUM(E16:E17) and Format Cells Border underline (

30. 30

    Step 30: result = $15

31. 31

    Step 31: 000) Enter in cell F18: the formula =SUM(F14:F17) (

32. 32

    Step 32: result = $277

33. 33

    Step 33: 000) Enter in cell C19: Planned increase in accounts payable and other current liabilities Enter in cell F19: 220000 Enter in cell C20: Equipment to be sold Enter in cell F20: 6000 Enter in cell C21: Long-term mortgage note to be issued Enter in cell F21: 500000 and Format Cells Border underline Enter in cell B22: Total funds to be provided Enter in cell F22: the formula =SUM(F18:F21) and Format Cells Border DOUBLE underline and bold (

34. 34

    Step 34: result = $1

35. 35

    Step 35: Enter in cell B24: Funds will be applied to: and Format Cells bold Enter in cell C25: Dividends payment Enter in cell C26: Purchase of building and equipment Enter in cell C27: Payment into sinking fund Enter in cell C28: Increase in accounts receivable Enter in cell C29: Increase in merchandise inventory Enter in cell C30: Increase in cash and prepaid expense(s) Enter in cell F25: 72000 Enter in cell F26: 520000 Enter in cell F27: 11000 Enter in cell F28: 110000 Enter in cell F29: 260000 Enter in cell F30: 30000 and Format Cells underline Enter to cell B31: Total funds to be applied Enter to cell F31: the formula

36. 36

    Step 36: =SUM(F25:F30) and Format Cells Border DOUBLE underline and bold (

37. 37

    Step 37: result = $1

38. 38

    Step 38: 000) Your Working Capital Budget should resemble the one pictured here if you apply a canary yellow to all the cells.

39. 39

    Step 39: Title the next worksheet tab

40. 40

    Step 40: CashFlowsBudget

41. 41

    Step 41: Select columns A:B and Format column width

42. 42

    Step 42: Select column C and Format column width 4"

43. 43

    Step 43: Enter to cell A1: Cash Flows Budget

44. 44

    Step 44: XYZ Corporation

45. 45

    Step 45: Enter to cell A2: for the year ended December 31

46. 46

    Step 46: 2015 (or as applicable for the current fiscal planning year)

47. 47

    Step 47: Enter to cell E1: (000's) and Format Cells Alignment Center

48. 48

    Step 48: Select columns D:E and Format Cells Number Custom $#

49. 49

    Step 49: ###;$(#

50. 50

    Step 50: ###);$0

51. 51

    Step 51: Enter the line items and amounts Enter to cell A4: Cash flows to be used in (from) operating activities

52. 52

    Step 52: and Format Cells bold Enter to cell B5: Cash receipts from customers Enter to cell B6: Cash paid to suppliers and employees Enter to cell B7: Equals: Cash generated from operations Enter to cell D5: 10000 Enter to cell D6: -2500 and Format Cells Border underline Enter to cell E7: the formula =SUM(D5:D6) Enter to cell B8: Interest payments Enter to cell B9: Income tax payments and Format Cells Border underline Enter to cell C10: Net cash flows from operating activities Enter to cell D8: -2100 Enter to cell D9: -2600 and Format Cells Border underline Enter to cell E9: the formula =SUM(D8:D9)

53. 53

    Step 53: and Format Cells Border underline Enter to cell E10: the formula =SUM(E7:E9)

54. 54

    Step 54: Enter to cell A12: Cash flows to be used in (from) investing activities

55. 55

    Step 55: and Format Cells bold Enter to cell B13: Equipment to be sold Enter to cell B14: Dividend Receipts Enter to cell C15: Net cash flows from investing activities Enter to cell D13: 8500 Enter to cell D14: 3000 and Format Cells Border underline Enter to cell E15: the formula =SUM(D13:D14)

56. 56

    Step 56: Enter to cell A17: Cash flows to be used in (from) financing activities

57. 57

    Step 57: and Format Cells bold Enter to cell B18: Dividend payments Enter to cell C19: Net cash flows from financing activities Enter to cell D18: -2800 and Format Cells Border underline Enter to cell E19: the formula =D18

58. 58

    Step 58: Enter to cell A21: Net increase in cash and cash equivalents planned Enter to cell E21: the formula =SUM(E10:E20)

59. 59

    Step 59: Enter to cell A22: Cash and cash equivalents

60. 60

    Step 60: beginning of fiscal planning year Enter to cell E22: 1150

61. 61

    Step 61: Enter to cell A23: Cash and cash equivalents

62. 62

    Step 62: end of fiscal planning year Enter to cell E23: the formula =SUM(E21:E22)

63. 63

    Step 63: and Format Cells bold and DOUBLE underline border.

64. 64

    Step 64: Make use of helper articles when proceeding through this tutorial: See the article How to Do Common Ratio Analysis of the Financials for a list of articles related to Excel

65. 65

    Step 65: geometry and/or trigonometry

66. 66

    Step 66: charting/diagramming and algebraic formulation.

Detailed Guide

About the Author