In Workbook 3, we introduced you to our Proprietary Financial Analyst Model (PFAM). It was designed to automatically upload historic company financial statements into a working model. In the next Workbook we use this this as a template to develop more advanced modeling and forecasting skills. Before becoming reliant on this turnkey tool, you will create a basic model from scratch in this Sheet by writing the required Excel logic. You will have to write the logic that appropriately: - Re-creates Accounting Relationships - Replicates Business Relationships - Identifies Formula Drivers Perform this assignment by filling in the Yellow Boxes To answer the question in column H, click on the yellow box to see the possible multiple choice answers and choose the correct answer. You will choose among: Accounting - an accounting relationship Buisness - a business relationship Driver - a application of a business relationship to the independent value The red drivers defaults the value in the current year to that of the previous year A mistake early in the assignment will not impact further answers. You will choose amone: Accounting - an accounting relationship Buisness a business relationship Driver - a application of a business relationship to the independent value The red drivers defaults the value in the current year to that of the previous year A mistake early in the assignment will not impact further answers. 2018 2019 019 2020E Relationship 100 Revenues is driven by the growth rate 110 10% 50 64 Gross Profits is driven by Gross Margin Income Statement Ravenues Growth Cost of Revenues Gross Profits Gross Margin Operating Expenses Opex) OpEx Percent of Revenues EBIT Net Interest income (Expense! Pretax Income Taxes Business Driver Accountine Business Driver Business Driver Accounting 15171 OpEx is driven by Opex Percent Revenues - 555 Interest Expense is expected to be flat Accounting Business Driver Tax Rate Net Income Balance Sheet Cash Accounting 100 15 16 Accounts Receivable Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 20 10 Accounting Business Driver Accounts Receivable is driven by the Days of Sales Outstanding (DSO)/365 Revenues Days of Sales Outstanding (DSO) is Accounts Receivable/Revenues 365 PP&E in a year is equal to the PPLE in the previous year plus Capex in the current year minus Depreciation in the current year. Accounts Payable is driven by the Days of Payables Outstanding (DPO) Days of Payables Outstanding (DPO) is Accounts Payable / Cost of Revenues 365 Equity is equal to the Equity in the previous year plus the Net Income minus Dividends 100 Accountine Accounting Cash Flow Statement Net Income + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (Capex) - Dividends Payout Ratio - Increase (Decrease) in Cash The pampany uses 10 year straight line depreciation (10% of PP&E in the previous year! Change in Accounts Receivable is equal to the Accounts Receivable in a year minus that in the previous year. Change in Accounts Payable is equal to the Accounts Payable in a year minus that in the previous year 18) 110) (10) Accounting Accounting Accounting Business Accountine #DIV/0! DIV/0! DIV/0! In Workbook 3, we introduced you to our Proprietary Financial Analyst Model (PFAM). It was designed to automatically upload historic company financial statements into a working model. In the next Workbook we use this this as a template to develop more advanced modeling and forecasting skills. Before becoming reliant on this turnkey tool, you will create a basic model from scratch in this Sheet by writing the required Excel logic. You will have to write the logic that appropriately: - Re-creates Accounting Relationships - Replicates Business Relationships - Identifies Formula Drivers Perform this assignment by filling in the Yellow Boxes To answer the question in column H, click on the yellow box to see the possible multiple choice answers and choose the correct answer. You will choose among: Accounting - an accounting relationship Buisness - a business relationship Driver - a application of a business relationship to the independent value The red drivers defaults the value in the current year to that of the previous year A mistake early in the assignment will not impact further answers. You will choose amone: Accounting - an accounting relationship Buisness a business relationship Driver - a application of a business relationship to the independent value The red drivers defaults the value in the current year to that of the previous year A mistake early in the assignment will not impact further answers. 2018 2019 019 2020E Relationship 100 Revenues is driven by the growth rate 110 10% 50 64 Gross Profits is driven by Gross Margin Income Statement Ravenues Growth Cost of Revenues Gross Profits Gross Margin Operating Expenses Opex) OpEx Percent of Revenues EBIT Net Interest income (Expense! Pretax Income Taxes Business Driver Accountine Business Driver Business Driver Accounting 15171 OpEx is driven by Opex Percent Revenues - 555 Interest Expense is expected to be flat Accounting Business Driver Tax Rate Net Income Balance Sheet Cash Accounting 100 15 16 Accounts Receivable Days of Sales Outstanding (DSO) PP&E Accounts Payable Days of Payables Outstanding (DPO) Equity 20 10 Accounting Business Driver Accounts Receivable is driven by the Days of Sales Outstanding (DSO)/365 Revenues Days of Sales Outstanding (DSO) is Accounts Receivable/Revenues 365 PP&E in a year is equal to the PPLE in the previous year plus Capex in the current year minus Depreciation in the current year. Accounts Payable is driven by the Days of Payables Outstanding (DPO) Days of Payables Outstanding (DPO) is Accounts Payable / Cost of Revenues 365 Equity is equal to the Equity in the previous year plus the Net Income minus Dividends 100 Accountine Accounting Cash Flow Statement Net Income + Depreciation - Increase in Accounts Receivable + Increase in Accounts Payable - Capital Expenditures (Capex) - Dividends Payout Ratio - Increase (Decrease) in Cash The pampany uses 10 year straight line depreciation (10% of PP&E in the previous year! Change in Accounts Receivable is equal to the Accounts Receivable in a year minus that in the previous year. Change in Accounts Payable is equal to the Accounts Payable in a year minus that in the previous year 18) 110) (10) Accounting Accounting Accounting Business Accountine #DIV/0! DIV/0! DIV/0