Kitchenware product is a small company of stainless steel kitchen utensils and is determined in the production and marketing of a new type of carving knife The approximated variable entails raw materials that is standing at three dollars, packaging that is standing at zero point nine dollars, The underlying inflation rates for the subsequent three years are conjecture to be three percent, five percent and six percent respectively.
Business Data Analysis
(a) Group formal business report
(a) Kitchenware Products have asked you to develop a financial planning model so that they can determine how net profits would be affected by changes in planned sales volume and product price over years 2 to 4.
Financial planning and business prospects are concepts used alternatively. However, there is a clear difference between the two terms. Planning involves formulating future operations over which the planner holds absolute control and business forecast. This involves anticipation of events though which the planner lacks absolute control. For instance, people may plan an excursion based on the weather forecast. However, in most times, such plans are spoilt because of ineffective forecasting (BARLOW, 2005, pp23-56). However, a business forecast is relatively different from the weather forecast. The major aim of financial forecast is to extrapolate historical company data.
Kitchenware product is a small company of stainless steel kitchen utensils and is determined in the production and marketing of a new type of carving knife (BOSLAUGH, 2012, pp202-267). The underlying estimates that the company wants to dispose are forty thousand units in the first year and anticipate an increase by as much as ten percent yearly thereafter. The underlying variable costs for every unit are approximated (Barlow, 2005, pp45-67). The approximated variable entails raw materials that is standing at three dollars, packaging that is standing at zero point nine dollars, direct labour which is also sanding at two dollars accompanied by distribution cost that is standing at one dollar.
The underlying inflation rates for the subsequent three years are conjecture to be three percent, five percent and six percent respectively. Inflation will influence the prevailing variable costs for the product but fixed costs are probably to remain at the same level of ten thousand dollars over the subsequent four years (BARLOW, 2005, pp23-56). The process of computing profits over the underlying subsequent four years, the corresponding tax rate will be taken into consideration as twenty three percent. Kitchenware Products intend to dispose the underlying carving knife as piece equivalent to eight dollars for the corresponding first year with a yearly escalation of zero point three dollars subsequently (BOSLAUGH, 2012, pp202-267). This financial planning model intend to aid in the determination of the net profits as they are affected by the corresponding alteration within the planned sales volumes accompanied by product price over the subsequent years two through to year four. The prevailing approximates are taken as the underlying base case for the entire four years (Barlow, 2005, pp45-67). Since Kitchenware intend to assess the underlying model in terms of the proportions alterations from the prevailing base case ought to depict alteration factors for the sales volumes accompanied by the product price constructed into the model.
Column C. contains planning values that entails starting assumptions while column D. depicts year 1. Column E. extending to G. contain details for the three underlying year planning horizon while column H. contains the totals for the entire four years. Kitchenware products have decided in making the main capital investment within the corresponding introduction of the new commodities, which is commonly known by the Wahl James Martin Electric Knife or as the corresponding Protocol within the company (Barlow, 2005, pp45-67). There also exists a double action safety lock in order to avert the underlying knife from being accidently functioned accompanied by a protection button for the secure exclusion of the blades. Financial planning accompanied by the business enterprise forecasting are frequent pertinent interchangeably (BARLOW, 2005, pp23-56). Nevertheless, there exist a distinct amidst planning which mainly concerns formulation of the prospect activities over which the planner has control accompanied by forecasting in predicting events over which the underlying planner possess no control. Business enterprise forecasting which is mainly based on the underlying extrapolation of chronological company data that is more dependable than weather forecasting and mainly serve the major monetary forecasting to aid planning accompanied by decision-making. Financial planning predicaments can be considered as semi-structured such as containing a set of postulation, which are made with highest degree of self-assurance while others supplementary are a diminutive, more that educated guess. The purpose of generating a computer-based financial model that permits the prevailing planner to take part with the underlying educated guess (BARLOW, 2005, pp23-56). Financial planning business model permits the planner to see instantaneously the prevailing outcomes of their confronts to variables which have an elevated degree of improbability are consequently reduced. Sensitivity analysis entails a planner questioning a series in order to aid in the determination of the variables that possesses a main influence on the corresponding outcome. This is normally accompanied by the identification of the variables that do not affect the outcomes thereby ensuring safety disregarded. Moreover, sensitivity analysis that might unearth the cost of definite component utilized within product P. which is the fundamental subject to quite great and capricious variations (Barlow, 2005, pp45-67). Nevertheless, the component's understanding to cost possess extremely little impact on the pricing of merchandise P. because it designates such a diminutive element of total cost.
A
B
C
D
E
F
G
H
I
1.
Financial planning model for kitchenware products
2.
3.
Planning
Values
Year 1
Year 2
Year 3
Year 4
Totals
4.
5.
Sales
4,000
10%
6.
Planned Growth
7.
% change volume
8.
Planned sales Volume
40,000
44,000
48,400
53, 240
185, 640
9.
Actual sales Vol
40,000
44,000
48,400
53,240
185,640
10.
11.
Product price
£8.00
12.
Price increase
£0.30
13.
% change price
14.
Planned price
£8.00
£8..30
£8..60
£8.8.90
15.
Actual price
£8..00
£8..30
£8..60
£8..90
16.
17.
Sales revenue
£320,000
£365,200
£416,240
£473,836
£1575,276
18.
19.
Inflation
0%
3%
5%
6%
20.
21.
Variable unit cost
22.
Raw material
£3.00
£3.09
£3.24
£3.44
23.
Labour cost
£2.00
£2.06
£2.16
£2.29
24.
Packaging
£0.90
£0.93
£0.97
£1.03
25.
Distribution
£1.00
£1.03
£1.08
£1.15
26.
Total unit cost
£6.90
£7.11
£7.46
£7.91
27.
28.
Direct costs
£276,000
£312,708
£361,178
£412,133
£1371,019
29.
30.
Gross profit
£44,000
£52,492
£55,062
£52,703
£204,257
31.
32.
Fixed costs
£10,000
£10,000
£10,000
£10,000
33.
34.
Net profit before tax
£34,000
£42,492
£45,062
£42,703
£164,257
35.
Less: tax@
23%
£7820
£9773
£10,364
£9822
36.
37.
Net profit after tax
£26,180
£32,719
£34,698
£32881
£126478
38.
Performance of simulation within a spreadsheet entails fist placing of random number generator (RNG) formula in every cell that designates either a random or corresponding uncertain accompanied by independent variable (BARLOW, 2005, pp23-56). Every random number generator (RNG) offers a sample observation from an appropriate distribution that depicts underlying range accompanied by frequency of probable numerical values for the variable. Once the corresponding random number generator (RNG) is put in place, the new sample values are produced automatically every time the spreadsheet is recomputed. The recalculate process of the spreadsheet n times, where n designates suitable number of either replications or scenarios, accompanied by numerical value of the bottom-line performance measure will be stored
b) the table below provides results for scenario 1, 2 and 3
I. Scenario 1: Suppose inflation is predicted to be 6% during the second year and 8% thereafter. What would be the profitability of the new carving knife in these circumstances?
II. Scenario 2: Suppose the Company decides to sell 3% more units per year as well as increasing the current selling price by 10% per year. What would be the effect on the 'net profit after tax' for each year?
III. Scenario 3: Suppose that Kitchenware Products decides to vary the projected sales volume over a range from -5% to 10% of the initial assumption. The company wants to see how the 'Net profit after tax' would change as the projected sales volume changes.
Results table for the three scenarios are computed in this table
Year 1
Year 2
Year 3
Year 4
TOTAL
Base case
INFLATION
3%, 5%, 6%
£26180
£32,719
£34,698
£32,881
£126,478
Scenario 1
High inflation
6%, 8%, 8%
£26180
£25,706.
£18,240
£7425
£77,731
Scenario 2
Volume (3%) price (10%) INCREASES
£52,576
£62, 895
£68,982
£71,678
£256,131
Scenario 3
Varying sales volume from -- 5% to + 10%
Sales Vol=-5%
£24,486
£30,698
£32,578
£30,852
£118,614
Sales Vol= 0%
£26180
£32,719
£34,698
£32,881
£126,478
Sales Vol= +5%
£27, 874
£34, 740
£36,818
£34,910
£134,342
Sales Vol=- 10%
£29,568
£36,761
£38,938
£36,939
£142,206
Financial planning model, Kitchenware Products is then utilized in analysis of the subsequent 'what-if' scenarios. The analysis of the scenario 1 that mainly pertain the inflation that was predicated as six percent during the corresponding second year accompanied by the subsequent eight percent in the subsequent years in the determination of the anticipated by the productivity of the new carving knife within the underlying circumstances (BARLOW, 2005, pp23-56). This will be mainly undertaken through the drawing of the results from the task set within the rows as depicted within the financial planning model. The results table will hold a lasting record of net profit after tax for every circumstance (Barlow, 2005, pp45-67). It mainly entails copying of the underlying net profit-after tax figures from the corresponding worksheet as depicted on the Row 37 into the corresponding row 4. The analysis of the scenario involves copying by hand devoid of utilizing excel formulae. The terminology 'What if ' scenarios mainly refer to the underlying base case of the numerical figures in the tables of the financial planning business model. It is significant to undertake checking of the entire changed cells that have altered back to their corresponding original numerical values prior to processing to a new 'What if '.
In order to aid Kitchenware Product in visualizing the underlying table that pertains to figures within the scenario 3,an individual require to draw a graph utilizing Excel's chart depicting the prevailing comparison of the corresponding profits for the diverse sales volumes taking note of the profit peak within year 3 (BARLOW, 2005, pp23-56). In the underlying report that pertains to every scenario's of net profit after tax is adequate. Nevertheless, in case there is complete spreadsheet details are demanded for a scenario, then the underlying Excel's Scenario Manger can then be utilized. When utilizing the Scenario Manger, which is activated through the Tool Scenario command, the corresponding Base Case scenario that ought to included in ensuring that the existing original BASE CASE cell numerical values are correctly restored (BOSLAUGH, 2012, pp202-267). Scenario 2 that entails the decision of selling three percent of the units every year accompanied by the escalation of the current selling price by ten percent every year, which mainly effecting the prevailing net profit after tax every year (Barlow, 2005, pp45-67). The analysis of this scenario mainly entails drawing of the results from the task set within the rows as depicted within the financial planning model. The results table will hold a lasting record of net profit after tax for every circumstance. It mainly entails copying of the underlying net profit-after tax figures from the corresponding worksheet as depicted on the Row 37 into the corresponding row 4. The analysis of the scenario involves copying by hand devoid of utilizing excel formulae. The terminology within the scenarios mainly refers to the underlying base case of the numerical figures in the tables of the financial planning business model (Barlow, 2005, pp45-67). It is significant to undertake checking of the entire changed cells that have altered back to their corresponding original numerical values prior to processing to a new 'What if '.
Similarly, scenario 3 which entails decision of the variation of the anticipated sales volumes over the existing range from the prevailing five percents of sales volumes to the corresponding ten percent within the initial assumption of the Kitchenware Products (BARLOW, 2005, pp23-56). The company demands to realize net profit after tax alteration as predictable sales volumes alteration. The analysis process mainly entails visualizing on the underlying table that pertains to figures within the scenario 3,an individual require to draw a graph utilizing Excel's chart depicting the prevailing comparison of the corresponding profits for the diverse sales volumes taking note of the profit peak within year 3. In the underlying report that pertains to every scenario's of net profit after tax is adequate (Barlow, 2005, pp45-67). Nevertheless, in case there is complete spreadsheet details are demanded for a scenario, then the underlying Excel's Scenario Manger can then be utilized. When utilizing the Scenario Manger, which is activated through the Tool Scenario command, the corresponding Base Case scenario that ought to included in ensuring that the existing original BASE CASE cell numerical values are correctly restored. Scenario 2 that entails the decision of selling three percent of the units every year accompanied by the escalation of the current selling price by ten percent every year, which mainly effecting the prevailing net profit after tax every year. The analysis of this scenario mainly entails drawing of the results from the task set within the rows as depicted within the financial planning model (BOSLAUGH, 2012, pp202-267). The results table will hold a lasting record of net profit after tax for every circumstance. It mainly entails copying of the underlying net profit-after tax figures from the corresponding worksheet as depicted on the Row 37 into the corresponding row 4.
You’re 83% through this paper. Sign up to read the full paper.
Sign Up Now — Instant Access Already a member? Log inAlways verify citation format against your institution’s current style guide requirements.