CLASS / COURSE: Excel Worksheet

QUESTION DESCRIPTION:

Assignment 7  Introduction:

This Assignment—Exam is a Case Study that has been designed to convey a simulated business environment needed for gathering information, preparing Excel worksheets, charts and answering specific Critical Thinking Questions (CTQ’s) for the purpose of making a recommendation to the Logistics Manager (LM).

You may want to add your own analysis, in the form of worksheets and/or charts along with any of your Critical Thinking Questions and Answers in preparation for making your recommendation.

To date, as a student, you have worked on what-if scenarios right after the Study Guide exercises in completing the assignment work. The result has been that it was relatively easy to identify which cells were used for data input and which were used to store formulas for calculated results.

To utilize the full power of Excel, a worksheet designer needs to keep in mind that all of the input cells, whenever possible, should be in a separate section of the current worksheet or on a separate worksheet and not included with the output results. When this methodology is used, the data input area is isolated to a section or to a worksheet(s) and this ensures that it is very easy to identify the data cells that can be changed. When data input cells are included in the calculation area or amongst the data output cells of the worksheet, data cells that need to have the number changed can be overlooked or worse still, a number is entered into a cell that contains a formula and the formula is destroyed. Please design your solution with distinct input and output worksheets, thank-you.

Exam Knowledge and Skills:

The Exam has been designed so that the student will demonstrate the knowledge and skills for the following:

·       items listed in the To Do List that are shown on page 2,

 

·       linking data and formulas on the output worksheet from the input section or worksheet(s),

 

·       applying the knowledge and skills learned to-date,

 

·       developing new knowledge and skills,

 

·       answering the Critical Thinking Questions, i.e. those which are posed as notes to yourself after meeting with the LM, and

 

·       Demonstrating your own initiative in developing CTQ’s with answers to questions that that you think would be helpful with your response to the LM.

 

Exam — What-if Analysis:

Case Information:

A1 Gas Barbeques Inc. (A1GBI), is a company located in Burlington, Ontario. It manufactures gas fireplaces, in three different (3) models, that are distributed throughout Canada and the United States.

During a recent meeting with the LM, she expressed her concern to you about acquiring some new production equipment. She believes that the current production equipment will not meet the company's sales’ needs and the company is about to make a serious mistake by not investing in new production equipment, soon enough. However, she can’t prove it, but she has a gut feeling about it. She has asked you to prepare the analysis to prove whether the investment should or should not be made. She wants to present a comprehensive analysis for whatever results that you are able to prepare.

 

Questions:

Before starting to gather the data necessary for the analysis, you list the worksheets and/or charts that you think you will need to prepare, in order to answer the questions that you think the LM would ask. As well, you list several reminders to yourself to create worksheets and/or charts to determine if the information can be used. The questions and reminders are as follows:

 

1. Prepare a worksheet to determine the Unit Sales Volume, by product, starting with the current year and then for the next five (5) years, i.e. each of the project years, thereafter, from one (1) through five (5), inclusive?

 

2. Prepare a worksheet with two (2) data tables to determine the Capacity Utilization Rate, for the company, for the current year and for each of the project years.

 

·       one data table will be for the current production equipment, and

 

·       the second data table will be for the new production equipment .

 

Note: for those students who have not encountered the Capacity Utilization Rate4 concept, it is the Actual Hours Used for production divided by Hours Available for production stated as a percent to one decimal place.

 

3. Assuming that A1GBI exceeds capacity during the next 5 years, in what year does the company reach or exceed the 100% utilization rate?

 

·       for the current production equipment state the year and the Capacity Utilization Rate for that year, and

 

·       for the new production equipment state the year and the Capacity Utilization Rate for that year, if applicable.

 

·       Excel note – use colour fill for the cell(s) that meet the criteria, i.e. equal to or greater than 100%, and then use a text box to record a message, as a reminder to yourself, to include this data in the memo to the LM.

 

4. Prepare a worksheet with two (2) data tables, one for current and one for new production equipment, to determine, by product and for the products in total for the current year’s sales, only, the:

 

·       Sales Revenue dollars,

 

·       Contribution Margin –Net change dollars, and

 

·       Contribution Margin –Net change percent (stated as a percent of Sales Revenue dollars)

 

5. Add a data table, to the previous worksheet, to calculate the Net change between the current and the new production equipment by product and for the products in total, for the current year’s sales, only, the:

 

·       Contribution Margin dollars, and

 

·       Contribution Margin, expressed as a Net Change Percentage.

 

6. Using the new production equipment data:

 

·       create a Trend Analysis worksheet, starting with the current year and for the 5-year life of the project, that displays the Total Sales Revenue and Total Contribution Margin, dollars and percents for each, starting with the current year as the base year equal to 100%.

 

7. Using the new production equipment data, prepared in the previous worksheet:

 

·       create a dual axis Trend Analysis chart, in a new chart sheet, starting with the current year and for the 5-year life of the project that displays the Total Sales Revenue and Total Contribution Margin, dollars and percents for each, starting with the current year as the base year equal to 100%.

 

8. Using the new production equipment data, create a data table for the creation of two (2) Pie Charts for the current year, only:

 

·       one chart is to display the Sales Revenue Dollars by product, and

 

·       the second chart is to display the Contribution Margin Dollars by product (display results as a percent as we did in Module 2, for both charts). – display both pie charts on the same worksheet.

 

9. Using the new production equipment data created in the Trend Analysis worksheet, create a data table to:

 

·       calculate the Contribution Margin Dollar increases attributable to the growth in sales in each of the 5 years?

 

10. Create a Capital Budget worksheet for this project, starting with the one created from the Study Guide exercise. This will be for the new production equipment, to answer the following::

 

·       does the project meet the Required Rate of Return for A1GBI; yes or no,

 

·       what were the actual results, and

 

·       observations?

 

11. Create a copy of the Capital Budget worksheet, then use it to calculate the actual Rate of Return, using Goal Seek, to answer the following:

 

·       what is the Expected Rate of Return that could be achieved,

 

·       how would that compare to the company's RRR, and

 

·       observations?

 

 

12. Create a copy of the Capital Budget worksheet, then use it to calculate the amount of capital equipment investment that management could make, using Goal Seek, to answer the following:

 

·       how much could the company invest in the new production equipment,

 

·       how would that compare to the original investment, and

 

·       observations?

 

13. Create a Solver model for the Production Planning Manager.

 

·       details provided in the interview with the PPM.

 

Interviews:

 

To prepare for the project you have interviewed various people within the organization and have gathered the following information and prepared several data tables, which are displayed at the end of this case.

 

Accounting Manager (AM) —

Your visit with the Accounting Manager (AM) yields the following information, plus the information contained in the data tables.

 

1. The average Capital Cost Allowance (CCA) rate for this type of manufacturing equipment is set by Revenue Canada and the asset class will continue to remain open.

 

2. The company's current planning strategy is to exclude inflation from any planning models because the assumption is that the price increases from suppliers of material and increases in labour rates will be offset by selling price increases to the customer. This assumption is based on the fact that the historical inflation rate in Canada and the United States has been in the one to two percent (1% to 2%) range during the past three (3) years. It is forecasted to remain at this level for the next three (3) to five (5) years.

 

3. At the present time, the sales volume increase isn’t great enough to require a working capital change, therefore the decision was made to exclude it.

 

4. The AM, estimated that the savings that the Engineering department had identified would result in a ten percent (10%) decrease in the Variable Cost per Unit. The company cost system includes the following in its variable cost per unit:

 

·       direct materials,

 

·       direct labour and

 

·       variable manufacturing overhead, i.e. excludes fixed manufacturing overhead.

 

5. Assuming that the new production equipment could be installed in the current year there would be a resulting savings in the Contribution Margin Dollars ($) per Unit. These total savings in the Contribution Margin Dollars would be considered to be a Cash-In for each year of the project.

 

During the discussion with the AM, you and he agreed that the total savings reflected in the Contribution Margin Dollars for production equipment would cover the cash savings for all cost reductions, which could include, but not limited to, the reduction in direct materials required for the product, reduction in scrap expense, lay-off or transfer from the department any of the production machine operator(s) and their associated benefits, excluding maintenance, which is classified as a fixed manufacturing overhead cost.

6. After a great deal of debate with the members of the management team it was decided that if the current production equipment’s utilization rate exceeded 100%, that the contribution margin generated by the sales increase would be considered to be a Cash-In for each year of the project, starting with the year that the

utilization rate exceeded 100%, and each year thereafter, for the balance of the project life.

 

Normally, the sales increase savings would not be considered a cash-in because the sales increase takes place whether or not new equipment is installed. However, in this case the sales increase, exceeding 100%, needs the new production equipment for the targets to be achieved, therefore the cash-in decision.

 

Note to student – re Capital Equipment Cost data table: several items in the data table state “-to be determined by the student:”. This means that you will need to decide what value is to be entered into the data table for use in the Capital Budget worksheet.

 

Engineering Manager (EM) —

Your visit with the Engineering Manager (EM) yields the following information, plus the information contained in the data tables.

 

7. The new production equipment is comprised of a series of automated workstations that cut and shape the sheet metal, followed by the welding of the shaped pieces into a barbeque.

 

8. The new production equipment being considered would have a capital cost, as shown in the Capital Equipment Cost data table. The salvage value of this new production equipment is expected to be approximately 10% of the capital cost after five (5) years of use.

 

9. In evaluating the potential savings attributable to the new production equipment the department identified a direct labour savings per unit for people directly involved in the production of the products. The calculated results are as shown in the new production equipment data table.

 

10. The EM indicates that the present fixed manufacturing overhead for the maintenance staffing level would decrease from two (2) employees to one (1) employee for a savings of $60,000 per year for that employee along with an additional savings in associated benefits of 22%.

 

11. The EM had been researching new production equipment on the market, and advised that the department had already spent $11,000 in time and travel on the project.

 

 

12. The EM also mentioned that – if capacity became an issue with this new equipment, additional work stations could be added that would increase the capacity by approximately 25% for very little additional cost.

 

Sales Manager (SM) —

Your visit with the Sales Manager (SM) yields the following information plus the information contained in the data tables.

 

13. Currently, a Unit Sales Forecast has been completed for the three barbeque models for the current year with the existing manufacturing equipment. This Unit Sales Forecast data is listed in the Sales data table.

 

14. The five (5) year project life forecasts a growth, compounded annually, in the number of units sold. The data table displayed at the end of this document, shows the annual growth rate to be used to calculate the compound growth.

 

Student Assistance: If you are unfamiliar with the concept of compound growth5, an example is provided in a separate Excel workbook that was attached to the email that had this document attached to it.

 

Production Planning Manager (PPM) —

Your visit with the Production Planning Manager (PPM) yields the following information, plus the information contained in the data tables.

 

15. Currently, the company is forecasting the production of the three barbeque models for the next year with the existing manufacturing equipment. The assumption is that the Minimum Production per Year or Constraints will be equal to the Unit Sales Forecast-Current Year and each year thereafter. In addition, the PPM is concerned that the Cost of Goods Sold budget could be cut by 10% (Inventory Investment from m2’s exercises). The PPM has advised the Sales Department that the Unit Sales Forecast for the Current Year could be cut by 15%. You offer to run a Solver model,  that would maximize the product mix at 85% of the current sales forecast and at 90% of the Cost of Goods Sold. You offer to include your findings in the report.

 

16. In addition, you received the Business Plan numbers for the various elements as set out in the current and new production equipment data tables. They are:

 

·       Production Hours per Unit, and

 

·       Total Hours available for the Year.

 

17. The Production Hours Available for the new equipment would remain as stated, in the new production equipment data table. The assumption is that the number of shifts per week would remain unchanged. The savings is generated by a drop in the number of hours per unit as stated in the new production equipment data table. Therefore, the same number of people could produce more units or fewer people would be required to produce the sales forecast requirements.

 

18. The PPM has agreed to accept the assumption provided by the Engineering and Accounting departments for the cost savings involved.

 

Required:

 

After preparing the questions and interviewing the various department managers you decide to produce the Excel worksheet(s), chart(s) and a Word file, necessary, to answer the questions and make recommendation(s) to the LM. See the To Do List on page 2, for more details.

 

·       The report needs to address the concerns of the LM, as a minimum, plus add any other items that you think the LM needs to know. Be sure to include specific numbers, in the report, to support your responses.

 

Excel/Word Tip: to use numbers from an Excel worksheet, without having to keyboard the numbers; in Word, select the Help topic, type copy from Excel, select a topic for assistance.

 

·       The introductory paragraph(s) of the report MUST include your recommendation(s) and support with specific numbers.

 

Rounding —

You may have a different preference for displaying the numbers for an Excel worksheet, but for this Case please do NOT round the calculated results, otherwise your results will be different than the results used for evaluation. It is also important that you link the cells, otherwise when you reenter a number you are rounding the number6.

 

Decimal Place Display —

Please use the following decimal place displays for your numbers:

 

·       unit quantity = 0 decimal place, e.g. 1,310

 

·       percent (%) = 1 decimal place, e.g. 36.5%

 

·       dollars ($) = 0 decimal place, e.g. $10,000

 

·       production hours per unit = 1 decimal place, e.g. 12.8

 

·       production hours available = 0 decimals, e.g. 7,500

 

Dollars and hours —

 

Ignore the decimal place numbers in your calculated results, because pennies and partial hours are not material to a decision. Note —do not round the numbers.

 

Integer values for Solver —

Use the integer constraint when using Solver.

SOLUTION DESCRIPTION: A1 Gas Barbeques Inc. (A1GBI) manufactures gas Fireplaces is in dilemma whether to invest in new production......

SUBJECTS / CATEGORIES:
1. Marketing
2. Finance
3. Financial Management
4. Accounting
5. Corporate Finance

DOWNLOAD QUESTION FILE: