if needed, please put formulas in cells to avoid confusion, thanks!
On file 113042-fgrim35r.xlsx, please show how the values are calculated in the second tab (Poplar template):
How you got ??
|
$
2,499,750.40
|
How you got??
|
(5,233,059.60)
|
Plaese show calculations
|
|
Business Objectives FNCE 390 RECAP QUESTIONS 1The primary goal of finance is to maximize shareholder wealth. YesNo 2The best measure of achieving this goal is market share price because the investor considers the following: - risk inherent in the firm (nature of operations and how financed) - time pattern of operating activity cash flows - time pattern of investing activities to support cash flows - time pattern of financing requirements and composition - perception of future based on historical performance - economic and political factors YesNo 3Present value model is the basis of financial evaluation as it takes into account expected future performance. Expected future performance implies risk - actual performance may vary… Discounts rates, assigned by financial markets, address risk to determine present value. YesNo 4Maximizing shareholder wealth through maximizing market share price does not give rise to potential conflicts with: Management objectives Social responsibility Ethical behaviour YesNo 5The primary difference between a limited company and proprietorship and partnership is the limitation of liability to the company itself versus owners of a proprietorship or partnership YesNo 6Corporate governance incorporates the objective of wealth maximization along with social responsibility, ethics and conflicts of interest YesNo Working capital FNCE 390 Working Capital 1In the perfect world cash receipts from selling activities would be received exactly the same time as related cash payments for products, services, selling, administrative. financing, taxes and dividends. YesNo 2In a perfect world current and long term assets would be matched with the same portion of current and long term debt and equity sources. YesNo 3Financing sources are comprised of short term liabilities, long-term debt, share capital and retained earnings. YesNo 4Working capital is defined as current assets - current liabilities reflecting differences in timing of cash flows relative to 1 above. YesNo 5Cash conversion cycle is the amount of time from when an time is purchased and paid for resale to the time it is sold and payment is received. YesNo 3What financing options do you have for the following asset side of a balance sheet: - Describe as short term debt / long-term debt / equity Current assets Financed by Cash$ 50,000 Accounts receivable1,200,000 Inventory3,000,000 Prepaid expenses150,000 4,400,000 Tangible long lived assets (Net book value) Land 2,500,000 Buildings10,000,000 Equipment9,000,000 21,500,000 Intangible long lived assets Patents and trademarks350,000 Customer lists1,000,000 Goodwill2,000,000 3,350,000 Total Assets$ 29,250,000 Ratios FNCE 390 RECAP QUESTIONS 1Ratio Analysis is best used to identify trends in relation to a benchmark YesNo 2There are two elements of leverage in a business - Operating and Financial Leverage YesNo Operating Leverage concentrates on maximizing net income through volume increases which increase contribution margin and reduce the cost per unit of fixed costs. YesNo Financial leverage involves the mix of debt and equity to increase return on shareholder equity. This is because debt is deductible for tax purposes. YesNo Operating leverage ignores interest and income taxes YesNo 3Operating leverage = Contribution margin / EBIT Contribution margin = (Revenues - Variable costs) * Units EBIT = Contribution margin - Fixed Costs YesNo 4Financial leverage = EBIT / EBT EBT = EBIT - Interest YesNo 5Turnover ratios are useful in managing working capital as they link balance sheet investment with related operating income statement accounts. YesNo 6Debt and equity as a percent of total assets is useful in assessing opportunities regarding financial leverage. YesNo Instructions IIntroduction For several years now you have been working with three "boot-strap" business operations involved in the manufacture of fertilizer and other nutrients. As a "green world" business investor you have recently joined a boutique venture capital firm. You believe in your mission and have introduced to the venture firm partners a project you believe has unlimited potential if these three businesses are combined (your fellow school mates). You have also convinced your school mates that there are many potential benefits by combining the three operations and gaining access to your firms venture capital funding. Believing you, they have asked you to "make it happen " and complete the combination. Your documented project plan and implementation steps are outlined below: The business's will combine effective February 1, 2023 into a single incorporated entity. You will create a February 1, 2023 Opening Balance sheet reflecting the closing of the business combination transaction. The businesses will then transition operations from "as is" to an integrated entity over the next year. The focus will be around reorganizing operations to improve financial performance and getting everyone aligned with strategy. You will develop the forecast income statement for the first year of operations using the assumptions provided for the year ended January 31, 2024 as well as the January 31, 2024 Balance Sheet. The plan is to establish a firm base believing it takes one year to establish operations before heading down the growth plan you have in mind. Financial performance for the first year is based on 2023 combined results of the individual businesses with certain adjustments you expect to see from the formation of the new company. IIProject Plan ACombination process - Entries for Column H 1Complete the column "Combined As Is" Balance Sheet by adding the respective balances of the individual entities. Column H reflects the totals of columns B-D-F 2Complete the column "Combined As Is" Income Statement by adding the respective balances of the individual entities. Column H reflects the totals of columns B-D-F 3Review the "Combined As Is" Ratios (each entity has formulas to work with) Test check accuracy of ratios in Column H BIncorporation process - revaluation adjustments reflect in Column J - new Balance column L 1The incorporation process has been structured such that an incorporated entity created by your firm will acquire the operations of each entity. As such all assets are revalued to their fair market values at the time of acquisition. The fair market values are: CashAll cash balances of original operations paid to founders - Combined cash = $0 Opening Balance Sheet cash = $0 Accounts receivableAccounts receivable revalued 8% higher as no allowance for doubtful accounts InventoryInventory is revalued at 10% lower due to obsolete raw materials Prepaid expensesTransfer $105,000 to Inventory as goods received day of transaction. Deferred revenueReduce by 20% to reflect work completed - record change to venture capital short term loan as is considered a purchase adjustment Landcombined revised market value per appraisal2,200,000 Building combined revised market value of2,450,000 Equipment increase combined market value to975,000 Vehiclesrevised market value380,000 Accumulated depreciationAdjusted all historical balances to a zero balance as assets are revalued and reflect a new starting value Goodwill and intangiblesAdd $420,000 for goodwill Accounts payableDue diligence discovered an additional $400,000 in payables Income taxes payableNone as start-up Deferred tax liabilityAdd $385,000 for tax effect of transaction as deferred tax liability Term loanNo change MortgageNo change Current portion of debtNo change Due to relativesConvert 30% of balance to share capital - repay 70% using venture capital short term loan Proprietorship capitalConvert full balances to share capital Partnership capitalConvert full balances to share capital Share capitalProprietor + Partnership capital + 30% of due from relatives converted Retained EarningsNone as the start-up of new legal entity Venture capital loanBalancing figure of all other balance sheet balances and adjustments C2023-2024 Operations - Operational Changes - reflect in 2023-2024 adjustments column (Column P) 1Revenues will increase by $9,300,000 2Gross profit will be 32% based on 2023-2024 pro forma ending revenues 3Selling costs will increase by 10% of incremental sales 4Administrating costs will increase by 9% of incremental cost of sales 5Amortization will increase by $259,000 per annum (in addition to historical) 6Interest expense will increase based on venture capital short term loan balance at 9% interest rate 7Income taxes will be 26% of combined incomes as now an incorporated entity - split as follows - 30% deferred - 70% current - adjust balance sheet accounts for these amounts 8Adjust final accounts receivable balances to reflect a 45 day collection cycle - difference to cash (use 360 days) 9Adjust final inventory balances to reflect 72 days on hand - difference to cash (use 360 days) 10Adjust final accounts payable balances to reflect a turnover ratio of 45 days - difference to cash (use 360 days) 11Acquired new formulation equipment for $255,000 and financed 100% with a new term loan at end of year. Term loan is repayable over five years and life of equipment will be 10 years. DBalance Sheet Steps - balancing everything 1Columns B through to L will balance at the Balance Sheet level - ignore income statement effects. 2Columns N & P must reflect the income statement changes and will balance by recording the balance amount in cash If you leave cash blank - the figure reflected in line 57 * -1 should work. Once cash adjusted - line 57 = 0 3Column R will balance if 1 and 2 above are balanced E Build the Year 1 cash flow statement to support the change in cash for Year 1 Poplar Template POPLAR LTD.Owner: Margaret LentilPartners: Helen Grass (45%) + Candace Pot (55%)Shareholder: Charles GreenBalance Sheet OpeningAs Is = Combined + Corporate Tax EffectOperational Changes Including Corporate TaxForecast Results Start-up Balance SheetsCanGrow (proprietorship)Bluebis (partnership)BigYield Inc.CombinedRevaluationPOPLAR LTD.First Year Feb 1 2023 - Jan 31 2024AdjustmentsPOPLAR LTD. January 31 2023February 1February 1 202320232023As IsAdjustmentsOpeningOperations2023-2024Jan 31 2024 Assets Current assets Cash$ 95,000$ 125,000$ 65,000$ 285,000$ (285,000)$ - 0$ 2,499,750.40(5,233,059.60)$ (2,733,309)Balancing Figure Accounts receivable1,450,0001,400,0001,025,0003,875,000310,0004,185,000871,2505,056,250 Inventory945,0001,350,000985,0003,280,000(223,000)3,057,0002,444,2005,501,200How you got ??$ 2,499,750.40 Prepaid expenses85,000100,00055,000240,000135,000375,000375,000How you got??(5,233,059.60) 2,575,0002,975,0002,130,0007,680,000(63,000)7,617,0002,499,750(1,917,610)8,199,141Plaese show calculations Property, plant and equipment Land700,000700,000675,0002,075,000125,0002,200,0002,200,000 Buildings240,000650,000495,0001,385,0001,065,0002,450,0002,450,000 Equipment135,000600,000120,000855,000120,000975,000255,0001,230,000 Vehicles75,000220,000100,000395,000(15,000)380,000380,000 1,150,0002,170,0001,390,0004,710,0001,295,0006,005,000- 0255,0006,260,000 Less: Accumulated amortization Accumulated amortization - Buildings(60,000)(145,000)(100,000)(305,000)305,000- 0(196,386)(166,767)(363,154) Accumulated amortization - Equipment(30,000)(90,000)(195,000)(315,000)315,000- 0(78,154)(66,367)(144,521) Accumulated amortization - Vehicles(90,000)(120,000)(40,000)(250,000)250,000- 0(30,460)(25,866)(56,326) (180,000)(355,000)(335,000)(870,000)870,000- 0(305,000)(259,000)(564,001) 970,0001,815,0001,055,0003,840,0002,165,0006,005,000(305,000)(4,000)5,695,999 Goodwill and other intangibles- 0- 0- 0- 0420,000420,000420,000 $ 3,545,000$ 4,790,000$ 3,185,000$