its in the file
Information Name: I Required: Complete the dynamic financial model. Below please find information regarding Projections 1 to 5. 1The overall market size for our products is increasing. Number of units are expected to grow by 13% (round up to the closest unit) every year. However, due to competition, to maintain market share, we can only increase selling price by 4% every year. To roundup a number to 0 decimal place, use the function =ROUNDUP(cell, 0). 2Current cost per unit is $0.80. Our suppliers are facing higher raw ingredient costs. We expect cost per unit to increase by 0.5% every year (i.e., cost per unit for Proj 1 is $0.804). 3Our inventory policy is to hold around 10% (round up to the closest unit) of next year's sales as our desired ending inventory. We use weighted average as our cost flow assumption. Going into Proj 1, we have 104 units with weighted average cost per unit of $0.80. 4We are able to negotiate a credit term with our suppliers of 30 days starting Proj t+1. 5Our customers have a credit term of 30 days. Our allowance for doubtful debt is set at 5% of Gross AR. Write off is 6% of previous year's sales. Allowance for doubtful debt has an eding balance of $6.8 for Hist 0. 6Deferred Revenue/Sales is 7%. 7SG&A/Sales is 11%. Prepaid Expense/SG&A is 3%. 8Other operating expense/Sales is 4.5%. Other Operating Liability/Other Operating expense is 4%. 9Planned CAPEXs are: Proj 1Proj 2Proj 3Proj 4Proj 5 CAPEX750.0850.0980.01,020.01,200.0 10Dep/CAPEX is 0.2 for Proj 1. Design a step function with increment of 0.05 for subsequent years (i.e., Dep/CAPEX for Proj 2 is 0.25) 11Ending Cash Balance/Sales is 15% 12Planned debt issuance and repayment for Long Term Debt are outlined as follow: Proj 1Proj 2Proj 3Proj 4Proj 5 Debt Issuance400.0500.0600.00.00.0 Debt Repayment(200.0)(222.0)(240.0)(250.0)(260.0) Debt Repyament includes both principle repayments and interest payments. 13All interest expense and interest revenue are calculated using average balances. 14Net borrowing cost for Long Term Debt is 1.1%. 15Cash balance is earning an interest of 0.7%. 16Short-term Revolver has a rate of 5.3%. That is, short-term borrowing has a borrowing cost of 5% and short-term investment has a rate of return of 5%. 17Effective tax rate (Income Tax Expense/EBT) is 19%. DTL/Sales is 2%. Accrued income tax/Taxes Owe is 25%. 18Dividend payout (Dividend paid/Net Income) is 18%. 19No additional stock issuance is expected. 20Based on your projections, outline problems that you foresee with the business. is Income Statement -Hist 2-Hist 1Hist 0Proj 1Proj 2Proj 3Proj 4Proj 5Step function Income Statement Sales$800.0$1,040.0 Units400520 Unit Selling Price $2.0$2.0 COGS320.0416.0 Bad Debt Expense16.024.0 SG&A 80.0100.8 Other Operating Expense40.052.0 Depreciation 100.0140.4 EBIT244.0306.8 Interest Expense21.632.3 Interest Revenue3.52.3 Net Interest Expense18.130.0 EBT225.9276.8 Income Tax Expense63.277.5 Net Income$162.7$199.3 Ratios & assumptions SG&A/Sales Other operating expense/Sales Income Tax Expense/EBT (Depreciation expense) bs Balance Sheet -Hist 2-Hist 1Hist 0Proj 1Proj 2Proj 3Proj 4Proj 5 xC1: A - (L + E) = 00.0000.0000.0000.0000.000 C2: NI - Dividends - ∆RE = 0325.8000.0000.0000.0000.000 C3: ∆Cash (BS) = ∆Cash (SCF)(259.967)0.0000.0000.0000.000 xBalance Sheet Cash $500.0$200.0$260.0 AR, net of allowance$100.0$130.0 Inventory$64.0$83.2 Prepaid Expenses$1.8$2.5 PP&E, net$500.0$900.0$1,461.6 Total Assets$1,000.0$1,265.8$1,937.3 xAccounts Payable$0.0$0.0 Deferred Revenue$40.0$52.0 Other Operating Liability$1.6$2.1 Accrued Income Taxes $15.8$19.4 Revolver$95.7$121.6 Long-Term Debt$500.0$450.3$895.6 Deferred Income Tax$16.0$20.8 Total Liability$500.0$619.4$1,111.5 Paid In Capital$500.0$500.0$500.0 Retained Earnings$146.4$325.8 Total Equity$500.0$646.4$825.8 xTotal Liability and Equity$1,000.0$1,265.8$1,937.3 Sales Cash/Sales DTL/Sales Net Income (Paid Dividend) END scf Statement of Cash Flows -Hist 2-Hist 1Hist 0Proj 1Proj 2Proj 3Proj 4Proj 5 Statement of Cash Flows Net IncomeHistorical not required Depreciation ∆Deferred Income Taxes (∆Working Capital) Cash Flows from Operating (Capital Expenditure) Cash Flows from Investing Cash Flows available for Financing (Paid Dividend) (Principle Repayment for Long-Term Debt) Issuance of Long-Term Debt Issuance of revolver (Repayment of Revolver) Cash Flows from Financing ∆Cash Beginning Cash Balance Ending Cash Balance Ratios & Assumptions Dividend/Net Income Net Income Deferred Income Taxes Capital expenditures wc Supporting Schedule for Working Capital -Hist 2-Hist 1Hist 0Proj 1Proj 2Proj 3Proj 4Proj 5 Sales COGS Bad Debt Expense SG&A Other Op Exp Income Tax Expense ∆Deferred Income Taxes Working Capital Balances Gross AR (Allowance) AR, net of allowance Inventory Prepaid Expenses Total Non-Cash Current Assets: Accounts Payable Deferred Revenue Other Operating Liability Accrued Income Taxes Total Non-Cash Current Liabilities: NET WORKING CAPITAL / (DEFICIT) Decrease (Increase) in ∆Working Capital Inventory step function Unit Sales Desired Ending Inventory Units needed (less Beginning Inventory) Units to be purchased Purchase price/ Cost per unit Purchase cost Inventory (in dollar amounts) using Weighted Average Beginning inventory Purchase cost (COGS) Ending Inventory Weighted average price check Ratios and Assumptions number of days365 Days of Payable Days of Sales Outstanding (Gross AR) Allowance/Gross AR write offs/ previous year's sales write offs Bad Debt Expense (implied) Inventory Policy - Desired Ending Inventory as a % of Sales Deferred Revenue/Sales Prepaid Exp/SG&A Other Op Liability/Other Op Exp Accrued Income Tax/Tax Owe Taxes Owe pp&e Supporting Schedule for PP&E(net) and Depreciation -Hist 2-Hist 1Hist 0Proj 1Proj 2Proj 3Proj 4Proj 5step function Beginning PP&E, netHistorical not required Capital expenditures (Depreciation expense) (Asset sales and write-offs) Ending PP&E, net Inputs and Drivers Dep/CAPEX debt Supporting Schedule for Debt and Interest -Hist 2-Hist 1Hist 0Proj 1Proj 2Proj 3Proj 4Proj 5 Long Term DebtHistorical not required Beginning balance Issuance (Principle Repayment) Ending balance Revolver Beginning balance Issuance / (repayment or purchase) Ending balance Interest Calculations Long-term debtAverage Balances Interest Rate Interest Expense RevolverAverage Balances Interest Rate Interest Expense (Interest Income) Total Interest Expense CashBalance Average Balances Interest Rate Interest Revenue Ratios & Assumptions (Debt Repayment) problems Based on your projections, outline problems that you foresee with the business. Please limit your anwers to three bullet points.