Company Selection. The company I choose is BEST BUY Signup Sheet to choose a listed company (First In First Out)Links to an external site. 1) Sign up your company here. None o
1. Company Selection. The company I choose is BEST BUY
Signup Sheet to choose a listed company (First In First Out)Links to an external site.
1) Sign up your company here. None of students share the same company. (First Sign-in, First Take-out)
2) Must choose a US based company that has at least 5years listed in the US stock exchanges from S&P 500 companies, Nasdaq with 3,300, NYSE with 2,400.
3) Recommended to choose a company that has their Inventory (All kinds of manufacturing, tech, industrial, Wholesale and retail, Agriculture…etc.)
4) Do Not choose Apple & Amazon (Wouldn’t get credit since covered in class) and a company that doesn't have inventory for example FINANCIAL SERVICE companies that have way different financial statement structures, such as banks, investment banks, insurance…
5) Download the recent 10Q of your company (You can find them on Investor Relation page in the company website or in SEC)
2. Complete Word part
You will submit a Word file that includes: forecasted financial statement analysis (a list of all the inputs, variables, and explanations of assumptions for the forecasting rationale, AFN or Excess cash, financial ratio analysis)
1) Company Analysis
a. Intro: Business Description & Market Analysis (max. 2 page)
b. Summarize Item 2. Management’s Discussion and Analysis on 10Q (max. 5 page but must include all topics)
2) List of explanations for all the input variables of assumptions to forecast 4 quarters forecasted financial statements
a. Why should this input variables be? How are they obtained? Where is the information? Must be realistic based on research.
b. All inputs MUST be described in Word with clear explanations and citations
3) Analyze and Evaluate forecasted Income Statement, Balance Sheet, and Statement of Cash Flows.
Explain how your input variable assumptions and projections affect to the forecasted financial statements and why the forecasted financial statements should be.
a. Compare and evaluate the I/S items between the current quarter VS. 4 forecasted quarter
b. Compare and evaluate the B/S accounts balance between the current quarter VS. forecasted quarter
c. Explain the business activities in terms of operating, investing, financing, and cash balance.
d. Use as many financial ratios as possible to analyze the forecasted financial statements.
4) Research policies regarding Ethics Sustainability & Responsibility (ESR) and Diversity, Equity, Inclusion & Belonging (DEIB) for the company you've chosen for the Final Project.
This is based on the Hult Academic Policy that mandatorily include ESR and DEIB in the course learnings.
a. Summarize the policies and evaluate them by your own words in a Word file
b. Turnitin % will be checked (significant Turnitin similarity may result in Honor Code Violation under AIC investigation)
c. Two pages of summary and evaluation each for ESR and DEIB.
3. Complete Excel part.
You will also submit an Excel file that includes: quarterly forecasted financial statements and financial ratio analysis.
1) Embed the recent 10Q Income Statement and Balance Sheet in the Excel template.
2) Apply the most reasonable assumptions to input variables and support them through research
3) Create 4 quarters forecasted financial statements for a year (I/S, B/S, and CF/S)
4) Compute the financial ratios to analyze the forecasted financial statements
2022F MFIN1756 FP&A with Excel.
Final Project Assignment (50%).
Forecasted Financial Statements and Analysis Report.
Company Name:
Student Name:
1. Company Analysis
1) Intro: Business Description & Industry Market Analysis
[Instruction. Research on any open resources and summarize with students’ own words. 2page long but not limited, including some figures, if any.]
…
2) Summarize Item 2 (Part I) on 10Q: Management’s Discussion and Analysis
[Instruction. Must include all the topics indicated on Item 2. Summarize each topic within a few sentences by students’ own words. Do not have to paraphrase all the contents. 5page long but not limited, including some figures, if any.]
…
2. List of explanations of all the input variables with assumptions, interpretations, and calculations on the forecasted financial statements.
[Instruction. All input variables, hard number inputs, and calculations on Excel part must be described in Word part. Hard input numbers on each cell on Excel should be explained by Where and Why this number can be applied. (Justified explanation by students’ own words with research citation) The input hard numbers should be explained by How this number is calculated with the clear process.]
1) Forecasted Income Statement
[Each quarter of all the income statemen items must be developed with research support, rather than just using the same % of sales throughout the forecasted quarters. Forecasted financial statements must be reasonable and realistic along with contemporary events and issues of the firm.]
a. Sales
b. Cost of Goods Sold
c. …
d. …
e. …
f. …
…
…
…
2) Forecasted Balance Sheet
[Tips! To get a higher grade, use as many independent assumptions on B/S accounts in each quarter as possible with research support, rather than just using the same % of sales throughout the forecasted quarters. There are many B/S accounts that are not moving along with % of Sales. Forecasted financial statements must be reasonable and realistic along with contemporary events and issues of the firm.]
(1) Assets
a. Cash
b. Marketable Securities
c. Accounts Receivable
d. …
e. …
…
…
(2) Liabilities
a.
b.
c.
…
…
(3) Equities
a.
b.
c.
…
…
3) Forecasted Statement of Cash Flows
[Instruction. Only interpretation is required of Cash Flows in Operating, Investing, and Financing. Interpret the main changes in the components of the activities.]
(1) Cash Flows in Operating
…
(2) Cash Flows in Investing
…
(3) Cash Flows in Financing
…
3. Comparative Analysis with financial ratios
[Instruction. All the ratio sections on Excel part must be described in Word part. What changes will incur in the company for the forecasted period? Interpret and analyze the ratios throughout the quarters.]
1) Liquidity (Safety)
a.
b.
c.
d. …
2) Productivity (Efficiency)
a.
b.
c.
d. …
3) Profitability (Performance)
a.
b.
c.
d. …
4) Insolvency (Leverage)
a.
b.
c.
d. …
5) Dupont ratio analysis (overall interpretation)
6) Additional ratio analysis (at least 4 more)
a.
b.
c.
d. …
4. Research policies regarding Ethics Sustainability & Responsibility (ESR) and Diversity, Equity, Inclusion & Belonging (DEIB) for the company you've chosen for the Final Project.
This is based on the Hult Academic Policy that mandatorily include ESR and DEIB in the course learnings.
a. Summarize the policies and evaluate them by your own words in a Word file
b. Turnitin % will be checked (significant Turnitin similarity may result in Honor Code Violation under AIC investigation)
c. Two pages of summary and evaluation each for ESR and DEIB.
,
APPL Forecast. FP&A
| Final Project. Forecasted Financial Statements and Comparative Analysis | Instruction. (Remove this part once your work is done.) | ||||||||
| Proejct Company: Apple Inc. | (1) Describe all the justifications and explanations of the requirements on the Word. | ||||||||
| Financial Analyst: Paul Ahn, CPA | (2) Research to justify all the hard inputs. Explain with WHY it should be and WHERE you get the info by pasting the URL link as an evidence on Word. | ||||||||
| (3) If calculated, then explan with HOW you compute the outcomes on Word. | |||||||||
| Part I. Assumption Table ($ million, %) | * Tips! To get a higher grade, use as many independent assumptions on accounts in each quarter as possible with research support, | ||||||||
| rather than just using the same % of sales throughout the forecasted quarters. | |||||||||
| 1. Forecasting Assumptions. | 2022 Q3 Actual | 2022 FQ4 | 2023 FQ1 | 2023 FQ2 | 2023 FQ3 | Forecasted financial statements must be reasonable and realistic along with contemporary events and issues of the firm. | |||
| Comprehensive Income Statement items: All the I/S items must be dealt in the Assumption part. | All the I/S items must be distinguished as % of Sales or trend methods and be justified. | ||||||||
| Sales forecasted ($ million) | 82,959 | 88,576 | 129,803 | 96,409 | 86,473 | Sales forecast must be linked to a sales forecasting model and be justified. | |||
| Cost of Sales (% of sales) | -56.7% | 57.0% | 58.0% | 59.0% | |||||
| R&D Exp (% of sales) | -8.2% | -8.5% | -8.5% | -9.0% | -9.0% | ||||
| SG&A Exp (% of sales) | -3.9% | -4.0% | -4.5% | -4.5% | -4.0% | ||||
| Depr. & Amort. (% of fixed assets) | 6.4% | 6.4% | 6.4% | 6.4% | 6.4% | ||||
| Other Income (% of sales) | 0.9% | 1.0% | 1.0% | 1.1% | 1.1% | ||||
| Tax rate (% of income before tax) | -15.7% | -15.7% | -15.7% | -15.7% | -15.7% | ||||
| Dividend (% of net Income, payout ratio) | -19.3% | -20.0% | -15.0% | -25.0% | -27.0% | ||||
| Stock Repurchase Program (% of net income) | -113.1% | 90.0% | -80.0% | 70.0% | 60.0% | ||||
| * Include or delete the I/S items according to the chosen company's I/S | |||||||||
| Balance Sheet accounts: All the B/S accounts with hard inputs or trend methods must be dealt in the Assumption part. | All the B/S accounts must be distinguished as % of Sales or trend methods and be justified. | ||||||||
| Current Marketable Securities ($ million) | 25% | 26% | 27% | 28% | 29% | ||||
| Non-current Marketable Securities ($ million) | 158% | 155% | 150% | 145% | 140% | ||||
| Property, plant and equipment, net | 40,335 | 43,968 | 44,621 | 275 | 45,929 | At least one B/S account must be forecasted by Trend Method and linked to the Assumption part. | |||
| Commercial paper (% annual interest rate) | 2.0% | 2.5% | 2.5% | 3.0% | 3.0% | ||||
| Short-term debt (% annual interest rate) | 2.0% | 2.5% | 2.5% | 3.0% | 3.0% | ||||
| Long-term debt (% annual interest rate) | 3.0% | 3.5% | 3.5% | 4.0% | 4.0% | ||||
| * Include or delete the B/S accounts according to the chosen company's B/S | |||||||||
| Part II. Financial Planning with Forecasted Financial Statements ($ million) | |||||||||
| 1. Income Statement for the quarter ended | % of sales | 2022 Q3 Actual | 2022 FQ4 | 2023 FQ1 | 2023 FQ2 | 2023 FQ3 | |||
| Total net sales | 100% | 82,959 | How gets this forecasted sales, which forecasting model, why this model, is this forecasted sales justifiable in 4 quarters | ||||||
| Less Cost of sales | -57% | (47,074) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| Gross margin | 43% | 35,885 | |||||||
| Less Operating expenses: | |||||||||
| Research and development | -8.2% | (6,797) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| Selling, general, and administrative | -3.9% | (3,266) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| Depreciation & Amortization | -3.3% | (2,746) | must be extracted if I/S doesn't present | ||||||
| Total operating expenses | -15.4% | (12,809) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| Operating Income | 28% | 23,076 | |||||||
| Other Income (Expense, excluding interest) | 0.9% | 709 | still the same % of sales? Then why? could be changed? Then why? | ||||||
| Interest expense | -0.9% | (719) | must extract if I/S doesn't present, must link to all the interest bearing debts | ||||||
| Income before tax | 28% | 23,066 | |||||||
| Less Provision for Income tax | -4% | (3,624) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| Net Income | 23% | 19,442 | |||||||
| (Dividend) | -5% | (3,760) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| (Stock Repurchased) | -27% | (21,991) | still the same % of sales? Then why? could be changed? Then why? | ||||||
| 2. Balance sheet (B/S), as of quarter end | % of sales | 2022 Q3 Actual | 2022 FQ4 | 2023 FQ1 | 2023 FQ2 | 2023 FQ3 | |||
| Current assets: | For the simplicity, plesase assume that the company does not change % of sales on each assets other than Cash. | ||||||||
| Cash and cash equivalents | 33% | $ 27,502 | must indentify if there is excess cash. | ||||||
| Marketable securities | 25% | 20,729 | – 0 | – 0 | – 0 | – 0 | |||
| Accounts receivable, net | 26% | 21,803 | – 0 | – 0 | – 0 | – 0 | |||
| Inventories | 7% | 5,433 | – 0 | – 0 | – 0 | – 0 | |||
| Vendor non-trade receivables | 25% | 20,439 | – 0 | – 0 | – 0 | – 0 | |||
| Other current assets | 20% | 16,386 | – 0 | – 0 | – 0 | – 0 | |||
| Total current assets | 135% | 112,292 | – 0 | – 0 | – 0 | – 0 | |||
| Non-current assets: | |||||||||
| Marketable securities | 158% | 131,077 | – 0 | – 0 | – 0 | – 0 | |||
| Property, plant and equipment, net | 49% | 40,335 | 43,968 | 44,621 | 275 | 45,929 | |||
| Other non-current assets | 63% | 52,605 | – 0 | – 0 | – 0 | – 0 | |||
| Total non-current assets | 270% | 224,017 | 43,968 | 44,621 | 275 | 45,929 | |||
| Total assets | 405% | $ 336,309 | |||||||
| Current liabilities: | For the simplicity, plesase assume that the company does not change % of sales on each liability other than AFN debt | ||||||||
| Accounts payable | 58% | $ 48,343 | – 0 | – 0 | – 0 | – 0 | |||
| Other current liabilities | 59%</td
Collepals.com Plagiarism Free Papers Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers. Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS Why Hire Collepals.com writers to do your paper? Quality- We are experienced and have access to ample research materials. We write plagiarism Free Content Confidential- We never share or sell your personal information to third parties. Support-Chat with us today! We are always waiting to answer all your questions.  All Rights Reserved Terms and Conditions |