Question

Using the functions in the spreadsheet, calculate the monthly average for each expense for the year. Using the calculations, calculate the percentage of each expenses to the total sales May- Aug- Sep- Oct- Nov Dec- Jan-14 Feb-14 14 Apr-14 14Jun-14 Jul-14 14 14 Sales Actual Actual Actual Actual Actual Actual Actual Actual Actual Actual Actual Actual 56,4s |57,02 |57,30 | S7,64 157,99 158,34 12,42 12,79 13,43 13,51 13,59 13,67 6,137 6,383 6386 6388 6,324 6,577 6,774 6,778 6,782 6,788 6,829 57,00 Product 1 48,267 50.680 52,708 55,343 3 56433 6 o Product 210.21410,725 11.154 11,711 3 11,942 Product 3 5,845 Total Sales 64,326 67,542 244 440 SA 699 452 582 515 | 944 |in | 848 24,14 28,33 21,70 24,94 25,0825,23 15,09 | 19,91 | 15,50 | 16,36 | 15,67 | 15,77 16,036 | 3.735 15,282 2,298 | 3,101 3.897「 3,919 | 3,942 24,14 Inventory 20,584 20,263 24,585 23,501 5 22,4105 12,865 13,508 10,537 14,688 2 18,6750 ーしー| Supplies 3,216 Total Cost of Goods 36,666 37.824 38,634 41,861 3 43,76 44,51 s0,S4 40,30 45,20 44,67 44,94 44,820 7 Operating 12,84 12.25 12.95 12,96 12,85 12,10 Salaries ↓ 12,879 13,179 12,879 12,579 2 12,780 s 410 1,150 1,250 1,200 1,000 Utilities 780 Insurance 1,522 Repairs 160 740 6B5 710795 910 1,180 1,020 650 710 745 522 1,522 522 1,522 522 1,522 1,522 22 1,522 1,522 800 15,11 16,07 15,36 16,64 16,38 16,56 15,46 Expenses 15,741 15,542 15,141 15.326 660 610579 10,402 11,063 11,053 719 691 880 10,82 13,02 11,45 11,79 11,86 3 8 10,458 79114 1.252 122 1252 1252 1252 1252 1.252 1,252 1,252 1.252 1,252 9,681 Expense 1,252 Net

Answer 1

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		Calculation of average expenses:
3			Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Average Expenses
4		Sales Revenue	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual
5		Total Sales	$64,326	$67,542	$70,244	$73,440	$75,454	$74,699	$75,452	$76,587	$77,515	$77,944	$78,377	$78,848
6		Direct Expenses
7		Inventory	$20,584	$20,263	$24,585	$23,501	$24,145	$22,410	$24,145	$28,337	$21,704	$24,942	$25,081	$25,231	$23,744	=AVERAGE(D7:O7)
8		Labor	$12,865	$13,508	$10,537	$14,688	$13,582	$18,675	$15,090	$19,913	$15,503	$16,368	$15,675	$15,770	$15,181
9		Supplies	$3,216	$4,053	$3,512	$3,672	$6,036	$3,735	$5,282	$2,298	$3,101	$3,897	$3,919	$3,942	$3,889
10		Total cost of goods	$36,665	$37,824	$38,634	$41,861	$43,763	$44,820	$44,517	$50,548	$40,308	$45,207	$44,675	$44,943	$42,814
11		Operating Expenses
12		Salaries	$12,879	$13,179	$12,879	$12,579	$12,882	$12,780	$12,845	$12,250	$12,950	$12,963	$12,850	$12,100	$12,761
13		Advertising	$400	$0	$0	$500	$0	$0	$0	$410	$1,150	$1,250	$1,200	$1,000	$493
14		Utilities	$780	$841	$740	$685	$710	$795	$910	$1,180	$1,020	$650	$710	$745	$814
15		Insurance	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522	$1,522
16		Repairs	$160	$0	$0	$40	$0	$741	$800	$0	$0	$0	$280	$100	$177
17		Total Operating Expenses	$15,741	$15,542	$15,141	$15,326	$15,114	$15,838	$16,077	$15,362	$16,642	$16,385	$16,562	$15,467	$15,766
18		EBIT	$27,660	$29,719	$31,610	$31,579	$31,691	$29,880	$30,936	$26,040	$37,207	$37,236	$33,702	$33,904
19		Income Taxes	$9,681	$10,402	$11,063	$11,053	$11,092	$10,458	$10,827	$9,114	$13,022	$11,458	$11,796	$11,867	$10,986
20		Interest Expense	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252	$1,252
21
22
23		Calculation of expenses as % of sales:
24			Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14
25		Sales Revenue	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual
26		Total Sales	$64,326	$67,542	$70,244	$73,440	$75,454	$74,699	$75,452	$76,587	$77,515	$77,944	$78,377	$78,848
27		Direct Expenses
28		Inventory	32.00%	30.00%	35.00%	32.00%	32.00%	30.00%	32.00%	37.00%	28.00%	32.00%	32.00%	32.00%	=O7/O$5
29		Labor	20.00%	20.00%	15.00%	20.00%	18.00%	25.00%	20.00%	26.00%	20.00%	21.00%	20.00%	20.00%
30		Supplies	5.00%	6.00%	5.00%	5.00%	8.00%	5.00%	7.00%	3.00%	4.00%	5.00%	5.00%	5.00%
31		Total cost of goods	57.00%	56.00%	55.00%	57.00%	58.00%	60.00%	59.00%	66.00%	52.00%	58.00%	57.00%	57.00%
32		Operating Expenses
33		Salaries	20.02%	19.51%	18.33%	17.13%	17.07%	17.11%	17.02%	15.99%	16.71%	16.63%	16.40%	15.35%
34		Advertising	0.62%	0.00%	0.00%	0.68%	0.00%	0.00%	0.00%	0.54%	1.48%	1.60%	1.53%	1.27%
35		Utilities	1.21%	1.25%	1.05%	0.93%	0.94%	1.06%	1.21%	1.54%	1.32%	0.83%	0.91%	0.94%
36		Insurance	2.37%	2.25%	2.17%	2.07%	2.02%	2.04%	2.02%	1.99%	1.96%	1.95%	1.94%	1.93%
37		Repairs	0.25%	0.00%	0.00%	0.05%	0.00%	0.99%	1.06%	0.00%	0.00%	0.00%	0.36%	0.13%
38		Total Operating Expenses	24.47%	23.01%	21.55%	20.87%	20.03%	21.20%	21.31%	20.06%	21.47%	21.02%	21.13%	19.62%
39		Income Taxes	15.05%	15.40%	15.75%	15.05%	14.70%	14.00%	14.35%	11.90%	16.80%	14.70%	15.05%	15.05%
40		Interest Expense	1.95%	1.85%	1.78%	1.70%	1.66%	1.68%	1.66%	1.63%	1.62%	1.61%	1.60%	1.59%
41