# Budget Forecasts XDemo by miannaveed

VIEWS: 42 PAGES: 42

• pg 1
```									BUDGET FORECASTS X

Welcome.

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

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).

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
http://www.exceltemplates.com/store/all_programs_editions.php
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

What if?

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

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

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

PURPOSE:

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).
QUICK START
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.
3/18/2010

Budget Start date                            6/1/2006

Current month end                           3/31/2010   << If you cannot read this date, you
Screen image taken from Year 1 budget forecast
Years 2 & 3
Div./Department
Cut Flowers
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
Miscellaneous
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

Currencies
If you need to display a different currency symbol within any spreadsheet, select the cell range, pull down Format>Cells and

Date formatting
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
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.