jagomart
digital resources
picture1_Budget Spreadsheet 32453 | Workbook1 2


 229x       Filetype XLS       File size 0.62 MB       Source: exinfm.com


File: Budget Spreadsheet 32453 | Workbook1 2
sheet 1 1 control menu financial analysis amp forecasting prepared by matt h evans cpa cma cfm purpose of spreadsheet revised 11222002 home active worksheet active general input wksh2 balance ...

icon picture XLS Filetype Excel XLS | Posted on 09 Aug 2022 | 3 years ago
Partial file snippet.
Sheet 1: 1 - Control Menu
Financial Analysis & Forecasting

Prepared by Matt H. Evans, CPA, CMA, CFM



Purpose of Spreadsheet:

Revised: 11/22/2002
Home - Active Worksheet active General Input Wksh2






Balance Sheet Wksh3 Income Statement Wksh4
To illustrate concepts related to financial analysis and forecasting.




Cash Flow Statement Wksh5 Key Financials Wksh6
The financial analysis uses a combination of ratios and industry averages to




Ratio Analysis Wksh7 Benchmark Analysis Wksh8
evaluate the financial performance of the company. Trend line graphs are also




Horizontal Analysis Wksh9 Vertical Analysis Wksh10
generated, comparing the company's performance with the industry averages.




Pro Forma Financials - Simple Projection Model Wksh11 Pro Forma Financials - Linear Trend Model Wksh12
Finally, the historical information is used to prepare a set of pro forma




Pro Forma Financials - Exponential Smoothing Wksh13 Scenario Analysis Wksh14
financial statements using both linear and non-linear functions.




Budget Analysis Wksh15 Final Budgets Wksh16








Required Inputs:














You will need to collect financial statements for several reporting periods. If you






want to benchmark the performance against the industry, then you will also need






to collect industry averages. The spreadsheet is setup to capture five reporting






periods (annual, quarterly, monthly). All input fields are highlighted in yellow.






For best results, SEC Filings are suggested since these reports provide more






detail than published financial statements.














Note: A small red triangle in the upper right corner of a cell indicates that a comment has






been inserted. Point your mouse over the cell and the comment will appear.



Comment boxes are used to describe accounts, ratios, and other information used in this spreadsheet! Whenever you see a small red triangle, point your mouse over this cell for additional information.










If a cell appears in red, this indicates a warning concerning a calculation.














Worksheets:














This spreadsheet consists of the following worksheets, divided into three sections:














A) Input Worksheets for financial analysis using historical data:















Worksheet Title Purpose

2 General Input Enter general information here - used on several worksheets.




3 Balance Sheet Enter comparative balances sheets for up to five periods.




4 Income Statement Enter comparative income statements for up to five periods.




5 Cash Flow Statement Enter comparative cash flow statements for up to five periods.













Caution: If you enter less than five years of historical information, certain worksheet






formulas may have to be revised.












B) Output Worksheets for evaluating financial performance:







6 Key Financial Data Calculates key financial information for further analysis.




7 Ratio Analysis Calculates a series of ratios for further analysis.




8 Benchmark Analysis Compare ratio analysis to industry averages.




9 Horizontal Analysis Horizontal analysis with corresponding trend lines.




10 Vertical Analysis Common size financials in percentages and graphs.











C) Pro Forma / Forecasted Financials for Budgeting:















11 Pro Forma - Simple Set of pro forma financials using simple assumptions




12 Pro Forma - Regression Set of pro forma financials using linear trending




13 Pro Forma - Exponential Set of pro forma financials using exponential smoothing




14 Scenario Analysis Example of Scenario Analysis and Goal Seek Analysis




15 Budget Analysis Preliminary budget analysis




16 Final Budgets Set of budgets per various assumptions and forecasts.












Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheet






due to the running of Solver.





Macros:














No macros have been used in this spreadsheet to give everyone some assurance that no viruses






are contained in the spreadsheet. However, you are free to add your own macros to save time.






Tools > Macro > Record New Macro














Excel Functions:














This spreadsheet uses certain financial functions (such as =TREND) which might not be






found in your version of Microsoft Excel. To take full advantage of financial and statistical






functions, you should install the Add On package titled: Analysis TookPak. Go to the main






tool bar, select Tools => Add-Ins => check the Analysis TookPak option, insert your






Excel CD and install the Analysis ToolPak. Also, you might want to install the Solver






Add-in since this is useful for solving special forecasting issues (such as finding the






optimal exponential factor).














Compatibility:














This spreadsheet was created with Microsoft Excel 2000. Older versions of Excel (such as 97)






may not be compatible with this spreadsheet.














Corrections:






With any “attempt” to build an Excel Model, I can easily make some mistakes.






So if you have suggestions to make the model better, drop me an email






and I’ll be glad to improve the financial model. My email address is:



matt@exinfm.com


Sheet 2: 2 - General Input

General Input Panel





Return to 1st Worksheet Home General Input active

The following general information should be entered:





Balance Sheet Wksh3 Income Statement Wksh4

Note: Sample data has been entered in the input cells to help you get started.





Cash Flow Statement Wksh5 Key Financials Wksh6








Ratio Analysis Wksh7 Benchmark Analysis Wksh8
2-1 Name of Company =>
X Y Z Corporation USA

Horizontal Analysis Wksh9 Vertical Analysis Wksh10








Pro Forma Financials - Simple Projection Model Wksh11 Pro Forma Financials - Linear Trend Model Wksh12
2-2 Reporting Periods =>
Annual (Annual, Semi-annual, Quarterly or Monthly)


Pro Forma Financials - Exponential Smoothing Wksh13 Scenario Analysis Wksh14








Budget Analysis Wksh15 Final Budgets Wksh16
2-3 Number of Days in Reporting Period are

365














What reporting periods will be entered?

















2-4 Most Current Period
2000 (1999, July 1998, 6/30/97, etc.)




2-5 Previous Period
1999 (1999, July 1998, 6/30/97, etc.)




2-6 2nd Previous Period
1998 (1999, July 1998, 6/30/97, etc.)




2-7 3rd Previous Period
1997 (1999, July 1998, 6/30/97, etc.)




2-8 4th Previous Period
1996 (1999, July 1998, 6/30/97, etc.)














2-9 Number of historical periods to be analyzed


5














How are the amounts expressed in the financial statements?








(such as: in millions of dollars, thousands of Canadian dollars, etc.)







2-10 millions of dollars

































































Sheet 3: 3 - Balance Sheet

Home Balance Sheet for









>> > X Y Z Corporation USA











<< < millions of dollars







End
Annual Annual Annual Annual Annual









Period Period Period Period Period







Ref Description 1996 1997 1998 1999 2000





















Current Assets













3-1 All cash on hand and any deposits that can be converted into cash. Restricted Cash should be Other Current Assets Cash and Cash Equivalents 990 950 901 998 870






3-2 Can be held long-term, but is held for the purpose of earning a return on idle cash. Short Term Marketable Securities 10 15 12 6 11






3-3 Receivables from sales of products on account. Notes Receivalbe should be reported in Other Current Assets for accurate ratio analysis. Accounts Receivable 1,020 1,550 1,830 2,250 3,040






3-4 Assets held for sale in ordinary business. All cost such as storage, handling, etc are part of inventory. There forms of inventory are Raw Materials, Work in Progress, and Finished Inventory. Inventory 1,005 1,360 1,650 1,900 2,060






3-5 All other current assets not otherwise categorized, such as prepaid items. Other Current Assets 870 1,150 1,370 1,650 1,530






3-6 Total Current Assets 3,895 5,025 5,763 6,804 7,511





















NonCurrent Assets 3-7 Noncurrent tangible assets used in business operations, such as equipment, vehicles, machinery, land, etc. Fixed Assets 14,006 17,605 21,826 26,950 28,100






3-8 Cumulative depreciation taken on fixed assets. Accumulated Depreciation (1,280) (1,700) (2,100) (2,550) (3,010)






3-9 Net Fixed Assets 12,726 15,905 19,726 24,400 25,090






3-10 All longterm investments at cost. Longterm Investments 360 320 120 590 905






3-11 Investments under the Equity Method in other companies Investments in Other Companies 65 0 0 250 412






3-12 All other non current assets, such as goodwill, trademarks, etc. Intangibles and Other Assets 100 110 105 135 195








Total Non Current Assets 13,251 16,335 19,951 25,375 26,602







3-13 Total Assets 17,146 21,360 25,714 32,179 34,113
















Currrent Liab 3-14 Payables related to normal trade transactions with suppliers, vendors, etc. Does not include notes payable. Accounts Payable 2,050 3,150 3,290 3,870 4,800






3-15 Short term loans, notes payable, commercial paper, etc. Short Term Borrowings 1,200 1,830 2,580 3,100 3,550






3-16 Portion of longterm debt that will come due in the current period. Short Term Portion of LT Debt 12 15 25 30 36






3-17 All other current liabilities such as taxes payable, advances from customers, etc. Other Current Liabilities 1,050 1,250 1,480 1,590 1,301






3-18 Total Current Liabilities 4,312 6,245 7,375 8,590 9,687






















3-19 Longterm bank loans, bonds, mortgages, capital lease obligations, etc. Longterm Debt / Borrowings 1,160 1,750 2,600 3,600 3,950







3-20 All other noncurrent liabilities, such as pensions, product warranties, and future obligations recognized. Other Longterm Liabilities 650 750 701 890 995








Total Non Current Liabilities 1,810 2,500 3,301 4,490 4,945







3-21 Total Liabilities 6,122 8,745 10,676 13,080 14,632





















Equity 3-22 Total Equity from issuing preferred stock. Preferred Equity 0 0 0 0 0






3-23 Common stock equity at par value. Common Equity 2,044 2,005 2,069 2,090 2,120






3-24 Common Equity above par value. Additional Paid in Capital 5,013 4,900 5,159 5,626 5,628






3-25 Transfer of earnings from various periods less dividends paid to shareholders. Retained Earnings 5,097 7,050 9,840 15,050 20,005






3-26 Foreign currency translations accounted for under the Current Rate Method are reported in the Balance Sheet. Foreign currency translations accounted for under the Temporal Method are reported in the Income Statement. Adj for Foreign Currency Transl 275 120 (550) (2,147) (6,722)






3-27 Stock buybacks and held in treasury at purchase cost. Reported as a negative amount to equity. Treasury Stock (1,405) (1,460) (1,480) (1,520) (1,550)






3-28 Total Shareholder Equity 11,024 12,615 15,038 19,099 19,481























Total Liabilities & Equity 17,146 21,360 25,714 32,179 34,113






















3-29 Make sure your Assets + Liabilities balance out with your total Equity. If not, the cell will appear in red with the out-of-balance amount. Check: Assets = Liab + Equity ? 0 0 0 0 0








Comment => Balances Balances Balances Balances Balances




































Additional Information 3-30 Isolate any nondepreciable fixed assets for standardization, such as land or work in progress. NonDepreciable Fixed Assets 0 0 0 0 0






3-31 Arises from timing differences between financials and tax return. Deferred Taxes 112 101 90 98 109






3-32 Goodwill Write Off 0 0 0 0 0






3-33 Average number of common shares outstanding. Rounded to fit with the Income Statement. If no major issues of stock and/or retirements took place during the period, then it's OK to use year-end outstanding shares. No of Common Shares o/s 1,320 1,290 1,302 1,345 1,322






3-34 Par Value of Common Stock $10.00 $10.00 $10.00 $10.00 $10.00






3-35 No of Preferred Shares o/s 0 0 0 0 0






3-36 Par Value of Preferred Stock











3-37 Fair market value (price) of stock at end of period. Market Price of Common Stock $22.65 $28.90 $37.05 $33.60 $29.40






3-38 Fair market value (price) of preferred stock at the end of the period. Market Price of Preferred Stock $0.00 $0.00 $0.00 $0.00 $0.00






3-39 Preferred dividends in arrears based on par value of preferred x dividend rate x shares outstanding x number of years in arrears. Preferred Dividends in Arrears 0 0 0 0 0






3-40 Par value of preferred stock plus any premium that must be paid to retire preferred stock. Liquidating value of Preferred Stk 0 0 0 0 0






3-41 Total Equity (Net Assets) less any preferred stock dividends in arrears and the liquidation value of outstanding preferred stock. Book Value per Share $8.35 $9.78 $11.55 $14.20 $14.74






3-42 Dividends per Common Share $1.01 $1.49 $1.89 $1.75 $1.76






3-43 Dividend Payout Ratio 45.47% 38.61% 39.44% 29.76% 30.24%






3-44 All dividends declared for the reporting period on preferred stock. Cash Dividends to Preferred Stock 0 0 0 0 0






3-45 All dividends declared and appropriated for the reporting period on common stock. Dividends actually paid per the Cash Flow Statement have been used to simplify the analysis. Cash Dividends to Common Stock 1,330 1,918 2,461 2,354 2,329






3-46 Total Dividends Paid 1,330 1,918 2,461 2,354 2,329







The words contained in this file might help you see if this file matches what you are looking for:

...Sheet control menu financial analysis amp forecasting prepared by matt h evans cpa cma cfm purpose of spreadsheet revised home active worksheet general input wksh balance income statement to illustrate concepts related and cash flow key financials the uses a combination ratios industry averages ratio benchmark evaluate performance company trend line graphs are also horizontal vertical generated comparing s with pro forma simple projection model linear finally historical information is used prepare set exponential smoothing scenario statements using both nonlinear functions budget final budgets required inputs you will need collect for several reporting periods if want against then setup capture five annual quarterly monthly all fields highlighted in yellow best results sec filings suggested since these reports provide more detail than published note small red triangle upper right corner cell indicates that comment has been inserted point your mouse over appear boxes describe accounts o...

no reviews yet
Please Login to review.