Fin Plan A2 Template A3 A4 A5 A6 A7 A8 A9 WK 2 Introduction 1500000 Week 2 Application Week 2 Introduction to Budgeting Templates Provided Wk 2 Application Worksheet Instructions: Complete following...

Fin Plan
                                                                                A2    Template                            A3                        A4                        A5                        A6                        A7                    A8                        A9
    WK 2    Introduction             1500000    Week 2 Application                                                            Week 2
        Introduction to Budgeting        Templates Provided                                                            Wk 2 Application Worksheet Instructions: Complete following action steps and post adjustments to 5 YR Plan worksheet (Scroll to Right)    Week 2 Financial Plan Adjustments                                Week 3 Financial Plan Adjustments                        Week 4 Financial Plan Adjustments                        Week 5 Financial Plan Adjustments                        Week 5 Financial Plan Adjustment                        Week 6 Financial Plan Adjustments                    Week 7 Financial Plan Adjustments                        Final Adjusted 5 YR Financial Plan
                    1800000    5 Year Financial Plan (Baseline Projections Provided to Students )                                                        $ =Increase (Decrease) to Base Line Plan                                     Capital Projects                         Profit Center "What If Analysis"                        Cost Center "What If Analysis"                        Monthly Allocation and Cash Flows                        Final Accuracy Review                    Financial Management Adjustments
                C4                                                                                                                                                    Depreciation Adjustment Made
                    1850000                Prior Year    Prior Year    Prior Year    Cu
ent YR    Budget    Budget    Budget     Budget                Cu
ent YR    Budget    Budget    Budget     Budget                Cu
ent YR    Budget    Budget    Budget     Budget        Cu
ent YR    Budget    Budget    Budget     Budget        Cu
ent YR    Budget    Budget    Budget     Budget        Cu
ent YR    Budget    Budget    Budget     Budget        Cu
ent YR    Budget    Budget    Budget        Cu
ent YR    Budget    Budget    Budget     Budget        Cu
ent YR    Budget    Budget    Budget     Budget
                        Income Statement        Measures    -3    -2    -1    Forecast    1    2    3    4                Forecast    1    2    3    4                Forecast    1    2    3    4        Forecast    1    2    3    4        Forecast    1    2    3    4        Forecast    1    2    3    4        Forecast    1    2    4        Forecast    1    2    3    4        Forecast    1    2    3    4
        In Week 2, Students will learn about the review and validation of 5 Year Plan Financial Projections, which is the starting point for the budget process. The students will also discuss some of the behavioral and organizational issues that affect quality of financial planning and budgeting, and then apply change management best practices that address the underlying issues. After the 5 Year Plan is validated, the Focus will turn to YR 1 of the budget.        The template provided shows Baseline 5 Year Financial Plan with full view of the primary Profit/Loss Line items, that will be used by the student for a comprehensive review and validation of Revenue Growth Assumptions and mathematical accuracy. Validation of financial projections normally includes direct discussions with Division and Department management who were involved in the development of the Base Line Financial Plan. The Student will be required to complete a detail analysis of certain key assumptions and make co
ections to the 5 Year Plan. Subsequent assignments will require special analysis and further adjustments to the base line 5 Year Plan.        Net Revenue                                                    1-Apple Press capacity is maxed out. Reduce Prepared Product Growth in Budget YR 1-4, to zero. Need to develop capital project plan in Week 3.
                    5,150,000        Prepared Apple Products    $    5,300,000    5,700,000    6,325,000    6,621,468    6,886,327    7,161,780    7,448,251    7,746,181                $ - 0    $ - 0    $ - 0    $ - 0    $ - 0                                                                                                                                                            6,621,468    6,886,327    7,161,780    7,448,251    7,746,181
                                Growth %    2.91%    7.02%    9.88%    4.48%    4.00%    4.00%    4.00%    4.00%                                                                                                                                                                                            4.69%    4.00%    4.00%    4.00%    4.00%
                            Pick Your Own Apples    $    1,900,000    2,000,000    2,190,000    2,273,180    2,273,180    2,273,180    2,273,180    2,273,180            2- Pick-Your-Own Apple Projections need to be validated in Special Analysis(See step 6 below )    $ - 0    $ - 0    $ - 0    $ - 0    $ - 0                                                                                                                                                            2,273,180    2,273,180    2,273,180    2,273,180    2,273,180
                                Growth %    5.56%    5.00%    8.68%    3.66%    0.00%    0.00%    0.00%    0.00%                                                                                                                                                                                            3.80%    0.00%    0.00%    0.00%    0.00%
                            Community Events    $    1,900,000    2,000,000    2,075,000    2,110,560    2,131,666    2,152,982    2,174,512    2,196,257            3- Community Events growth is not sustainable. Reduce growth to zero. Need to research other growth options as part of SWOT Analysis     $ - 0    $ - 0    $ - 0    $ - 0    $ - 0                                                                                                                                                            2,110,560    2,131,666    2,152,982    2,174,512    2,196,257
                                Growth %    2.70%    5.00%    3.61%    1.68%    1.00%    1.00%    1.00%    1.00%                                                                                                                                                                                            1.71%    1.00%    1.00%    1.00%    1.00%
                            Total Net Revenues    $    9,100,000    9,700,000    10,590,000    11,005,208    11,291,172    11,587,942    11,895,943    12,215,618            4-Write a 1-2 page written document that justifies the adjustments made to the 5 YR Baseline. Attach copy of completed Apple Orchard Yield Analysis. Refer to SWOT Analysis for management direction on new growth opportunities.    $ - 0    $ - 0    $ - 0    $ - 0    $ - 0                                                                                                                                                            11,005,208    11,291,172    11,587,942    11,895,943    12,215,618
                                Growth %    76.70%    6.19%    9.18%    3.92%    2.60%    2.63%    2.66%    2.69%                                                                                                                                                                                            3.92%    2.60%    2.63%    2.66%    2.69%
                            Cost of Sales        7,780,500    8,245,000    8,895,600    9,106,901    9,356,995    9,616,833    9,885,529    10,165,838                        - 0    - 0    - 0                                                                                                                                                            9,106,901    9,356,995    9,616,833    9,885,529    10,165,838
                                %    85.50%    85.00%    84.00%    82.75%    82.87%    82.99%    83.10%    83.22%            5- Check all worksheet formulas and totals to assure that Adjustment made to 5 YR plan are properly posted and sum up co
ectly to Adjusted Final Plan Totals. Perform critical analysis to assure financial data and business facts are in sync. Create a list of at least 5 critical questions about the Year Plan that need to be address before the Five Plan Plan is finalized.                                                                                                                                                                                82.75%    82.87%    82.99%    83.10%    83.22%
                            Gross Profit        1,319,500    1,455,000    1,694,400    1,898,307    1,934,177    1,971,109    2,010,414    2,049,780                                                                                                                                                                                            1,898,307    1,934,177    1,971,109    2,010,414    2,049,780
                                GP %    14.50%    15.00%    16.00%    17.25%    17.13%    17.01%    16.90%    16.78%                                                                                                                                                                                            17.25%    17.13%    17.01%    16.90%    16.78%
                            Administrative Cost        850,000    900,000    1,000,000    1,075,000    1,100,000    1,125,000    1,150,000    1,175,000                                                                                                                                                                                            1,075,000    1,100,000    1,125,000    1,150,000    1,175,000
                            Interest on debt        215,000    215,000    215,000    215,000    215,000    215,000    215,000    215,000                                                                                                                                                                                            215,000    215,000    215,000    215,000    215,000
                            Depreciation        - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0                                                                                                                                                                                            - 0    - 0    - 0    - 0    - 0
                            Net Income Before Taxes        254,500    340,000    479,400    608,307    619,177    631,109    645,414    659,780                        - 0    - 0    - 0                                                                                                                                                            608,307    619,177    631,109    645,414    659,780
                            Income taxes     40%    101,800    136,000    191,760    247,432    247,671    252,444    258,166    263,912                        - 0    - 0    - 0                                                                                                                                                            243,323    247,671    252,444    258,166    263,912
                            Net Income         152,700    204,000    287,640    360,875    371,506    378,665    387,248    395,868                        - 0    - 0    - 0                                                                                                                                                            364,984    371,506    378,665    387,248    395,868
                            Net Income    %    1.68%    2.10%    2.72%    3.28%    3.29%    3.27%    3.26%    3.24%                                                                                                                                                                                            12.16%    11.01%    10.09%    9.35%    8.73%
                            Return on Equity        7.10%    8.67%    10.89%    12.02%    11.01%    10.09%    9.35%    8.73%
                                                                            Template
                                                                            A10
                C5        Apple Orchard Land Yield Forecast            June YTD    July    Aug    Sept    Oct    Nov    Dec    Total YR Forecast            6. Apple Orchard Productivity is reliant on suitable acreage and tree planting density. The student needs to review the apple yield plan for reasonableness and accuracy and update the 5 YR Financial Plan for Budget YRS 1-4
                            Available Acres    Acres    6,000    6,000    6,000    6,000    6,000    6,000    6,000    6,000
                            Acres Planted    Acres    4,000    4,000    4,000    4,000    4,000    4,000    4,000    4,000
                            Optimal Tree Density =700    Trees/Acres    350    350    350    350    350    350    350    350
                Validation of the Plan will require in-depth analysis of financial projections. To illustrate the application of Financial Analysis, that student will be asked to perform a special analysis of Anthony's Orchard apple harvest yield potential, and then compute the revenue growth potential for the 5 Year Plan. If the analysis is materially different than the Base Line Revenue Projection, a co
ection needs to me made to the base year financial plan. This type of analysis is normally done with the full support of operations management, since achievement of financial goals will ultimately become the primary responsibility of Operations and Sales Department Management.                Yield Table                                            Apple Orchard Yield and Revenue Plan     Yield = Bins / Acres
                        Brae Burn     Age and Yield Rate Potential     Bins / Acre                                                 Age of Trees    Yield        Budget    Budget    Budget    Budget
                        Total Acres Planted By Age Category     1 YR Old     0    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0            Budget Year >            Forecast    YR 1    YR 2    YR 3    YR 4
                            2 YRS Old     0    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0            Braeburn Apple Bin Yield Capacity will increase each year as new orchard reaches full maturity.    YR 1    0    - 0    - 0
                            3 YRS Old     5    2,000    2,000    2,000    2,000    2,000    2,000    2,000    2,000                YR 2    0    - 0    - 0
                            4 YRS Old     12    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0                Cu
ent YR 3    5    10,000    - 0
                            5 + YRS Old -    25    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0            2000 acres of Braeburn Apples have been planted.    YR 4    12    - 0    24,000
                            Total Acres Planted        2,000    2,000    2,000    2,000    2,000    2,000    2,000    2,000                YR 5    25    - 0        50,000    50,000    50,000
                            Projected Yield / Month    Bins    - 0    - 0    1,800    1,800    1,800    1,800    1,800    9,000                        - 0
                                Yield Table                                            Brae Burn Total Yield Potential            10,000    24,000    50,000    50,000    50,000
                        Honey Crisp     Age and Yield Potential     Bins/Acre
                        Total Acres Planted By Age Category     1 YR Old     0    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0            Budget Year >            Forecast    YR 1    YR 2    YR 3    YR 4
                            2 YRS Old     0    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0            Honey Crisp Apple Bin Yield Capacity will increase each year as new orchard reaches full maturity.    YR 1    0    - 0    - 0    - 0    - 0
                            3 YRS Old     5    2,000    2,000    2,000    2,000    2,000    2,000    2,000    2,000                YR 2    0    - 0    - 0    - 0    - 0
                            4 YRS Old     12    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0                Cu
ent YR 3    5    10,000    - 0    - 0    - 0
                            5 + YRS Old -    25    - 0    - 0    - 0    - 0    - 0    - 0    - 0    - 0            2000 acres of Honey Crisp have been planted.    YR 4    12    - 0    24,000    - 0    - 0
                            Total Acres Planted        2,000    2,000    2,000    2,000    2,000    2,000    2,000    2,000                YR 5    25    - 0    - 0    50,000    50,000    50,000
                            Projected Yield / Month    Bins            1,700    1,700    1,700    1,700    1,700    8,500            Adjustment for Actual Yield            1,625
                            Total Yield / Month    Bins    - 0    - 0    3,500    3,500    3,500    3,500    3,500    17,500            Honey Crisp Total Yield Potential            11,625    24,000    50,000    50,000    50,000
                                                                            Total Yield Potential - All Apples             21,625    48,000    100,000    100,000    100,000
                                                                            Allocation of Production:    Units
                                                                            Loss and Waste Allowance %    %        19%    15%    15%    15%    15%
                                                                            Product Losses    Bins        4,139    7,200    15,000    15,000    15,000
                                                                            Prepared Product (Cu
ent Facility)    Bins        - 0    10,500    10,500    10,500    10,500
                                                                            Prepared Product (New Facility 10,500 YR YR 2-4)    Bins            8,800    10,500    10,500    10,500
                                                                            Community Events - (Max 4,000 Bins)    Bins        - 0    4,000    4,000    4,000    4,000
                                                                            You Pick - (25% Max for Budget Yr 2-4 )    Bins    25%    17,486    17,500    25,000    25,000    25,000
                                                                            Excess Yield Available for Other Uses    Bins        - 0    - 0    35,000    35,000    35,000
                                                                            You Pick Revenues based on Yield Analysis    You Pick Bin Price    $ 130.00    2,273,180    2,275,000    3,250,000    3,250,000    3,250,000
                                                                            5 Year Plan Budget Sales - Baseline            2,273,180    2,273,180    2,273,180    2,273,180    2,273,180
                                                                            5 Year Plan Co
ection            - 0    1,820    976,820    976,820    976,820
                                                                            General Comments: Cu
ent Year Harvest is primarily used for You-Pick sales. Other Cu
ent Year apple requirements were purchased at market prices. For Plan Year 1-4, all apple product and production needs will come directly from Anthony's Orchard Harvest. Management estimates that 25% of annual apples harvest can be used for U-Pick sales. As the Orchard matures, Management will purchase and develop new orchard properties.
                            Business Case: Anthony's Orchard management uses this template for evaluating the financial feasibility of orchard land acquisitions. A proposal to purchase additional land is subject to very strict criteria that involve surveys, soil testing, and title search that assures that the property is suitable for apple orchard use free of any liens or legal restrictions. The initial financial analysis presented below supports a "No Decision".                                                        Template
    Week 3    Capital Budgeting        Templates Provided                                                                    A11
        Introduction
        In Week 3, you will examine two capital projects for the Anthony's Orchard case study. This scenario allows you to analyze capital budgets, as well as discuss and apply capital project financial models. The application assignment will include a special apple orchard industry fact sheet that includes all relevant business and financial details that you will need for completing a capital project analysis and making a recommendation to the Board of Directors.        C6        Capital Project #1                                                    Work Sheet Instructions        Capital Project #2                Compute NPV for a New Apple Press
                For Week 3, a sample Capital Project Work Sheet is provided. Financial formulas are embedded in the worksheet and data is loaded into the template. The NPV formula, selected from Excel Tool Bar, is used to compute the net present value of project cash flows. The 8% Cost of Capital rate is only used for illustrative purposes. If a project generates a positive NPV value, and all other decision criteria is met, the recommendation is to "GO" with the Investment of funds. You will be asked a series of questions that will confirm a basic understanding of Capital Project Analyses.        Proposal to Acquire Land for Apple Orchard Expansion            Year    Year    Year    Year    Year    Year    Year    Yea
                            Timing        0    1    2    3    4    5    Year 6-10 ( Amounts are assumed to be discounted into Year 6 dollars)    11-20            Capital Budgeting        Forecast Line Items        Base Measures    Year    Year    Year    Year    Year    Year    Year    Yea
                                                                Economic Life of Apple Orchard (trees) is assumed to be only 10 year. Residual value of land is considered separately.            1. For Capital Project #1, review the fact sheet 2013 Cost Estimation of Establishing a Cider Apple Orchard in Western Washington and Capital Project #1 Financial Analysis (provided in columns F-P) related to acquisition of additional land for expansion of Apple Orchard business operations. Then perform a critical analysis of this capital project and prepare a 2-page executive report to the Board of Directors that defends or challenges the decision proposed by Management. Critical analysis includes a review of the business facts and careful review of the financial information in the completed Capital Projects financial model that is provided in Columns F-P. Make changes (if needed) to the Capital Project financial analysis to support the position being taken to support, or not support, the project.        Timing Of Capital Project        Unit of Measure    Cu
ent YR Forecast    Budget YR 1    Budget Yr 2    Budget YR 3    Budget YR 4    Budget YR 5    6-10    11-20
                            Revenue Forecast    $    0    - 0    - 0    141,750    340,500    1,304,100    6,520,500                        Apple Production Requirement        Bins        10,500    10,500    10,500    10,500    10,500    10,500    Economic Life of Apples Press is 10 Years
                            Operating Expenses    $    0    805,390    299,100    400,700    525,230    793,450    3,967,250                        Cases Produced        $    157,654    160,000    160,000    160,000    160,000    160,000    160,000
                            Operating Income (Loss)    $    0    (805,390)    (299,100)    (258,950)    (184,730)    510,650    2,553,250                        Case Price/ Total Revenue         $42    6,621,468    6,720,000    6,720,000    6,720,000    6,720,000    6,720,000    6,720,000    - 0
                                                                                    Gross Profit        10.75%    711,850    722,442    722,442    722,442    722,442    722,442    722,442    - 0
                            Capital Investment for 100 Acre land Parcel Acquisition                                                        Selling and Distribution         5%    (331,073)    (336,000)    (336,000)    (336,000)    (336,000)    (336,000)    (336,000)    - 0
                            Land Cost and Residual Value    $    (1,350,000)                        1,500,000                        Capital Investment
                            I
igation System (Economic Life of 10 Yrs)    $    (225,000)                                        2. Capital Project Proposal #2 involves the addition of a new apple press. Using the template provided in Columns U-AE, complete the NPV computations and determine the NPV value of the proposed project. For purposes of this case, assume that no additional capital investment, beyond that provided in template, is required. Computations are provided for Budget YR 1. Computations need to be completed for Years 9-10. For purposes of this case application, the capital investment is completed in cu
ent year and apple press operation commence day 1 of Budget Year 1. Based on the information provided and the financial results of NPV calculations, prepared a 2-page executive summary that defends or challenges management’s plan to purchase and install a new apple press.        Land -         $13,500 / Acre    - 0
                            Orchard Machinery, Building, Equipment ( life of 10 Yrs )    $    (153,490)                                                I
igation System        $22,500/Acre    - 0
                            Trellis System (Economic Life of 10 Years)    $    (203,600)                                                Building, Machinery, Equipment        Itemized Estimate    (1,500,000)
                            Total Cost    $    (1,932,090)    - 0    - 0    - 0    - 0    - 0    1,500,000                        Trellis System        $20,366 / Acre    - 0
                            Net Cash flow    $    (1,932,090)    (805,390)    (299,100)    (258,950)    (184,730)    510,650    4,053,250    - 0                    Total Cost        $    (1,500,000)
                                                                                    Net Cash flow        $    (1,119,224)    386,442    386,442    386,442    386,442    386,442    386,442    - 0
                            NPV     $    (78,174)    NPV value is negative and supports a NO-GO decision
                            WACC    %    6%                                                    NPV     $    617,826    NPV should be positive to support a project...