3. WORDRemember, the CFO is your audience, so focus on the numbers. It should be very clear what the...

Question

3. WORDRemember, the CFO is your audience, so focus on the numbers. It should be very clear what the document is about, what the analysis implies, and what recommendations you are making (if any) for future progression of the initiative.You will lose marks if you include superfluous information. The CFO is highly knowledgeable about the business and will be irritated by time wasted reading an unfocussed report. Your report must present only pertinent information and key aspects of the financial modelling.This is a business style report for a non-academic ‘insider’. It must provide concise and easily readable information to aid decision-making. It must include explanation of major conceptual points with simple definitions and explanation of relevant terminology (especially operational matters) and complexities). The CFO is often unfamiliar with operational matters.You MUST INCLUDE an Executive Summary. Assume that your report will be sent to the CFO so it must be comprehensive and describe the ‘big picture’ and the main ‘numbers’ (e.g., EBIT, Capex, NPV, IRR). The structure should also include Introduction, Discussion of Key Findings, and Conclusion. The Cover Page should not include the Executive Summary.The CFO will expect your report to:? analyse and discuss the information, and evaluate it critically. ? identify problems and suggest solutions.? speculate about future trends and impacts.Parameter & Input Tables 		PARAMETER 		DSD - Coles cartons of beer per pallet	72		Attached Liquor Stores 		Brewer pallet utilisation	75%		Sydney DC	172 		 Semi-trailer Trucks: Litres of Diesel per km 	0.5		Melbourne DC	137 		FY17 Forklift cost	110,000		Brisbane DC	106 		Forklift - annual carton capacity	1,000,000		Adelaide DC	41 		Days of sales in inventory in DC	15		Perth DC	57 		Sales days in a year	365		National Total	513 		Litres of Diesel per km per pallet	0.05 		FY18 IT Transport Systems	400,000 		FY18 Transport mangement costs	200,000		Projected FY17 Coles Costs 		FY17 Transition expenses	300,000		$	DC Costs		Into-Store Transport Costs 		Beer % of Mix Revenue	40%			FCPC	VCPC	FCPC	VCPC 		DSD Beer % of Total Beer	85%		Sydney DC	0.4500	0.3200	0.1100	0.4100 		Gross Margin Mark up	23%		Melbourne DC	0.5000	0.3100	0.1000	0.4000 		FY17 Beer Average Selling Price	42		Brisbane DC	0.4750	0.3400	0.1300	0.4200 		FY17 Projected Total Revenue($Billion)	3.5		Adelaide DC	0.4250	0.3500	0.0900	0.3700 		Average DC-to-Store delivery distance(km)	35		Perth DC	0.4000	0.3300	0.1200	0.3800 		DSD Beer Volume % of Total Volume	3.50% 		Annual Volume Growth - All Stock Keeping Units	2.50% 		Inflation	3.20% 		Hurdle Rate	11% 		Tax Rate	30% 		FY17 Diesel Fuel Cost per Litre	1.42 		FY18 Baseline Rebate	$1 Supporting Tables 		DSD Beer Carton Volumes 		FY18 DSD Beer Volume - Cartons 			Liquor Stores	Cartons	% 		Sydney DC	172	9,737,167	33.5% 		Melbourne DC	137	7,755,767	26.7% 		Brisbane DC	106	6,000,812	20.7% 		Adelaide DC	41	2,321,069	8.0% 		Perth DC	57	3,226,852	11.1% 		National Total	513	29,041,667	100% 		 SALES REVENUE 			FY17	FY18	FY19	FY20	FY21	FY22	FY23	FY24	FY25	FY26	FY27	FY28 		National DSD Beer Revenue ($)	1,190,000,000	1,258,782,000	1,331,539,600	1,408,502,588	1,489,914,038	1,576,031,069	1,667,125,665	1,763,485,529	1,865,414,992	1,973,235,979	2,087,289,018 		Beer price ($)	42	43.34	44.73	46.16	47.64	49.16	50.74	52.36	54.04	55.77	57.55 		DSD Beer Cartons 		   Sydney 	9,499,675	9,737,167	9,980,596	10,230,111	10,485,864	10,748,010	11,016,711	11,292,128	11,574,432	11,863,792	12,160,387 		   Melbourne 	7,566,602	7,755,767	7,949,661	8,148,402	8,352,112	8,560,915	8,774,938	8,994,312	9,219,169	9,449,649	9,685,890 		   Brisbane 	5,854,451	6,000,812	6,150,833	6,304,603	6,462,218	6,623,774	6,789,368	6,959,102	7,133,080	7,311,407	7,494,192 		   Adelaide 	2,264,457	2,321,069	2,379,096	2,438,573	2,499,537	2,562,026	2,626,076	2,691,728	2,759,022	2,827,997	2,898,697 		   Perth 	3,148,148	3,226,852	3,307,523	3,390,211	3,474,967	3,561,841	3,650,887	3,742,159	3,835,713	3,931,606	4,029,896 		   National Total	28,333,333	29,041,667	29,767,708	30,511,901	31,274,699	32,056,566	32,857,980	33,679,430	34,521,415	35,384,451	36,269,062 		DC COSTS 		Forklifts (Pallets)	FY17	FY18	FY19	FY20	FY21	FY22	FY23	FY24	FY25	FY26	FY27	FY28 		   Sydney 	131,940	135,238	138,619	142,085	145,637	149,278	153,010	156,835	160,756	164,775	168,894 		   Melbourne 	105,092	107,719	110,412	113,172	116,002	118,902	121,874	124,921	128,044	131,245	134,526 		   Brisbane 	81,312	83,345	85,428	87,564	89,753	91,997	94,297	96,654	99,071	101,547	104,086 		   Adelaide 	31,451	32,237	33,043	33,869	34,716	35,584	36,473	37,385	38,320	39,278	40,260 		   Perth 	43,724	44,817	45,938	47,086	48,263	49,470	50,707	51,974	53,274	54,606	55,971 		National Total	393,519	403,356	413,440	423,776	434,371	445,230	456,361	467,770	479,464	491,451	503,737 		Forklift Unit Cost ($)	110,000	113,520	117,153	120,902	124,770	128,763	132,883	137,136	141,524	146,053	150,727 		Forklifts Quautity (ROUNDUP) 		   Sydney 	10	10	10	11	11	11	12	12	12	12	13 		   Melbourne 	8	8	8	9	9	9	9	9	10	10	10 		   Brisbane 	6	7	7	7	7	7	7	7	8	8	8 		   Adelaide 	3	3	3	3	3	3	3	3	3	3	3 		   Perth 	4	4	4	4	4	4	4	4	4	4	5 		National Total	31	32	32	34	34	34	35	35	37	37	39 		Incremental Forklifts Total		1	0	2	0	0	1	0	2	0	2 		Forklifts Capex	3,520,000		234,305			128,763		274,271		292,106 		Forklifts Depreciation (10 year straight line) ($) 		   Sydney 		113,520	113,520	125,610	125,610	125,610	138,898	138,898	138,898	138,898	153,971 		   Melbourne 		90,816	90,816	102,906	102,906	102,906	102,906	102,906	117,059	117,059	117,059 		   Brisbane 		79,464	79,464	79,464	79,464	79,464	79,464	79,464	93,616	93,616	93,616 		   Adelaide 		34,056	34,056	34,056	34,056	34,056	34,056	34,056	34,056	34,056	34,056 		   Perth 		45,408	45,408	45,408	45,408	45,408	45,408	45,408	45,408	45,408	60,481 		National Total		363,264	363,264	387,444	387,444	387,444	400,733	400,733	429,037	429,037	459,183 		Asset Disposal-Forklifts												441,861 		Incremental Fixed DC Costs		363,264	363,264	387,444	387,444	387,444	400,733	400,733	429,037	429,037	459,183 		Incremental Variable DC Costs		9,739,699	10,302,653	10,898,147	11,528,059	12,194,381	12,899,217	13,644,791	14,433,460	15,267,714	16,150,188 		Incremental DC Costs		10,102,963	10,665,917	11,285,591	11,915,504	12,581,826	13,299,949	14,045,524	14,862,498	15,696,752	16,609,371 		TRANSPORT COSTS 			FY17	FY18	FY19	FY20	FY21	FY22	FY23	FY24	FY25	FY26	FY27	FY28 		Incremental Fuel (VC) ($)	1.42	1.47	1.51	1.56	1.61	1.66	1.72	1.77	1.83	1.89	1.95 		   Sydney 		346,822	366,868	388,073	410,504	434,231	459,329	485,878	513,962	543,669	575,093 		   Melbourne 		276,247	292,215	309,105	326,971	345,870	365,861	387,008	409,377	433,039	458,068 		   Brisbane 		213,739	226,093	239,161	252,985	267,607	283,075	299,437	316,744	335,052	354,418 		   Adelaide 		82,673	87,451	92,506	97,853	103,508	109,491	115,820	122,514	129,596	137,086 		   Perth 		114,935	121,578	128,606	136,039	143,902	152,220	161,018	170,325	180,169	190,583 		   National Total		1,034,416	1,094,205	1,157,450	1,224,351	1,295,118	1,369,976	1,449,161	1,532,922	1,621,525	1,715,249 		IT System - Capex ($)	400,000					468,229 		IT System - Tax 3 year straight line ($)		133,333	133,333	133,333			156,076	156,076	156,076 		IT System - Reporting 5 year straight line (FC) ($)		80,000	80,000	80,000	80,000	80,000	93,646	93,646	93,646	93,646	93,646 		Beer Transport Management Costs (FC) ($)		200,000	206,400	213,005	219,821	226,855	234,115	241,606	249,338	257,316	265,551 		Incremental Fixed Transport Costs		280,000	286,400	293,005	299,821	306,855	327,760	335,252	342,983	350,962	359,196 		Incremental Variable Transport Costs		1,034,416	1,094,205	1,157,450	1,224,351	1,295,118	1,369,976	1,449,161	1,532,922	1,621,525	1,715,249 		Incremental Transport Costs		1,314,416	1,380,605	1,450,455	1,524,172	1,601,973	1,697,736	1,784,413	1,875,906	1,972,487	2,074,446 		WORKING CAPITAL 			FY17	FY18	FY19	FY20	FY21	FY22	FY23	FY24	FY25	FY26	FY27	FY28 		Inventory in DCs	40,753,425	43,108,973	45,600,671	48,236,390	51,024,453	53,973,667	57,093,345	60,393,340	63,884,075	67,576,575	67,576,575 		Change		2,491,699	2,635,719	2,788,063	2,949,213	3,119,678	3,299,995	3,490,735	3,692,500	0	-67,576,575 		Cashflow	-40,753,425	-2,491,699	-2,635,719	-2,788,063	-2,949,213	-3,119,678	-3,299,995	-3,490,735	-3,692,500	0	67,576,575 		Supply Chain Per Carton Impacts from DSD Beer Conversion 		FY18 - Incremental Costs of DSD Beer Converted 			DC		Into-Stores Transport		Stores	Total 			FCPC	VCPC	FCPC	VCPC	VCPC	FCPC	VCPC	TCPC 		Sydney DC	0.0000	0.3302	0.2000	0.0356	0.0000	0.2000	0.3659	0.5659 		Melbourne DC	0.0000	0.3199	0.0000	0.0356	0.0000	0.0000	0.3555	0.3555 		Brisbane DC	0.0000	0.3509	0.0000	0.0356	0.0000	0.0000	0.3865	0.3865 		Adelaide DC	0.0000	0.3612	0.0000	0.0356	0.0000	0.0000	0.3968	0.3968 		Perth DC	0.0000	0.3406	0.0000	0.0356	0.0000	0.0000	0.3762	0.3762 		National Total	0.0000	1.7028	0.2000	0.1781	0.0000	0.2000	1.8809	2.0809 					Only 1 transport manager Summary Output Tables 		Requirement Table 1: Baseline Detailed Financial Summary - $1.00 per carton rebate in FY18 			FY17	FY18	FY19	FY20	FY21	FY22	FY23	FY24	FY25	FY26	FY27	FY28 		   DSD Beer carton volume (M)	28.3333	29.0417	29.7677	30.5119	31.2747	32.0566	32.8580	33.6794	34.5214	35.3845	36.2691 		   Rebate per carton		1.0000	1.0320	1.0650	1.0991	1.1343	1.1706	1.2080	1.2467	1.2866	1.3278 		EBIT ($M) 		   Sales	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000		Irrelevant 		   Gross Profit		29.0417	30.7203	32.4959	34.3742	36.3610	38.4627	40.6858	43.0374	45.5250	48.1564 		   Distribution Centre Costs		-10.1030	-10.6659	-11.2856	-11.9155	-12.5818	-13.2999	-14.0455	-14.8625	-15.6968	-16.6094 		   Transportation Costs		-1.3144	-1.3806	-1.4505	-1.5242	-1.6020	-1.6977	-1.7844	-1.8759	-1.9725	-2.0744 		   In-Store Costs		0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000 		   Transition Expenses	-0.3000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000 		   EBIT	-0.3000	17.6243	18.6738	19.7599	20.9345	22.1772	23.4650	24.8559	26.2990	27.8558	29.4725 		CASH FLOWS ($M) 		   EBIT excluding depreciation	-0.3000	17.1810	18.2305	19.2924	20.4671	21.7098	22.9706	24.3615	25.7764	27.3331	28.9197 		   Depreciation (@tax rate)		0.4966	0.4966	0.5208	0.3874	0.3874	0.5568	0.5568	0.5851	0.4290	0.4592 		   Income Tax	0.0900	-5.3033	-5.6181	-5.9440	-6.2563	-6.6292	-7.0582	-7.4755	-7.9084	-8.3286	-8.8137 		   After tax cash before investing	-0.2100	11.8777	12.6124	13.3485	14.2107	15.0806	15.9124	16.8860	17.8679	19.0044	20.1060 		   Working Capital (Inventory)	-40.7534	-2.4917	-2.6357	-2.7881	-2.9492	-3.1197	-3.3000	-3.4907	-3.6925	0.0000	67.5766 		   Capex - IT Systems	-0.4000					-0.4682 		   Capex - Forklifts	-3.5200		-0.2343			-0.1288		-0.2743		-0.2921 		   Asset Disposal - Forklifts												0.4419 		   Net Cash Flows	-44.8834	9.3860	9.7423	10.5604	11.2615	11.3639	12.6124	13.1210	14.1754	18.7123	87.6826	0.3093 		($M) NPV (@11.0%)	50.7731 		IRR	0.2039 		Requirement Table 2: Executive Summary & Sensitivity Analysis FY18 			Scenario 1	Scenario 2	Scenario 3	Scenario 4 		Rebate per carton ($)	1.00	0.66	0.39	1.15 		Gross Profit ($M)	29.0417	19.1368	11.4174	33.3979 		DC Costs ($M)	-10.1030	-10.1030	-10.1030	-10.1030 		Transportation Costs ($M)	-1.3144	-1.3144	-1.3144	-1.3144 		Other Costs ($M)	0.0000	0.0000	0.0000	0.0000 		EBIT ($M)	17.6243	7.7195	0.0000	21.9805 		($M) NPV (@11.0%)	50.7731	-0.0000	-39.5705	73.1036 		IRR	0.2039	-0.0544		0.2902   Microsoft Word - CMA Assignment 2017 Spring.docx 1 | P a g e     Cost Management and Analysis #22753    ASSESSMENT ITEM 2: GROUP ASSIGNMENT     Weighting: 20%.           Spring 2017 1. OVERVIEW  Your group is a team of management accountants working for Coles (under the corporate entity of  Wesfarmers). You are to perform the required financial analysis for the initiative in Appendix One of  this document. You are to prepare the analysis for the CFO (i.e., your CMA Lecturer). Your group must submit and present your financial analysis in three formats:   A. Excel – financial modelling   B. Word – written report   C. PowerPoint – for presentation in-class (5 minutes per group). Extracts from the Excel modelling are to be included in your Word report and PowerPoint  presentation. 2. EXCEL   The CFO (i.e., your Lecturer) will review your financial modelling. The CFO will need to be very  comfortable with your technical execution. Like many senior managers, the CFO is extremely time  poor and needs to be communicated to very concisely. The CFO needs to quickly understand the  design of your models, as well as assumptions and limitations. You will email your Excel file to your lecturer for review.  Here is what the CFO will expect to see:    The model is clearly structured.   Input and output tables are clear and separate.    Tables logically present key components (e.g., costs, benefits, EBIT, assumptions and  parameters).    The model is materially complete and comprehensive.    The model is technically correct, e.g., cash flows are on an after-tax basis.   Excel extracts used in the written report or PowerPoint presentation should be appropriately  formatted, e.g., present figures in $M (e.g., $0.18M for $180,000). 2 | P a g e     3. WORD   Remember, the CFO is your audience, so focus on the numbers. It should be very clear what the  document is about, what the analysis implies, and what recommendations you are making (if any) for  future progression of the initiative. You will lose marks if you include superfluous information. The CFO is highly knowledgeable about  the business and will be irritated by time wasted reading an unfocussed report. Your report must  present only pertinent information and key aspects of the financial modelling. This is a business style report for a non-academic ‘insider’. It must

Robert · Accepted Answer

Analysis of the financial summary: 
Fixed Cost Analysis: 
 As per the financial summary of the company IT Transport System which is 4, 
00,000, Transport management cost is 200000 and transition expenses is 3, 00,000. These 
fixed cost need to be paid by the company in all way whether it would be less or more sales 
done by the company.  It will also cost the in- store cost, distribution centre cost under the 
fixed cost.  All these fixed will be depreciated over it fixed time and will tax benefited during 
these periods. So that these fixed cost will have to bear the company in all situations. 
Variable Cost of the company analysis: 
 Company’s variable cost includes the Beer % of the Mix revenue and all other 
expenses will be included in the variable cost which would incur during the period of making 
per unit productions. This cost is also important for the company because it is incurred on per 
unit production and also the used in the forecasting the expenses on the company in 
upcoming years. Without variable cost it is not possible for the company to prepare the 
proper budget for any of its projects and plans, and forecast for profits. 
Total Cost of the company: 
 Total cost of the company includes the all expenses during the project life time span 
of the company. On the basis of the total cost incurred by the project, per unit cost is 
calculated and the profit margin calculated from the total revenue minus total cost. Total cost 
also include the Job cost, Insurance cost, mixed cost of the project. For instance: travelling 
cost for the transitions of the project, insurance cost of the project and machinery etc. Total 
cost includes the direct cost and indirect cost, like, cost of goods sold etc.  
IRR of the project: 
 Internal rate of returns describe the rate on which when we discount from that rate, 
present value of the project would be zero. The Internal rate of return (IRR) for an investment 
is the percentage rate earned on each dollar invested for each period it is invested. IRR is also 
another term used for interest. Ultimately, IRR gives an investor the means to compare 
alternative investments based on their yield.
IRR Analysis on scenario 1:  
 In the scenario 1, IRR is 26.57%,

3. WORD Remember, the CFO is your audience, so focus on the numbers. It should be very clear what the document is about, what the analysis implies, and what recommendations you are making (if any) for...

Answer To: 3. WORD Remember, the CFO is your audience, so focus on the numbers. It should be very clear what...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment