Grossmont College Use and Functionality of Trendline Excel Model Report

User Generated

snanenynzrre

Business Finance

Grossmont College

Description

1.Demonstrate mastery of building and using Excel models via a written, comprehensive report.

2.Demonstrate effective writing/communication skills by explaining the use and what-functionality of an Excel model, using clear and concise writing and screenshots where appropriate.

3.To demonstrate proper report writing, including introduction:

* Body, and conclusion, in the context of this financial modeling course using Excel, using APA guidelines. 

In this course, you have learned about and created many Excel models(some simple and some complex). As a final component in this course, you will create a report to showcase 1 of those models. You need to pick 1 model (you solved/created)that you think would be appropriate to show a potential employer. The goal is to impress a potential employer by demonstrating you can create a complex and interesting model.

Consider these recommendations:

•The report should include the following sections:

Title page (use APA format)

Table of contents (brief, which models you chose)

Introduction(what is the reader going to read in the body and why it is important)

Body (model 1and model 2)

Conclusion(brief conclusion about your models and what you learned).

You should include a description of the model (what is it and what it does) how the model might be useful to you, your company, a coworker, your boss, your client. Talk about and demonstrate the “what-if” functionality model has. 

•You should include screenshots of your model to help the reader understand what you are describing(keep the reader engaged).

•Keep in mind you could share this report with the potential future employer to display your knowledge of Excel and your ability to present information in writing.

•Include as much detail as you deemed appropriate for your audience (potential boss). 

The model I chose is Trendline. Please include screenshot of the model I solved.

Unformatted Attachment Preview

Elvis Products International Income Statement For the Year Ended Dec. 31,2020 2021 Sales 4,300.00 Cost of Goods Sold 3,609.11 Gross profit 690.89 Selling and G&A Expenses 334.80 Fixed Expense 100.00 Depreciation Expense 25.00 EBIT 231.09 Interest Expense 76.00 Earning Before Taxes 155.08 Taxes 38.77 Net Income 116.31 *Forecast Notes: Tax Rate Additional Depreciation Interest Rate 25% 5.00 11.70% 2020 3,850.00 3,250.00 600.00 330.30 100.00 20.00 149.70 76.00 73.70 18.43 55.28 25% - 2019 3,432.00 2,864.00 568.00 240.00 100.00 18.90 209.10 62.50 146.60 36.65 109.95 Assets Cash and Equipments Accounts Receivable Inventory Total Current Assets Plan and Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owner's Equity Accounts Payable Short-term Notes Payable Other Current Liabilities Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owner's Equity *Forecast Discretionary Financing Needed Total Accumulated DFN Notes: Net Additions to Plant & Equipment Life of New Equipment New Depreciation (Straight Line) Iteration Elvis Products International Balance Sheet As of Dec. 31,2020 2021* 52.00 444.51 914.90 1,411.40 577.00 191.20 385.80 1,797.20 189.05 225.00 163.38 577.43 424.61 1,002.04 460.00 83.26 769.25 1,771.29 2020 52.00 402.00 836.00 1,290.00 527.00 166.20 360.80 1,650.80 175,200 225,000 140,000 540,200 424,612 964,812 460,000 225,988 685,988 1,650,800 25.91 Deficit 25.91 50000 10 5000 0 2019 57.60 351.20 715.20 1,124.00 491.00 146.20 344.80 1,468.80 145,600 200,000 136,000 481,600 323,432 805,032 460,000 203,768 663,768 1,468,800 Year Sales 2016 1,890,532 2017 2098490 2018 2350308 2019 3432000 2020 3850000 2021 4300000 2022 4825244 2023 5350489 Linear Trend Extrapolation Sales 6,000,000 5,000,000 4,000,000 y = 525245x + 1E+06 R² = 0.9212 3,000,000 2,000,000 1,000,000 0 2016 2017 2018 2019 2020 Cost of Goods 4000000 3500000 y = 0.8583x - 63681 R² = 0.9983 Cost of Goods 3000000 2500000 2000000 1500000 1000000 0 500000 1000000 1500000 2000000 2500000 Sales 3000000 3500000 4000000 4500000 SUMMARY OUTPUT Regression Statistics Multiple R 0.999143049 R Square 0.998286831 Adjusted R Square 0.997715775 Standard Error 35523.07658 Observations 5 ANOVA df Regression Residual Total Intercept Sales 1 3 4 SS 2.20596E+12 3785666909 2.20975E+12 MS F Significance F 2.20596E+12 1748.141 3.01101E-05 1261888970 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% -63680.82471 58134.676 -1.095401731 0.353404 -248691.3096 121329.6601 0.85826407 0.02052734 41.81077863 3.01E-05 0.792936912 0.923591227 Forecasting Formula COGS= intercept + slope (sales) COGS= 63680.82+0.8582*(sales) Lower 95.0% Upper 95.0% -248691.3096 121329.6601 0.792936912 0.923591227 Year 2016 2017 2018 2019 2020 2021 Sales Cost of Goods 1890532 1570200 2098490 1695694 2350308 1992400 3432000 2864000 3850000 3250000 4,300,000 3626855 (63,680.82) 0.85826407 COGS = -63,680.8247 + 0.8582 * (Sales) 3,609,000 17,855 Axis Title Cost of Goods Sales Elvis Products International Statement of Cash Flows For the Year Ended Dec. 31,2020 ($ in 000's) Cash Flows from Operations Net Income 55.28 Depreciation Expense 20.00 Change in Accounts Receivable -50.80 Change in Inventories -120.80 Change in Accounts payable 29.60 Change in Other Current Liabilities 4.00 Total Cash Flows from Operations Cash flows from Investing Change in Plant & Equipment -36.00 Total Cash Flows from Investing Cash Flows from Financing Change in Short-term Notes Payable 25.00 Change in Long-term Debt 101.18 Change in Common Stock 0.00 Cash Dividends Paid to Shareholders -33.06 Total Cash Flows from Financing Net Change in Cash Balance -62.73 -36.00 93.13 -5.60 Month S&P 500 AAPL FCNTX Jul-14 -1.38% 2.87% -1.44% Aug-14 4.00% 7.75% 4.44% Sep-14 -1.40% -1.71% -1.13% Oct-14 2.44% 7.20% 1.48% Nov-14 2.69% 10.60% 2.15% Dec-14 -0.25% -7.19% -0.53% Jan-15 -3.00% 6.14% -1.34% Feb-15 5.75% 10.07% 5.98% Mar-15 -1.58% -3.14% -0.49% Apr-15 0.96% 0.58% -0.84% May-15 1.29% 4.53% 2.16% Jun-15 -1.94% -3.73% -0.29% Jul-15 2.10% -3.29% 3.46% Aug-15 -6.03% -6.62% -5.96% Sep-15 -2.47% -2.18% -2.06% Oct-15 8.44% 8.34% 7.08% Nov-15 0.30% -0.58% 0.64% Dec-15 -1.58% -11.02% -1.33% Jan-16 -4.96% -7.52% -5.71% Feb-16 -0.13% -0.13% -1.18% Mar-16 6.78% 12.72% 5.59% Apr-16 0.39% -13.99% 0.25% May-16 1.80% 7.18% 1.67% Jun-16 0.26% -4.27% -1.51% Jul-16 3.69% 9.01% 4.48% Aug-16 0.14% 2.36% 0.27% Sep-16 0.02% 6.55% 0.43% Oct-16 -1.82% 0.43% -1.67% Nov-16 3.70% -2.15% 0.60% Dec-16 1.98% 4.80% 0.54% Jan-17 1.90% 4.77% 4.37% Feb-17 3.97% 13.37% 3.85% Mar-17 0.12% 4.87% 1.56% Apr-17 1.03% -0.01% 2.82% May-17 1.41% 6.78% 3.59% Jun-17 0.62% -5.72% -0.40% Jul-17 2.06% 3.27% 3.54% Aug-17 0.31% 10.71% 1.61% Sep-17 2.06% -6.02% 0.85% Oct-17 2.33% 9.68% 4.73% Nov-17 3.07% 2.03% 1.59% Dec-17 1.11% -1.52% 0.33% Jan-18 5.73% -1.06% 9.28% Feb-18 -3.69% 6.81% -2.29% Mar-18 -2.54% -5.81% -3.52% Apr-18 0.38% -1.50% 1.20% May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 2.41% 0.62% 3.72% 3.26% 0.57% -6.84% 2.04% -9.03% 8.01% 3.21% 1.94% 4.05% -6.35% 7.05% 13.51% -0.94% 2.80% 20.04% -0.83% -3.05% -18.12% -11.67% 5.52% 4.48% 9.70% 5.64% -12.42% 13.05% 4.06% 0.92% 1.94% 4.51% 0.14% -9.72% 0.71% -7.87% 9.45% 2.39% 2.21% 4.88% -5.72% 6.63% SUMMARY OUTPUT Regression Statistics Multiple R 0.562911877 R Square 0.316869781 Adjusted R Square 0.305091674 Standard Error 0.028959828 Observations 60 ANOVA df Regression Residual Total Intercept X Variable 1 1 58 59 SS MS 0.022563023 0.022563023 0.048642957 0.000838672 0.07120598 Coefficients Standard Error t Stat 0.004736911 0.003832614 1.235948006 0.257316699 0.049609553 5.18683772 Forcasting Formula APPL= Intercept + Slope (S&P500) APPL= 0.004736911 + 0.257316699* (S&P500) F Significance F 26.90328553 2.84496E-06 P-value 0.221460194 2.84496E-06 Lower 95% -0.002934899 0.15801239 AAPL S&P500 and AAPL -0.1 -0.05 0.25 0.2 0.15 0.1 0.05 0 -0.05 0 -0.1 -0.15 -0.2 -0.25 S&P 500 Upper 95% Lower 95.0% Upper 95.0% 0.012408722 -0.002934899 0.012408722 0.356621007 0.15801239 0.356621007 y = 1.2314x + 0.0058 R² = 0.3169 0.05 0.1 SUMMARY OUTPUT Regression Statistics Multiple R 0.605986 R Square 0.367219 Adjusted R Square 0.356309 Standard Error 0.060974 Observations 60 ANOVA df Regression Residual Total SS MS F Significance F 1 0.125137 0.125137 33.65882 2.89E-07 58 0.215633 0.003718 59 0.34077 Coefficients Standard Error t Stat P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% Intercept 0.003844 0.008192 0.469251 0.640649 -0.01255 0.020241 -0.01255 0.020241 X Variable 11.245344 0.214654 5.801622 2.89E-07 0.815666 1.675021 0.815666 1.675021 Forcasting Formula FCNTX = Intercept + Slope (AAPL) FCNTX = 0.003844 + 1.245344* (AAPL) AAPL and FCNTX 0.15 y = 0.2949x + 0.0056 R² = 0.3672 0.1 FCNTX 0.05 -0.25 0 -0.2 -0.15 -0.1 -0.05 0 -0.05 -0.1 -0.15 AAPL 0.05 0.1 0.15 0.2 0.25 li Format Painter = = Merge & Center % . 98 Condition Formattin Clipboard Font Alignment Number SECURITY WARNING Automatic update of links has been disabled Enable Content H25 X X fi B C E F G H 1 J 1 SUMMARY OUTPUT 2 3 Regression Statistics 4 Multiple R 0.999143049 5 R Square 0.998286831 6 Adjusted R Square 0.997715775 7 Standard Error 35523.07658 8 Observations 5 9 10 ANOVA df SS MS Significance F 3.01101E-05 1 2.20596E+12 2.20596E+12 1748.141 12 Regression 13 Residual 14 Total 3 3785666909 1261888970 4 2.20975E+12 15 16 17 Intercept 18 Sales Coefficients -68680.82471 Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 58134.676 -1.095401731 0.353404 -248691.3096 121329.6601 -248691.3096 121329.6601 0.02052734 41.81077863 3.01E-05 0.792936912 0.923591227 0.792936912 0.923591227 0.85826407 19 C 20 21 Forecasting Formula COGS= intercept + slope (sales) 22 COGS= 63680.82+0.8582* (sales) 23 24 25 26 27 MC.. + Cashflows statements Chart 1 Sheet4 Data PF Income Statement Balance Sheet Regressiqp Results Ready EL FEN File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Cut Eg Copy Times New Roman 11 Α Α O A ce Wrap Text General Paste BIU V V Format Painter MA = = = € E Merge & Center $ %, 6.0 00 .00 Insert Delete Forn Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells SECURITY WARNING Automatic update of links has been disabled Enable Content 18 fr C D E F G H J K L M S&P500 and AAPL A B 1 SUMMARY OUTPUT 2 3 Regression Statistics 4 Multiple R 0.562911877 5 R Square 0.316869781 6 Adjusted R Square 0.305091674 7 Standard Error 0.028959828 8 Observations 60 0.3 y = 1.2314x + 0.0058 • R2 = 0.3169 0.2 0.1 AAPL 0.05 0.1 -0.1 df -0.2 -0.3 S&P 500 9 10 ANOVA -0.1 -0-05 SS MS Significance F 12 Regression 1 0.022563023 0.022563023 26.90328553 2.84496E-06 13 Residual 58 0.048642957 0.000838672 14 Total 59 0.07120598 15 16 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 17 Intercept 0.004736911 0.003832614 1.235948006 0.221460194 -0.002934899 0.012408722 -0.002934899 0.012408722 18 X Variable 1 0.257316699 0.049609553 5.18683772 2.84496E-06 0.15801239 0.356621007 0.15801239 0.356621007 19 20 Forcasting Formula APPL= Intercept + Slope (S&P500) 21 APPL= 0.004736911 +0.257316699* (S&P500) 22 23 24 25 26 27 28 20 Sheet4 AAPL ... + Chart 1 S&P500 and AAPL Data Regression Results Cashflows statements Monthly Returns ME Ready amazon W © O Type here to search . 1 FULL inan
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.

1

Financial Performance: Trend Analysis

Student’s First Name, Middle Initial(s), Last Name
Institutional Affiliation
Course Number and Name
Instructor’s Name and Title
Assignment Due Date

2
Introduction
Microsoft Excel is a powerful software for performing financial analysis due to its
efficiency in the sorting, analysis, and visualization of data. Organizing information in the
spreadsheets increases accessibility and therefore, helpful in avoiding errors during the
execution of different functions and formulae. In writing the report, the aim is to discuss
linear trend analysis in Excel using the data obtained from the 2020 financial year of Elvis
Products International. The analysis will perform a regression line to determine the prediction
line and the value of the R-square to determine the goodness of fit before using the trend
function to estimate the values for 2021 to 2023.

Development of the Model in Excel
Like contemporary English, developing a trend in statistical analysis refers to
establishing patterns in a variable basing on historical information. The analyst uses
information collected in different periods for the same variable, and then uses the information
to establish a possible future behavior based on the realized trend. Visualizing the trend is
also beneficial for comparison, and is not subjective during interpretation. For this report,
there was need to construct a regression line basing on the unit time to establish the goodness
of fit, the relevance of time in explaining revenue, and the prediction line for the data. The
simple linear regression line is defined as𝑦 = 𝑎 + 𝑏𝑥 + 𝑒, where a is the intercept, b the
slope, and e the error term. The model run is an intercept model using the sales data as the
dependent variable (y) and time as an independent variable (x). The data is as shown in the
image below.

3

Select the dataset then from the data tab, choose the data analysis command that
enables you to access a dropdown list of various statistical tests that can be used on the
dataset. From the list, select regression to enable the entry of the two ranges comprising the Y
and X variable respectively.

Enter the ranges in the dialogue box according to the data, which is cells ...

Related Tags