# CORPORATE MODELLING IN BUSINESS

QUESTION

ISYS2043 Assignment One

Corporate Modeling Case Study

1.         Due Date:       5 pm on Friday, Week 6 online submission through Blackboard at my studies

2.         The Case Description

Problem Background

Dixies is an established Australian company which currently imports all its products.  However, due to the recent changes in the Australian economy, it is thinking of manufacturing its products in Australia.  It intends to manufacture shelving in the first instance.  The company’s shelving comes in two types, a superior quality (Type A) and an inferior quality (Type B).  The market for Type B is shrinking, and its selling price is depressed since there is a large amount of production capacity in several overseas plants for producing Type B only which are old and are desperate for markets.  Dixies intends to produce both products at the one plant. For your reference some specific financial information is detailed in the following.

Capital Items

The proposed plant will produce both types of shelving: Type A and Type B.  Capital Investment will be an amount based on your student number.

• If your student number starts with a 3, the Capital Investment is equal to the first 6 digits of your student number plus additional amount of \$500,000. (Thus if your student number is 3113008, the Capital Investment is \$811,300).
• If your student number starts with a 2, the Capital Investment is half your student number.  (Thus if your student number is 2001234P, the Capital Investment is \$1,000,617).
• For any other numbers, the Capital Investment is equal to the first 6 digits of your student number.

The plant is expected to be viable for at least 10 years. However Dixies is assuming conservatively that the plant will have a life of Six (6) years. The Discount Rate used by the company is 12% per year.

Revenue Data

The demand for the shelving is assumed to be as follows:

 Product Year 1 Demand (tonnes) Annual Growth Unit Selling Price (\$ per tonne) Type A 5,400 +10% \$1,315 Type B 2,100 -15% \$1,260

Capacity and Costs

Plant Capacity is 8,000 tons per year in total.  The combined sales of Type A and Type B must not exceed this amount. If the capacity is tight, Type A shelving must have the priority.  In other words, all the required Type A shelving should be produced and as much of the Type B shelving as the capacity constraint permits.  Fixed Costs are \$1,300,000 in the first year rising at 1% per year afterwards.  Unit Variable Costs are \$1,100 per tons for both Type A and Type B product.

Taxation

Depreciation Allowance for tax purposes is 1/6 per year flat rate.  The Tax Rate is 30%, due immediately.  Dixies has substantial taxable profits from other parts of its business, so any ‘negative tax’ will be immediately taken up as profit.

Economic Factors

General inflation is assumed to be zero. Working Capital may be ignored.  All money movements, other than initial capital outlay, are assumed to occur at the end of the year.

3.      The Evaluation Tasks

a)           Produce a spreadsheet model to obtain the Net Present Value of the net cash flow for the proposed plant.

b)           Dixies investment group has estimated that if Dixies do not manufacture for the Type B market and produce a single purpose plant, producing only Type A product, the Initial Capital Investment is reduced by \$30,000.  Compare the Net Present Value of this proposal with the initial proposal assuming that Type A sales are the same in both cases.

c)           What Initial Capital Investment would cause the proposal outlined in 2 to have the same Net Present Value as the initial proposal in 1?

d)           Assuming that the Discount Rate is uniformly distributed between 10% and 14%, and that Type A growth rate is normally distributed with a mean of 10% and standard deviation of 1%, and Capital Investment is normally distributed with a mean of \$960,000 and standard deviation of \$25,000. Develop a pessimistic model and an optimistic model and further carry out a Monte Carlo analysis using 200 simulations.

e)           Write a brief report that includes your recommendations together with your reasons for the problem described above to Dixies management. Please note that the report that you are to prepare is a report to management. As a result you need think careful about how you are going to present the report in order to help the management understand your analysis. At the same time you also need address the questions listed as above comprehensively.

4.      What to Submit

• A Spreadsheet file with all the models (Note that you will be penalised if your spreadsheet models are in separate files in your submission).
• A written report in a word file.

A penalty of 20% per day may be applied for late submission.  60% of the marks will be allocated for the spreadsheet model, and 40% for the written report. This assignment must be of a satisfactory standard in order to pass this course.

SOLUTION

# Recommendations to the Management

It has been seen that there were two alternatives which could have been taken up, one of the options is two manufacture both Type A and Type B shelving.  The options that are available  are tabulated below:

 Option Type A Type B Total % utilization Option 1 5400 2100 7500 93.75 Option 2 5400 0 5400 67.50

The above data has been shown for the first year of operation.

It can be clearly seen that the plant will have 93.75% in the first year of operation while only 67.5% in the first year in case only product A is produced. Thus the fixed cost will contribute more to per unit cost. This is to say that the profit margin will be reduced.  Present value of the cash flow over the period of six years is shown below:

 Option 1 \$193,704.02 \$205,371.89 \$217,985.90 \$207,624.58 \$198,615.41 \$174,701.28 Option 2 (\$17,635.27) \$44,801.10 \$100,049.61 \$148,832.04 \$191,799.30 \$173,941.34

It can be seen from the above table that the present value in the first three years is the major difference between the two alternatives. This is because of two reasons. Firstly, these three years contribute maximum to the present value. Secondly these three years are having lower value for the utilization percentage of the plant which has been compensated in the option 1 by production of Type B shelving.

This can be seen in the model that has been developed. The net present value is coming out to be negative in the option 2 while it is positive in case of Option1.

Recommendation: The Company has two options. Firstly it can go with the existing option 1 that it is having in hand. The other option is to go in for the manufacturing of A to the extent that the saving in capital investment that has been realized because of manufacturing only A should be done and kept as inventory. The cost of inventory could also be factored in while going for this approach. The production of Type A shelving can be increased and imported as it is giving more profits and its demand in the market is on the higher side. So a market analysis can be done and the production of the Product A can be increased if the demand is there in the market for the product.

The company can also keep going for production of Type B shelving as long as the price of Type B shelving is not less than the sum of unit cost and \$ 30,000 which is the saving when Only Type A shelving was produced. Also the second option should be considered if there is increase in demand of Type A shelving

 Year 0 1 2 3 4 5 6 Type A 5400 5940 6534 7187 7906 8000 Type B 2100 1785 1466 813 94 0 Total 7500 7725 8000 8000 8000 8000 Unit Cost (Type A) \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 Unit Cost (Type B) \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 Production & Sales(MT) Unit Cost (Type A) \$1,325.00 \$1,325.00 \$1,325.00 \$1,325.00 \$1,325.00 \$1,325.00 Unit Cost (Type B) \$1,260.00 \$1,260.00 \$1,260.00 \$1,260.00 \$1,260.00 \$1,260.00 Revenues Type A \$7,155,000.00 \$7,870,500.00 \$8,657,550.00 \$9,523,305.00 \$10,475,635.50 \$10,600,000.00 Type B \$2,646,000.00 \$2,249,100.00 \$1,847,160.00 \$1,023,876.00 \$118,263.60 \$0.00 Total \$9,801,000.00 \$10,119,600.00 \$10,504,710.00 \$10,547,181.00 \$10,593,899.10 \$10,600,000.00 Costs Variable \$8,250,000.00 \$8,497,500.00 \$8,800,000.00 \$8,800,000.00 \$8,800,000.00 \$8,800,000.00 Fixed \$1,300,000.00 \$1,313,000.00 \$1,326,130.00 \$1,339,391.30 \$1,352,785.21 \$1,366,313.07 Total Cost excl Dep \$9,550,000.00 \$9,810,500.00 \$10,126,130.00 \$10,139,391.30 \$10,152,785.21 \$10,166,313.07 PBDIT \$251,000.00 \$309,100.00 \$378,580.00 \$407,789.70 \$441,113.89 \$433,686.93 Less Dep \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 PBIT \$115,783.33 \$173,883.33 \$243,363.33 \$272,573.03 \$305,897.22 \$298,470.27 Less Interest \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 PBT \$115,783.33 \$173,883.33 \$243,363.33 \$272,573.03 \$305,897.22 \$298,470.27 Less Tax \$34,735.00 \$52,165.00 \$73,009.00 \$81,771.91 \$91,769.17 \$89,541.08 PAT \$81,048.33 \$121,718.33 \$170,354.33 \$190,801.12 \$214,128.05 \$208,929.19 Assets Capital Invested \$811,300.00 \$811,300.00 Depreciation Depreciation \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 12.00% 0 1 2 3 4 5 6 PAT \$81,048.33 \$121,718.33 \$170,354.33 \$190,801.12 \$214,128.05 \$208,929.19 +DEP \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 \$135,216.67 Operating CF \$216,265.00 \$256,935.00 \$305,571.00 \$326,017.79 \$349,344.72 \$344,145.85 Terminal Cf (\$811,300.00) Total CF (\$811,300.00) \$216,265.00 \$256,935.00 \$305,571.00 \$326,017.79 \$349,344.72 \$344,145.85 Present Value Factor 1.00 0.89 0.80 0.71 0.64 0.57 0.51 present Value (\$811,300.00) \$193,093.75 \$204,827.01 \$217,499.40 \$207,190.20 \$198,227.58 \$174,355.00 NPV \$383,892.94
 Year 0 1 2 3 4 5 6 Type A 5400 5940 6534 7187 7906 8000 Type B 0 0 0 0 0 0 Total 5400 5940 6534 7187 7906 8000 Unit Cost (Type A) \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 Unit Cost (Type B) \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 \$1,100.00 Production & Sales(MT) Unit Cost (Type A) \$1,325.00 \$1,325.00 \$1,325.00 \$1,325.00 \$1,325.00 \$1,325.00 Unit Cost (Type B) \$1,260.00 \$1,260.00 \$1,260.00 \$1,260.00 \$1,260.00 \$1,260.00 Revenues Type A \$7,155,000.00 \$7,870,500.00 \$8,657,550.00 \$9,523,305.00 \$10,475,635.50 \$10,600,000.00 Type B \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 Total \$7,155,000.00 \$7,870,500.00 \$8,657,550.00 \$9,523,305.00 \$10,475,635.50 \$10,600,000.00 Costs Variable \$5,940,000.00 \$6,534,000.00 \$7,187,400.00 \$7,906,140.00 \$8,696,754.00 \$8,800,000.00 Fixed \$1,300,000.00 \$1,313,000.00 \$1,326,130.00 \$1,339,391.30 \$1,352,785.21 \$1,366,313.07 Total Cost excl Dep \$7,240,000.00 \$7,847,000.00 \$8,513,530.00 \$9,245,531.30 \$10,049,539.21 \$10,166,313.07 PBDIT (\$85,000.00) \$23,500.00 \$144,020.00 \$277,773.70 \$426,096.29 \$433,686.93 Less Dep \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 PBIT (\$215,216.67) (\$106,716.67) \$13,803.33 \$147,557.03 \$295,879.62 \$303,470.27 Less Interest \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 PBT (\$215,216.67) (\$106,716.67) \$13,803.33 \$147,557.03 \$295,879.62 \$303,470.27 Less Tax (\$64,565.00) (\$32,015.00) \$4,141.00 \$44,267.11 \$88,763.89 \$91,041.08 PAT (\$150,651.67) (\$74,701.67) \$9,662.33 \$103,289.92 \$207,115.73 \$212,429.19 Assets Capital Invested \$781,300.00 Depreciation \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 12.00% 0 1 2 3 4 5 6 PAT (\$150,651.67) (\$74,701.67) \$9,662.33 \$103,289.92 \$207,115.73 \$212,429.19 +DEP \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 \$130,216.67 Operating CF (\$20,435.00) \$55,515.00 \$139,879.00 \$233,506.59 \$337,332.40 \$342,645.85 Terminal Cf (\$781,300.00) Total CF (\$781,300.00) (\$20,435.00) \$55,515.00 \$139,879.00 \$233,506.59 \$337,332.40 \$342,645.85 PVF 1.00 0.89 0.80 0.71 0.64 0.57 0.51 PV (\$781,300.00) (\$18,245.54) \$44,256.22 \$99,563.11 \$148,397.66 \$191,411.46 \$173,595.05 NPV (\$142,322.03)

K001

“The presented piece of writing is a good example how the academic paper should be written. However, the text can’t be used as a part of your own and submitted to your professor – it will be considered as plagiarism.

But you can order it from our service and receive complete high-quality custom paper.  Our service offers Business  essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”