excel functions

ExcelFunctionHomework2withoutanswers.xlsx

Home >Business & Finance homework help >Accounting homework help >excel functions

Exercise 1

Refer to the database in A17:E316 below that contains monthly retail sales data since 1992.
Required:
A.	Fill out the table in G18:H43 that shows the relative frequency of the % Change data in Col. E.
	Use the information in G18:H20 to construct the BINs and the Frequency data using the Frequency function in an array. You must use arrays in order to receive credit.
B.	Plot the relative frequency you created in G18:H43 in a Histogram (horizontal bar chart) that shows the relative frequency for each bin size.
	Plot the data in the space J18:S43 (yellow shaded). Label the chart "Frequency Distribution of Monthly Sales Change" and the horizontal axis should be % with 1 decimdal place.
C.	In the space G47:I52 (yellow shaded),
	a. Identify the 5 largest % changes in descending order using the LARGE function
	b. Use the MATCH function to identify the ROW # associated with each of the extracted records.
	c. Use the VLOOKUP function to identify the Year associated with each of ROWs identified.
D.	In the space G55:J82 (yellow shaded), use the Data-Advance Filter command to extract a table that contains only the December monthly records.
E.	From the extracted December sales data in Col. I, provide a FITTED value (forecast) in Col. K using the TREND function in an array.
F.	Again using the TREND function in an array, provide a forecast of the extrapolated Sales data for the Years 2017-2021. Put the forecast in K83:K87.
Row	Year	Month	VALUE	% Change	A.			B.
0	1992	Jan	146,913		Min	-2.5%
1	1992	Feb	147,270	0.2%	Max	2.5%
2	1992	Mar	146,831	-0.3%	Bin Size	0.25%
3	1992	Apr	148,082	0.9%
4	1992	May	149,015	0.6%	BIN	FREQ
5	1992	Jun	149,821	0.5%
6	1992	Jul	150,809	0.7%
7	1992	Aug	151,064	0.2%
8	1992	Sep	152,595	1.0%
9	1992	Oct	153,577	0.6%
10	1992	Nov	153,605	0.0%
11	1992	Dec	155,504	1.2%
12	1993	Jan	157,525	1.3%
13	1993	Feb	156,292	-0.8%
14	1993	Mar	154,774	-1.0%
15	1993	Apr	158,996	2.7%
16	1993	May	160,624	1.0%
17	1993	Jun	160,171	-0.3%
18	1993	Jul	162,832	1.7%
19	1993	Aug	162,491	-0.2%
20	1993	Sep	163,285	0.5%
21	1993	Oct	164,711	0.9%
22	1993	Nov	166,593	1.1%
23	1993	Dec	168,101	0.9%
24	1994	Jan	167,504	-0.4%
25	1994	Feb	169,652	1.3%
26	1994	Mar	172,775	1.8%
27	1994	Apr	173,099	0.2%
28	1994	May	172,340	-0.4%	C.
29	1994	Jun	174,307	1.1%	Largest %	Row #	Year
30	1994	Jul	174,801	0.3%
31	1994	Aug	177,289	1.4%
32	1994	Sep	178,776	0.8%
33	1994	Oct	180,569	1.0%
34	1994	Nov	180,695	0.1%
35	1994	Dec	181,492	0.4%
36	1995	Jan	182,423	0.5%	D.
37	1995	Feb	179,472	-1.6%			<======== Insert Criteria here
38	1995	Mar	180,996	0.8%
39	1995	Apr	181,702	0.4%					E.
40	1995	May	183,543	1.0%	Year	Month	VALUE	% Change	Fitted Value
41	1995	Jun	186,088	1.4%
42	1995	Jul	185,470	-0.3%
43	1995	Aug	186,814	0.7%
44	1995	Sep	187,338	0.3%
45	1995	Oct	186,546	-0.4%
46	1995	Nov	189,052	1.3%
47	1995	Dec	190,809	0.9%
48	1996	Jan	189,167	-0.9%
49	1996	Feb	192,269	1.6%
50	1996	Mar	193,993	0.9%
51	1996	Apr	194,712	0.4%
52	1996	May	196,210	0.8%
53	1996	Jun	196,127	-0.0%
54	1996	Jul	196,229	0.1%
55	1996	Aug	196,215	-0.0%
56	1996	Sep	198,843	1.3%
57	1996	Oct	200,488	0.8%
58	1996	Nov	200,200	-0.1%
59	1996	Dec	201,191	0.5%
60	1997	Jan	202,414	0.6%
61	1997	Feb	204,273	0.9%
62	1997	Mar	204,965	0.3%
63	1997	Apr	203,372	-0.8%
64	1997	May	201,676	-0.8%
65	1997	Jun	204,666	1.5%						F.
66	1997	Jul	207,049	1.2%
67	1997	Aug	207,643	0.3%
68	1997	Sep	208,298	0.3%
69	1997	Oct	208,064	-0.1%
70	1997	Nov	208,982	0.4%
71	1997	Dec	209,379	0.2%
72	1998	Jan	209,684	0.1%
73	1998	Feb	209,532	-0.1%
74	1998	Mar	210,792	0.6%
75	1998	Apr	213,623	1.3%
76	1998	May	214,619	0.5%
77	1998	Jun	216,324	0.8%
78	1998	Jul	214,853	-0.7%
79	1998	Aug	213,669	-0.6%
80	1998	Sep	215,712	1.0%
81	1998	Oct	219,465	1.7%
82	1998	Nov	221,150	0.8%
83	1998	Dec	223,226	0.9%
84	1999	Jan	224,020	0.4%
85	1999	Feb	226,240	1.0%
86	1999	Mar	227,407	0.5%
87	1999	Apr	228,978	0.7%
88	1999	May	231,238	1.0%
89	1999	Jun	231,926	0.3%
90	1999	Jul	233,933	0.9%
91	1999	Aug	236,589	1.1%
92	1999	Sep	237,516	0.4%
93	1999	Oct	237,560	0.0%
94	1999	Nov	240,462	1.2%
95	1999	Dec	245,498	2.1%
96	2000	Jan	243,483	-0.8%
97	2000	Feb	247,133	1.5%
98	2000	Mar	249,847	1.1%
99	2000	Apr	245,789	-1.6%
100	2000	May	246,225	0.2%
101	2000	Jun	248,178	0.8%
102	2000	Jul	247,184	-0.4%
103	2000	Aug	247,601	0.2%
104	2000	Sep	251,812	1.7%
105	2000	Oct	251,267	-0.2%
106	2000	Nov	250,225	-0.4%
107	2000	Dec	250,603	0.2%
108	2001	Jan	252,736	0.9%
109	2001	Feb	252,730	-0.0%
110	2001	Mar	250,346	-0.9%
111	2001	Apr	254,759	1.8%
112	2001	May	255,262	0.2%
113	2001	Jun	254,020	-0.5%
114	2001	Jul	253,015	-0.4%
115	2001	Aug	254,570	0.6%
116	2001	Sep	249,856	-1.9%
117	2001	Oct	268,072	7.3%
118	2001	Nov	260,363	-2.9%
119	2001	Dec	256,552	-1.5%
120	2002	Jan	256,376	-0.1%
121	2002	Feb	257,677	0.5%
122	2002	Mar	257,060	-0.2%
123	2002	Apr	261,320	1.7%
124	2002	May	257,616	-1.4%
125	2002	Jun	259,815	0.9%
126	2002	Jul	262,811	1.2%
127	2002	Aug	264,975	0.8%
128	2002	Sep	260,650	-1.6%
129	2002	Oct	262,011	0.5%
130	2002	Nov	263,559	0.6%
131	2002	Dec	265,802	0.9%
132	2003	Jan	267,276	0.6%
133	2003	Feb	263,199	-1.5%
134	2003	Mar	267,842	1.8%
135	2003	Apr	267,240	-0.2%
136	2003	May	267,347	0.0%
137	2003	Jun	270,432	1.2%
138	2003	Jul	273,393	1.1%
139	2003	Aug	277,901	1.6%
140	2003	Sep	276,413	-0.5%
141	2003	Oct	274,779	-0.6%
142	2003	Nov	278,322	1.3%
143	2003	Dec	277,567	-0.3%
144	2004	Jan	278,884	0.5%
145	2004	Feb	280,966	0.7%
146	2004	Mar	286,232	1.9%
147	2004	Apr	282,971	-1.1%
148	2004	May	288,250	1.9%
149	2004	Jun	284,146	-1.4%
150	2004	Jul	287,341	1.1%
151	2004	Aug	287,909	0.2%
152	2004	Sep	293,177	1.8%
153	2004	Oct	295,055	0.6%
154	2004	Nov	296,178	0.4%
155	2004	Dec	299,766	1.2%
156	2005	Jan	297,187	-0.9%
157	2005	Feb	300,378	1.1%
158	2005	Mar	300,212	-0.1%
159	2005	Apr	304,817	1.5%
160	2005	May	301,773	-1.0%
161	2005	Jun	311,025	3.1%
162	2005	Jul	313,690	0.9%
163	2005	Aug	309,721	-1.3%
164	2005	Sep	310,428	0.2%
165	2005	Oct	310,772	0.1%
166	2005	Nov	313,061	0.7%
167	2005	Dec	313,443	0.1%
168	2006	Jan	322,605	2.9%
169	2006	Feb	320,117	-0.8%
170	2006	Mar	321,527	0.4%
171	2006	Apr	322,458	0.3%
172	2006	May	321,513	-0.3%
173	2006	Jun	323,034	0.5%
174	2006	Jul	324,482	0.4%
175	2006	Aug	325,130	0.2%
176	2006	Sep	322,815	-0.7%
177	2006	Oct	322,651	-0.1%
178	2006	Nov	323,410	0.2%
179	2006	Dec	327,238	1.2%
180	2007	Jan	327,067	-0.1%
181	2007	Feb	328,076	0.3%
182	2007	Mar	331,135	0.9%
183	2007	Apr	329,401	-0.5%
184	2007	May	334,155	1.4%
185	2007	Jun	330,891	-1.0%
186	2007	Jul	332,200	0.4%
187	2007	Aug	333,793	0.5%
188	2007	Sep	335,711	0.6%
189	2007	Oct	337,289	0.5%
190	2007	Nov	340,762	1.0%
191	2007	Dec	336,975	-1.1%
192	2008	Jan	337,560	0.2%
193	2008	Feb	334,452	-0.9%
194	2008	Mar	334,370	-0.0%
195	2008	Apr	336,149	0.5%
196	2008	May	337,699	0.5%
197	2008	Jun	338,439	0.2%
198	2008	Jul	336,507	-0.6%
199	2008	Aug	333,813	-0.8%
200	2008	Sep	327,820	-1.8%
201	2008	Oct	314,779	-4.0%
202	2008	Nov	301,844	-4.1%
203	2008	Dec	293,999	-2.6%
204	2009	Jan	298,881	1.7%
205	2009	Feb	297,862	-0.3%
206	2009	Mar	292,698	-1.7%
207	2009	Apr	293,576	0.3%
208	2009	May	296,569	1.0%
209	2009	Jun	301,817	1.8%
210	2009	Jul	302,569	0.2%
211	2009	Aug	309,095	2.2%
212	2009	Sep	300,839	-2.7%
213	2009	Oct	304,128	1.1%
214	2009	Nov	307,130	1.0%
215	2009	Dec	308,522	0.5%
216	2010	Jan	308,847	0.1%
217	2010	Feb	308,703	-0.0%
218	2010	Mar	316,208	2.4%
219	2010	Apr	318,425	0.7%
220	2010	May	315,804	-0.8%
221	2010	Jun	314,951	-0.3%
222	2010	Jul	315,539	0.2%
223	2010	Aug	317,154	0.5%
224	2010	Sep	320,052	0.9%
225	2010	Oct	324,337	1.3%
226	2010	Nov	327,598	1.0%
227	2010	Dec	329,715	0.6%
228	2011	Jan	332,554	0.9%
229	2011	Feb	334,577	0.6%
230	2011	Mar	337,859	1.0%
231	2011	Apr	339,700	0.5%
232	2011	May	339,347	-0.1%
233	2011	Jun	341,540	0.6%
234	2011	Jul	341,491	-0.0%
235	2011	Aug	341,821	0.1%
236	2011	Sep	345,254	1.0%
237	2011	Oct	348,092	0.8%
238	2011	Nov	349,275	0.3%
239	2011	Dec	349,737	0.1%
240	2012	Jan	352,546	0.8%
241	2012	Feb	357,166	1.3%
242	2012	Mar	358,872	0.5%
243	2012	Apr	356,670	-0.6%
244	2012	May	356,133	-0.2%
245	2012	Jun	352,170	-1.1%
246	2012	Jul	353,792	0.5%
247	2012	Aug	358,186	1.2%
248	2012	Sep	361,676	1.0%
249	2012	Oct	361,819	0.0%
250	2012	Nov	362,976	0.3%
251	2012	Dec	364,795	0.5%
252	2013	Jan	367,271	0.7%
253	2013	Feb	372,037	1.3%
254	2013	Mar	367,505	-1.2%
255	2013	Apr	368,766	0.3%
256	2013	May	369,455	0.2%
257	2013	Jun	371,446	0.5%
258	2013	Jul	373,075	0.4%
259	2013	Aug	372,113	-0.3%
260	2013	Sep	372,451	0.1%
261	2013	Oct	373,768	0.4%
262	2013	Nov	374,229	0.1%
263	2013	Dec	376,820	0.7%
264	2014	Jan	373,424	-0.9%
265	2014	Feb	378,385	1.3%
266	2014	Mar	382,742	1.2%
267	2014	Apr	385,975	0.8%
268	2014	May	386,702	0.2%
269	2014	Jun	387,912	0.3%
270	2014	Jul	387,488	-0.1%
271	2014	Aug	390,329	0.7%
272	2014	Sep	389,129	-0.3%
273	2014	Oct	390,327	0.3%
274	2014	Nov	391,780	0.4%
275	2014	Dec	387,814	-1.0%
276	2015	Jan	385,414	-0.6%
277	2015	Feb	383,343	-0.5%
278	2015	Mar	389,634	1.6%
279	2015	Apr	388,905	-0.2%
280	2015	May	392,927	1.0%
281	2015	Jun	393,079	0.0%
282	2015	Jul	395,117	0.5%
283	2015	Aug	395,446	0.1%
284	2015	Sep	395,317	-0.0%
285	2015	Oct	393,814	-0.4%
286	2015	Nov	395,600	0.5%
287	2015	Dec	396,723	0.3%
288	2016	Jan	394,878	-0.5%
289	2016	Feb	395,126	0.1%
290	2016	Mar	393,775	-0.3%
291	2016	Apr	399,134	1.4%
292	2016	May	399,588	0.1%
293	2016	Jun	402,608	0.8%
294	2016	Jul	403,103	0.1%
295	2016	Aug	402,695	-0.1%
296	2016	Sep	406,879	1.0%
297	2016	Oct	409,881	0.7%
298	2016	Nov	409,813	-0.0%

Exercise 2

1	You borrowed a loan of $40,000 for a luxury car. The term of the loan is 48 months. The interest rate is 8%.
	a.	What is the monthly payment?
	b.	What is the balance of the loan (i.e., principal balance) after you have paid the 35th payment?
	c.	How much interest would you have paid after the 35th payment?
2	You are interested in buying a grocery store as a family business. The asking price is $120,000, in cash. You estimated
	the store will generate net cash flow of Year 1 = $5,000, Year 2 = $10,000, Year 3 = $15,000
	Years 4 & 5 =$20,000, Year 6 = -$10,000 due to remodeling, Years 7 & 8 = $25,000, Years 9 & 10 = $30,000. Enter data in cells E28:F38.
	a.	If your cost of money is 10%, what is the NPV of the proposal over 10 years?
	b.	What does the cost of money need to be in order for the proposal to break even in 10 years?
	c.	What is the project's Modified Internal Rate of Return (MIRR) over 10 years if the financing rate is 10% but the reinvestment
		rate is only 5%?
	d.	If the store continues to generate a net cash flow of $30,000 perpetually after 10 years, what will be the NPV? (cost of money is still 10%)
3	Refer back to Question 1 above. Assume the basic terms are still 8% and 48 months.
	Perform a What-If 2-var. Data Table that gives the monthly payment amount given the combination of the following changing assumptions:
		Interest rate	5%	7%	9%	10%	12%	14%
		Term	12	24	36	48	60	72
Put your answers in the space below. Clearly label the Questions the answers pertaining to.
1	a.
	b.
	c.
2	a.			Year	CF
	b.
	c.
	d.
3	Int
	Term
	Princ

Exercise 3

You are interested in buying a business for $40,000 upfront. You want to make sure the business returns a target IRR.
1	Build a 5-year forecast model of Net Cash Flow (NCF) based on the following assumptions (fill in Column J):
	a.	The last historical year (Year 0) sales is $35,000
	b.	The projected sales growth rate for the forecast period (Year 1-5) is 12% per year
	c.	Net income = Net Profit Margin x Sales. Net Profit Margin is assumed to be 15%
	d.	Depreciation charge is assumed to be $2,500 per year
	e.	Net Cash Flow (NCF) = Net Income + Depreciation
	f.	What is the IRR of the project using NCF based on these assumptions?
2	Now use Goal Seek to find out:
	Note: you must use Goal Seek to get full credit - in your answer describe your procedure for the results
	a.	What must the Sales Growth rate be in order to achieve an IRR = 10%, or IRR = 12%
	b.	Assuming Sales Growth remains at 12%, what must the business sell for (i.e., Investment) in order to achieve an IRR of 13%?
3	Create a Scenario Analysis (using Data+What-If Analysis+Scenario Manager) labeled Worst Case by changing:
	Sales Growth Rate to 10% and Net Profit Margin to 12%, with Target Cells = IRR (i.e., cell D28) and Year 5 Net Cash Flow (cell I27).
	Make sure in the Scenario Analysis Summary you show Current Values as those in (1) above.
	The Scenario Summary will be created in a new sheet in this workbook. Properly re-label the headers for the columns and rows.
		Answer for (1)(f) =
		Answer for (2):
			Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Assumption
		Sales
		NI
		Depr
		Investment
		NCF
		IRR
		IRR	Sales Gr	Investment
		10%
		12%
		13%

Exercise 4

This problem requires the use of the Excel Add-In called Solver. If your spreadsheet does not show Solver at the far right location of the Data tool bar,
you need to load in the applet first from Excel Options by clicking open the Microsoft Office button on the upper left corner of your spreadsheet.
Read the following description of the problem carefully:
1	Below in range E21:P33 is a proforma (forecast) of cash flow for a hypothetical company
2	This forecast is based on the Free Cash Flow to Equity (FCFE) method which is used to value the fair market value (FMV) of a public company's stock
3	Each line item has its own forecast assumptions, which are laid out in range B21:C42.
4	To learn more about a line item, study the formula or put the cursor on the title and read the comment box
5	Examine the assumptions and the forecast results carefully. Make sure you understand the logic of the worksheet
6	In addition, in range E35:P39 you are given several key variables which are important for the exercise
7	The key item here is the Discretionary Capital Expenditure (Capex) line in G38:P38.
8	Management wants to produce a Discretionary Capital Budget (G38:P38) that will
	(a) Maximize the Price per Share (G42), but subject to the constraints that the
	(b) Minimum debt balance in any year (G44) must be at least zero [to rule out negative debt]
	(c) Minimum cash balance in any year (G45) must be at least $10 [to maintain safety margin in cash]
	(d) The Discretionary Capex in any year (G46) must be at least zero [to rule out negative spending]
	(e) The Maximum Capex/Sales ratio in any year (G47) cannot exceed 10% [to guard against over spending]
9	Objective: You are to use Solver to achieve the results in (8). The key output will be the annual Discretionary Capex amounts in G38:P38.
	Assumptions								Year
	Sales level in Year 0	150.0	$ in millions		1	2	3	4	5	6	7	8	9	10
	Sales growth rate (per yr.)	8%	Sales		$ 162.0	$ 175.0	$ 189.0	$ 204.1	$ 220.4	$ 238.0	$ 257.1	$ 277.6	$ 299.9	$ 323.8
	EBIT (earnings before interest and tax) margin	25%	EBIT		40.5	43.7	47.2	51.0	55.1	59.5	64.3	69.4	75.0	81.0
	Tax rate	40%	NOPAT ernest: ernest: NOPAT (non-operating profit after tax) = EBIT x (1-Tax Rate)		24.3	26.2	28.3	30.6	33.1	35.7	38.6	41.6	45.0	48.6
	Depreciation to capex (capital expenditure) ratio	5%	Depreciation		0.2	0.2	0.2	0.2	0.2	0.2	0.3	0.3	0.3	0.3
	Replacement (non-discretionary) capex to sales ratio	2%	Capex ernest: ernest: Capex (capital expenditure) = replacement capex + discretionary capex		3.2	3.5	3.8	4.1	4.4	4.8	5.1	5.6	6.0	6.5
	Working capital (WC) to sales ratio	5%	Working capital		8.1	8.7	9.4	10.2	11.0	11.9	12.9	13.9	15.0	16.2
			FCF (Free Cash Flow) ernest: ernest: FCF (free cash flow) = NOPAT + Depreciation - Capex - WC		13.1	14.2	15.3	16.5	17.9	19.3	20.8	22.5	24.3	26.2
	Interest rate on debt	8%
	Interest rate on cash	2%	Aftertax interest expense		0.7	0.3	(0.0)	(0.4)	(0.9)	(1.4)	(1.9)	(2.5)	(3.1)	(3.8)
	Portion of FCF (free cash flow) used to retire debt	40%	Change in debt		(5.2)	(5.7)	(6.1)	(6.6)	(7.1)	(7.7)	(8.3)	(9.0)	(9.7)	(10.5)
			FCFE (Free Cash Flow to Equity) ernest: ernest: FCFE = cash available for distribution to shareholders = FCF - aftertax interest expense (i.e. payment to debtholders) + increase in debt (borrowings)	ernest: ernest: NOPAT (non-operating profit after tax) = EBIT x (1-Tax Rate)		$ 7.2	$ 8.2	$ 9.2	$ 10.4	$ 11.6	$ 13.0	$ 14.4	$ 16.0	$ 17.7	$ 19.6
	Debt level in Year 0	20.0	Ending debt balance		$ 14.8	$ 9.1	$ 3.0	$ (3.7)	$ (10.8)	$ (18.5)	$ (26.8)	$ (35.8)	$ (45.5)	$ (56.0)
	Cash level in Year 0	12.0	Ending cash balance		$ 19.2	$ 27.0	$ 35.8	$ 45.8	$ 56.9	$ 69.4	$ 83.3	$ 98.7	$ 115.8	$ 134.7
			Discretionary capex
			Ratio of capex to sales		2%	2%	2%	2%	2%	2%	2%	2%	2%	2%
			PV of FCFE Editor: Editor: use 12% discount rate
			ernest: ernest: Capex (capital expenditure) = replacement capex + discretionary capex	Common shares outstanding	2.5		Price per share
					ernest: ernest: This is expected value of the firm's stock price based on the FCFE approach	Minimum debt balance
			Minimum cash balance
			Min. discretionary capex
			Max. capex/sales ratilo