BUDGET FORECASTS X
ADVANCED FINANCIAL REPORTING & ANALYSIS EXCEL WORKBOOKS.
Our demo editions provide copies of inputs, outputs supplied by this forecasting
The calculation routines are disabled. The outputs are copies from the program version. Some spreadsheets are screen
Please read through the Excel requirements page.
The Buy Now purchase buttons accept Visa or MasterCard.
BUDGET FORECASTS X
COMPLETE SET GOLD EDITION PLUS BONUS 33 EXCEL TEMPLATES WITH FORMULAS (Next page).
COPYRIGHT. MILLENNIUM SOFTWARE INC, 2006.
Last updated 6/06/2006.
This is the demo version.
G & ANALYSIS EXCEL WORKBOOKS.
uts, outputs supplied by this forecasting program.
s from the program version. Some spreadsheets are screen images..
XCEL TEMPLATES WITH FORMULAS (Next page).
COMPLETE SET PLUS FINANCIAL FORMULAS
Further information at this page:
All Single Workbooks plus:
BONUS! 33 additional Financial excel templates with formula sequences.
There are no Macros. The formulas and functions used are completely available for you to use in your own applications or within these tem
Reveal formulas. When you need to reveal a formula cell click the cell and press F2. When you want to see all the formulas within the entir
System requirements. The bonus templates run under Windows Excel and Apple Mac Excel and in a pocket PC with Excel.
The Complete set supplies all our single workbook titles plus these:
BONUS TEMPLATES with OPEN FORMULAS
EXCEL TEMPLATES with FORMULAS
1. What if profit modelling? (xls) A handy compact Excel table calculates profits, working capital, cash flows and valuation (P/e ratio),
2. What if capital assumptions change? (xls) Capital investment “what if” analysis supplied in one compact table. Calculates net pres
3. What if financial ratios change? (xls) Calculates key financial ratios for successive periods within a compact Excel table spreadshee
4. What if currencies change? (xls) Instant calculation of 24 leading cross currency rates in real time within Excel. Imports current rat
5. What if costs change? (xls) Excel template calculates operating p/l for any combination changes in direct costs, capacity utilization,
6. What price to fill spare capacity? (xls) Excel template calculates the optimal price/demand table and chart that yields maximum pr
Securities & investments analysis templates.
7. Portfolio rate of return. (xls) A very handy spreadsheet and formulas if you want to track the return and annual rate of return from
8. Bond yields & amortization (xls) – This spreadsheet amortizes any bond to maturity, calculates Original Issue Discount, yield to ma
9. Treasury yields & amortization. (xls) This spreadsheet amortizes Government bonds to maturity calculates Original Issue Discount,
10. Bond pricing & IOD. (xls) This spreadsheet calculates the Original Issue Discount to yield a target maturity and annual yield to mat
11. Company valuation. (xls) This spreadsheet calculates company value of using EBITDA multiples adjusting for cash, working capital
12. Terminal (Closure/Sale) value. (xls) A very useful spreadsheet formulates the closure (sale) valuation to a cash flow forecast. Co
13. Net Present value & Internal Rate of Return #1. (xls) NPV formulas and calculations with 10 inputs for cash flow, working capita
14. Net Present value & Internal Rate of Return #2. (xls) NPV formulas and calculations with 10 inputs for cash flow, working capita
15. Net Present value & Internal Rate of Return #3. (xls) NPV and annual IRR formulas and value when input dates and timing of c
16. Present value of an amount to be received in the future. (xls) Present value of an amount due in the future calculated from tod
17. Future value of an investment or savings amount. (xls) Present value of an amount due in the future calculated from any starti
18. Rates of return & growth transformed to compound annual rates. (xls) This spreadsheet transform a series of sales figures in
19. Net Present Value of rental leasing. Decision making rental lease or purchase spreadsheet calculates the after tax net present va
20. Present value of Capital Budget Cash Flows. (xls) Excel template plus formula sequences for capital budgeting calculates net pre
Capital projects analysis templates
21. Project Appraisal Excel Template (xls). Quick calculations of EBITDA, payback, net present value, internal rate of return over 20
22. Construction projects rate of return. (xls) Specially tailored spreadsheet calculates the rate of return for construction projects wi
23. Capital Project Cash Analysis. (xls) Capital budget cash analysis that will stretch to any length of time line horizon. Over 10,000 c
24. Cash flow amortization schedule #1. (xls) A very useful Excel template and formulas for amortizing business loans to calculate a
25. Cash flow amortization schedule #2. (xls) Excel template provides the same calculations as #24 above plus an extra column ad
Balance sheet analysis template
26. Altman Ratio Scores. Altman ratio solvency formulas and template provides quick calculation of Altman Z scores for any firm. Altm
Borrowing capacity template.
27. Extra borrowing capacity with balloon. (xls) You can borrow more with a balloon option. This template works out how much mor
Balanced Scorecard calculations
28. Balanced Scorecard budgeting. (xls). With these dual templates you can enter your targets for a balanced scorecard budget (mak
Tools > Useful tools for Excel
29. Date stamp formula. (xls) Spreadsheet and formulas which will monitor task completions within Excel with this fixed time/date stam
30. Credit card validity check. (xls) Visa, MasterCard, Amex and Discover credit card numbers (13, 15 or 16 digits) can be validated*
* (Validated means checking whether the credit card number represents a real credit card. It does not say whether the credit card is acti
31. Moving average formula (max 500). (xls) A nice solution for Excel which ignores zero values so that you can build your own reco
32. Filling in the Blanks. (xls). This formula spreadsheet shows you how to fill the blanks when you have large imported lists of data im
33. Intentional Circular Calculations. (xls) Excel tells us when a formula creates a circular loop which needs unpicking. You can use in
Tools > Add-in Functions
34. Fifty five extra financial functions (VBA) add-in. (xla) More than fifty additional financial functions (VBA codes) give you with se
The files listed above are downloadable as a zip file (3 MB) or you can select CD shipping ($6.95 extra).
our own applications or within these templates. You can add to or adjust these templates anyway you want.
ant to see all the formulas within the entire spreadsheet, select Tools > Options > check the box Formulas in the View tab.
n a pocket PC with Excel.
al, cash flows and valuation (P/e ratio), unit costs and break even levels when the following variables change > unit sales, revenues, sales growth rates
one compact table. Calculates net present values and internal rate of return for any combination of pricing, direct costs, operating efficiencies, mainten
within a compact Excel table spreadsheet. You can compare up to five successive periods and the trend changes.
al time within Excel. Imports current rates for US dollar, AUS$, Can$, Euro, Brazilian Real, Chinese Yuan ,HK$, India Rupee, Japanese Yen, Malaysian Ri
nges in direct costs, capacity utilization, direct costs, material utilization, payroll costs, utility costs with this neat handy spreadsheet. A very handy cost
table and chart that yields maximum profits to fill the spare capacity you define. You need to input demand levels at two differing price levels for the sp
e return and annual rate of return from a series of buy and sell share transactions. You can select the NASD 30/360 or 365 days calculation formula. Qu
ates Original Issue Discount, yield to maturity and the annual yield to maturity using the Excel IRR function.
urity calculates Original Issue Discount, yield to maturity and the annual yield to maturity using Excel IRR function.
target maturity and annual yield to maturity. Plus formulas within 7-10.
ples adjusting for cash, working capital, real estate and long term liabilities.
e) valuation to a cash flow forecast. Computes the present value of a hypothetical future sale applying a market driven EBITDA multiple, which adds to
h 10 inputs for cash flow, working capital additional capital and disposals, calculates up to 50 years.
h 10 inputs for cash flow, working capital additional capital and disposals, calculate up to 120 months.
value when input dates and timing of cash flows are irregular.
unt due in the future calculated from today’s date.
in the future calculated from any starting date.
et transform a series of sales figures into continuously compounding annual rates of growth or rates of return measured from a fixed starting date such
t calculates the after tax net present value of a rental lease accounting for tax relief.
for capital budgeting calculates net present value and internal rate of return of a capital budget. 225 expense elements, average and total cash flow ca
nt value, internal rate of return over 20 years incorporating working capital.
te of return for construction projects with alternative scenarios. You can vary the debt to equity ratio; change the number of pre-construction periods, c
ngth of time line horizon. Over 10,000 cost elements plus date due payable or receivable. Calculate cash flows in and cash flows out on any date over a
amortizing business loans to calculate after tax net payments and effective after tax interest rate (using the internal rate of return method). Incorporat
as #24 above plus an extra column added to enter variable loan rates into any amortization period. Adjusts the amortization schedule with repayment
n of Altman Z scores for any firm. Altman ratios predict insolvency, or confirm financial well being based on score. (Two linked templates).
This template works out how much more you can borrow with a ceiling monthly repayment.
s for a balanced scorecard budget (make percentage changes to existing values and create fixed financial targets) that will improve your current financ
ithin Excel with this fixed time/date stamp formula for Excel.
(13, 15 or 16 digits) can be validated* with Excel using this complex checksum formula. Saves time and money with a one time check routine if you ar
s not say whether the credit card is active or delinquent).
ues so that you can build your own records of moving averages from within a large range table.
you have large imported lists of data imported from other applications which creates problems with sort and filtering Our template shows you how to c
p which needs unpicking. You can use intentional circular loops to perform some very neat financial calculations. This spreadsheet shows you how with
functions (VBA codes) give you with several short cut commands for repetitive financial calculations. From Quick() (Quick Ratio) and CRNT (current rat
unit sales, revenues, sales growth rates, dire
ct costs, operating efficiencies, maintenance c
ndia Rupee, Japanese Yen, Malaysian Ringgit, M
handy spreadsheet. A very handy cost
ls at two differing price levels for the spreads
360 or 365 days calculation formula. Quick and
driven EBITDA multiple, which adds to the presen
measured from a fixed starting date such as the start of
lements, average and total cash flow calculations,
e number of pre-construction periods, constr
n and cash flows out on any date over any time
ernal rate of return method). Incorporates arran
amortization schedule with repayment calculations a
re. (Two linked templates).
s) that will improve your current financial results.
with a one time check routine if you are processing
ering Our template shows you how to clean up
This spreadsheet shows you how with two exam
k() (Quick Ratio) and CRNT (current ratio) to
Budget Forecasts X
Short cut to ten year profit and loss forecast for up to 10 activity centers (Gold edition).
Consolidate individual p/l forecasts into combined p/l forecast.
Budget fixed and variable expenses.
Sensitivity and variablity analysis.
10 activity centers (Gold edition).
1. Enter unit sales forecasts into the Year 1 table E9 > P11.
2. Enter unit costs into the table C9 - C11.
3. Enter product prices into the table C14 - C16.
4. Enter percentage variable costs to sales value into the table C33-C42.
5. Enter fixed expenses starting at row 44.
6. If you are allocating Management charges or overhead allocations into the forecast, enter these into row 26.
7. Repeat for years 2 & 3, and 4-10.
You can adjust Year 1 to commence at any month by editing the date in E10 of Set Up.
The screen image below from the program edition shows Precedent dependant cells for these calculations.
Screen image of Precedents:
hese into row 26.
Enter your Business Name > Happy Valley Farm
Budget Start date 6/1/2006
Current month end 3/31/2010 << If you cannot read this date, you
If you cannot read this date, you need to switch your ToolPak VBA On. (Tools/Add-Ins).
Screen image taken from Year 1 budget forecast
Years 2 & 3
Happy Valley Farm To print columns A-N on one page, set Print Set up to Landscape and adjust to 65% normal size
……..………….…...…..3 Months Ending………….…………….… …………...…..3 Months Ending…………………………………
Unit Sales Sep-07 Dec-07 Mar-09 Jun-09 Year 2 Sep-09 Dec-09 Mar-10 Jun-10 Year 3
Products Unit Costs 1 2 3 4 Totals 5 6 7 8 Totals
Flowers - Export $0.25 100,000 115,000 125,000 140,000 480,000 155,000 165,000 170,000 175,000 665,000
Flowers - Local $0.41 75,000 85,000 90,000 95,000 345,000 105,000 110,000 115,000 120,000 450,000
Flowers - Eldoret $0.75 105,000 112,000 118,000 126,000 461,000 135,000 145,000 150,000 155,000 585,000
Total Units 2,986,000 280,000 312,000 333,000 361,000 1,286,000 395,000 420,000 435,000 450,000 1,700,000
Sales Unit Prices
Flowers - Export $2.35 $235,000 $270,250 $293,750 $329,000 $1,128,000 $364,250 $387,750 $399,500 $411,250 $1,562,750
Flowers - Local $2.99 $224,250 $254,150 $269,100 $284,050 $1,031,550 $313,950 $328,900 $343,850 $358,800 $1,345,500
Flowers - Eldoret $3.55 $372,750 $397,600 $418,900 $447,300 $1,636,550 $479,250 $514,750 $532,500 $550,250 $2,076,750
Total Sales $8,781,100 $832,000 $922,000 $981,750 $1,060,350 $3,796,100 $1,157,450 $1,231,400 $1,275,850 $1,320,300 $4,985,000
Direct Cost of Sales $1,396,700 $134,500 $147,600 $156,650 $168,450 $607,200 $183,050 $195,100 $202,150 $209,200 $789,500
Gross Margin $7,384,400 # $697,500 $774,400 $825,100 $891,900 $3,188,900 $974,400 $1,036,300 $1,073,700 $1,111,100 $4,195,500
Gross Margin % 84.09% 83.8% 84.0% 84.0% 84.1% 84.0% 84.2% 84.2% 84.2% 84.2% 84.2%
Operating Expenses $2,427,910 $238,376 $252,646 $269,766 $293,774 $1,054,252 $332,461 $317,932 $356,231 $367,033 $1,373,658
Operating Profit/Loss $4,956,490 $459,124 $521,754 $555,334 $598,126 $2,134,648 $641,939 $718,368 $717,469 $744,067 $2,821,842
Management Charges $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Profit/Loss $4,956,490 $459,124 $521,754 $555,334 $598,126 $2,134,648 $641,939 $718,368 $717,469 $744,067 $2,821,842
Operating Margin % 56.44% 55.18% 56.59% 56.57% 56.41% 56.23% 55.46% 58.34% 56.23% 56.36% 56.61%
Sep-07 Dec-07 Mar-09 Jun-09 Year 2 Sep-09 Dec-09 Mar-10 Jun-10 Year 3
Variable Costs Budget 24.30%
Variable Costs Variable % $202,176 $224,446 $238,566 $257,574 $922,452 $281,261 $299,232 $310,031 $320,833 $1,211,358
Fertilizer 6.40% 53,248 59,008 62,832 67,762 242,950 74,077 78,810 81,654 84,499 319,040
Pesticides 3.15% 26,208 29,043 30,925 33,410 119,577 36,460 38,789 40,189 41,589 157,028
Advertising 6.00% 49,920 55,320 58,905 63,621 227,766 69,447 73,884 76,551 79,218 299,100
Heating Fuel 2.40% 19,968 22,128 23,562 25,448 91,106 27,779 29,554 30,620 31,687 119,640
Gas/Diesel 2.00% 16,640 18,440 19,635 21,207 75,922 23,149 24,628 25,517 26,406 99,700
Electricity 0.85% 7,072 7,837 8,345 9,013 32,267 9,838 10,467 10,845 11,223 42,373
Irrigation Water 0.20% 1,664 1,844 1,964 2,121 7,592 2,315 2,463 2,552 2,641 9,970
Repairs, Equip./Vehicles 2.00% 16,640 18,840 19,635 21,207 75,922 23,149 24,628 25,517 26,406 99,700
Repairs, Bldg. 0.25% 2,080 2,305 2,454 2,651 9,490 2,894 3,079 3,190 3,301 12,463
Office Supplies 0.15% 1,248 1,383 1,473 1,591 5,694 1,736 1,847 1,914 1,980 7,478
Fixed costs $36,200 $28,200 $31,200 $36,200 $131,800 $51,200 $18,700 $46,200 $46,200 $162,300
Loan Payment 8,000 0 0 0 0 0 0 0 0 0 0
Salary and Wages 144,500 10,000 12,000 15,000 20,000 57,000 25,000 2,500 30,000 30,000 87,500
Depreciation 28,000 0 0 0 0 0 0 0 0 0 0
Insurance 1,600 0 0 0 0 0 0 0 0 0 0
Property Taxes 52,000 0 0 0 0 0 0 0 0 0 0
Lease/Rental 24,000 0 0 0 0 0 0 0 0 0 0
Professional Fees 36,000 0 0 0 0 0 0 0 0 0 0
Income Tax 0 0 0 0 0 0 0 0 0 0 0
Dividends 0 0 0 0 0 0 0 0 0 0 0
Total expenses 55.30% $238,376 $252,646 $269,766 $293,774 $1,054,252 $332,461 $317,932 $356,231 $367,033 $1,373,658
Screen image taken from years 4-10 budget forecast.
Gold edition expansion..
Gold edition expansion...
Check you have TookPak-VBA switched on!
To ensure that date formatting works properly you need to check that you have Excel's ToolPak VBA switched on as shown o
If you need to display a different currency symbol within any spreadsheet, select the cell range, pull down Format>Cells and
If the dates you see in this demo are out of range and do not display properly, you need to adjust your Long date settings in
Follow these steps:
Click Start, bottom left hand corner and then Control Panel.
Select Date, Time, Language and Regional Options.
Select change the format of numbers, dates and time.
Select Customize in the second image displayed opposite >>>>
Select the date tab and change the Long date format as shown in the third image above:
(To enlarge either image, right click the image/Format picture/Size/Increase the height):
ak VBA switched on as shown opposite
e, pull down Format>Cells and adjust to the required symbol.
djust your Long date settings in your Control Panel.