Assignment Overview

Scenario: You are a consultant who works for the Diligent Consulting Group. Your client, the New Star Grocery Company, believes that there may be a relationship between the number of customers who visit the store during any given month (“customer traffic”) and the total sales for that same month. In other words, the greater the customer traffic, the greater the sales for that month. To test this theory, the client has collected customer traffic data over the past 12-month period, and monthly sales for that same 12-month period (Year 1).

Case Assignment

Using the customer traffic data and matching sales for each month of Year 1, create a Linear Regression (LR) equation in Excel, assuming all assumptions for linear regression have been met. Use the Excel template provided (see “Module 2 Case – LR –Year 1” spreadsheet tab), and be sure to include your LR chart (with a trend line) where noted. Also, be sure that you include the LR formula within your chart.

After you have developed the LR equation above, you will use the LR equation to forecast sales for Year 2 (see the second Excel spreadsheet tab labeled “Year 2 Forecast”). You will note that the customer has collected customer traffic data for Year 2. Your role is to complete the sales forecast using the LR equation from Step 1 above.

After you have forecast Year 2 sales, your Professor will provide you with 12 months of actual sales data for Year 2. You will compare the sales forecast with the actual sales for Year 2, noting the monthly and average (total) variances from forecast to actual sales.

To complete the Module 2 Case, write a report for the client that describes the process you used above, and that analyzes the results for Year 2. (What is the difference between forecast vs. actual sales for Year 2—by month and for the year as a whole?) Make a recommendation concerning how the LR equation might be used by New Star Grocery Company to forecast future sales.

Data: Download the Module 2 Case template here: Data chart for BUS520 Case 2. Use this template to complete your Excel analysis.

Assignment Expectations

Excel Analysis

Conduct accurate and complete Linear Regression analysis in Excel. Use Excel support to find information on linear regression in Excel: https://support.office.com/en-us/Search/results?query=linear+regression

Written Report

• Length requirements: 4–5 pages minimum (not including Cover and Reference pages). NOTE: You must submit 4–5 pages of written discussion and analysis. This means that you should avoid use of tables and charts as “space fillers.”
• Provide a brief introduction to/background of the problem.
• Your written (in Word) analysis should discuss the logic and rationale used to develop the LR equation and chart.
• Provide complete, meaningful, and accurate recommendation(s) concerning how the New Star Grocery Company might use the LR equation to forecast future sales. (For example, how reliable is the LR equation in predicting future sales?) What other recommendations do you have for the client?
• Write clearly, simply, and logically. Use double-spaced, black Verdana or Times Roman font in 12 pt. type size.

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "GET10" in your order"

Assignment Overview

You are the lead consultant for the Diligent Consulting Group. It is mid-October. One of your top clients, Sunshine Floor Barn, has just closed the books for the first three quarters of the year (January through September). Sunshine Floor Barn requests that you analyze the sales performance of its 5 product lines over this 3-quarter period. From past consulting work you have done for the company, you know that Sunshine Floor Barn has 4 regions and 18 total store locations. Each Regional Manager at the company has compiled the data for his/her region. The raw data provided consists of the sales revenue for each of the 5 premium flooring lines for all 4 regions and 18 locations for the first three quarters of the current year.

Case Assignment

The data have been provided in list format. Generate a Pivot Table Report with Charts. Use the Pivot Table and Charts to analyze the data. Following your in-depth analysis of the data, write a report to Sunshine Floor Barn in which you discuss and analyze the data, and make appropriate recommendations relative to how Sunshine Floor Barn should improve its sales performance going forward.

Assignment Expectations

Data: To begin, download the list data here: Data chart for BUS520 Case 3

Excel Analysis:

Provide accurate and complete Excel analysis (Pivot Table with Charts).

Written report:

• Length requirement: 4–5 pages minimum (not including Cover and Reference pages). NOTE: You must have 4–5 pages of written discussion and analysis. This means you should avoid use of tables and charts as “space fillers.”
• Provide a brief introduction to/background of the problem.
• Using the Pivot Table and Pivot Charts, discuss and analyze the data, noting key highs and lows, trends, etc.
• Include charts from your Pivot Table to support your written analysis. (Please do not use charts as “space fillers.” Instead, use them strategically to support your written analysis.)
• In a “Recommendations” section, give clear, specific, and meaningful recommendations that Sunshine Floor Barn should use to improve overall company sales.
• Be sure to consider highs, lows, and trends in the data. Which cities are the highest performers? Lowest? Which regions and quarter had the highest sales? Lowest sales? Consider what may be driving the numbers: Poor marketing? Outstanding marketing strategies? Inventory management? Seasonal sales? Other? There are innumerable possibilities. Your role is to reflect on the data, and ultimately, to use the data to give useful recommendations.
• Write clearly, simply, and logically. Use double-spaced, black Verdana or Times Roman font in 12 pt. type size.
• Have an introduction at the beginning to introduce the topics and use keywords as headings to organize the report.
• Avoid redundancy and general statements such as “All organizations exist to make a profit.” Make every sentence count.
• Paraphrase the facts using your own words and ideas, employing quotes sparingly. Quotes, if absolutely necessary, should rarely exceed five words.

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "GET10" in your order"

Assumed Certainty: Multi-Attribute Decision Making (MADM)

Scenario: You are the Vice President of Franchise Services for the Lucky restaurant chain. You have been assigned the task of evaluating the best location for a new Lucky restaurant. The CFO has provided you with a template that includes 6 criteria (attributes) that you are required to use in your evaluation of 5 recommended locations. Following are the 6 criteria that you will use to evaluate this decision:

Traffic counts (avg. thousands/day)—the more traffic, the more customers, and the greater the potential sales. 

Building lease and taxes (thousands $ per year)—the lower the building lease and taxes, the better.

Size of building (square feet in thousands)—a larger building is more preferable.

Parking spaces (max number of customers parking)—more customer parking is preferable.

Insurance costs (thousands $ per year)—lower insurance costs are preferable.

Ease of access (subjective evaluation from observation)—you will need to “code” the subjective data. Use Excellent = 4, Good = 3, Fair = 2, and Poor = 1.

Now that you have collected the data from various sources (your CFO and COO, local real estate listings, personal observation, etc.), you have all the data you need to complete an analysis for choosing the best location. Download the raw data for the 5 locations in this Word document: BUS520 Module 3 SLP.docx

Assignment

Review the information and data regarding the different alternatives for a new restaurant location. Then do the following in Excel:

Table 1: Develop an MADM table with the raw data.

Table 2: Convert the raw data to utilities (scaled on 0 to 1). Show the utility weights in a second table.

Table 3: Develop a third table with even weights (16.7%) for each variable.

Evaluate Table 3 for the best alternative.

Table 4: Complete a sensitivity analysis by assigning weights to each variable.

In a Word document, do the following:

• Discuss the process used to put together Tables 1–4 above.
• Provide the rationale you used for choosing for each of the weights you used in Table 4.
• Give your recommendation of which location the company should choose (based on results of Table 4).

SLP Assignment Expectations

Excel Analysis

Complete Excel analysis using MADM (all four tables noted above must be included).

Accurate Excel analysis (Excel file includes working formulas showing your calculations; all calculations and results must be accurate).   

Written Report

• Length requirements: 2–3 pages minimum (not including Cover and Reference pages). NOTE: You must submit 2–3 pages of written discussion and analysis. This means that you should avoid use of tables and charts as “space fillers.”
• Provide a brief introduction to/background of the problem.
• Discuss the steps you used to compile the Excel analysis (i.e., the four tables).
• Discuss the assumptions used to assign weights to each variable of your sensitivity analysis (Table 4). That is, provide the rationale for your choice of weights for each variable.
• Provide a complete and meaningful recommendation related to the location that should be chosen as a new site.
• Write clearly, simply, and logically. Use double-spaced, black Verdana or Times Roman font in 12 pt. type size.
• Have an introduction at the beginning to introduce the topics and use keywords as headings to organize the report.
• Avoid redundancy and general statements such as “All organizations exist to make a profit.” Make every sentence count.
• Paraphrase the facts using your own words and ideas, employing quotes sparingly. Quotes, if absolutely necessary, should rarely exceed five words.

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "GET10" in your order"

You are a Consultant for the professional service firm, BUSI 2083 LLP. Your firm specializes in providing a wide variety of internal business solutions for different clients. One of the partners in your practice is impressed with the work you have completed to date and would like to give you additional responsibility. She has asked you to take the lead on this engagement with the hope that a successful outcome may lead to your promotion to Senior Consultant. You take the background files from the partner and get started.

Perfect Stitch Replica’s Limited, a nationwide distributor of low-cost imitation clothing, has an exclusive agreement for the distribution of the clothing. Sales have grown so rapidly over the last few years that it has become necessary to add new members to the management team. To date, the company’s budgeting practices have been minimal, and at times, the company has experienced a cash shortage. You have been given responsibility for all planning and budgeting. Your first assignment is to prepare a master budget for the next three months, starting April 1. You are anxious to make a favourable impression and have assembled the information below.

Additional Information

The clothing is sold to retailers for an average price of $10 each. Recent and forecasted sales in units are as follows:

Ending inventories should be equal to 40% of the next month’s sales in units.

The average cost of the clothing is $4 each. Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 20% of a month’s sales are collected by month-end. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible.

The company’s monthly operating expenses are given below:

All operating expenses are paid during the month, in cash, with the exception of depreciation and insurance. Insurance is paid on an annual basis, in November of each year. The company plans to purchase $16,000 in new equipment during May and $40,000 in new equipment during June; both purchases will be paid in cash. The company declares dividends of $15,000 each quarter, payable in the first month of the following quarter. The company’s balance sheet at March 31 is given below:

The company wants a minimum ending cash balance each month of $50,000. All borrowing is done at the beginning of the month; any repayments are made at the end of the month. The company has an agreement with a bank that allows it to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month, and for simplicity, assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $50,000 in cash.

Prepare the following budgets for the first three months of 2016:

1. A sales budget by month and in total
2. A schedule of expected cash collections from sales, by month and in total.
3. A merchandise purchases budget in units and in dollars. Show the budget by month and in total.
4. A schedule of expected cash disbursements for merchandise purchases, by month and in total.
5. A cash budget. Show the budget by month and in total.
6. A budgeted Income Statement for the three-month period ending June 30. Use the variable costing approach.
7. Provide a budgeted Balance Sheet as at June 30^th

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "GET10" in your order"

Instructions

Individually students will develop a formal response to the problem(s) posed in the case, addressing the following areas within the analysis, and using the same headings. (Numbers in brackets do not necessarily correspond to length but represent the weight that will be given to each section in the grading):

1. Issue Identification (5%): Identification of the problem/issue that must be resolved or decision that must be made. Phrase the problem/cause in the most succinct way possible. Think about:
   1. Differentiating the immediate from the basic problem
   1. The implications of the problem(s)
   1. Identifying the root cause of the problem(s)
   1. Determining the decision facing the key person(s)?
2. Identification of Key Success Factors (10%): Identify the company-specific factors in point form that are absolutely critical to the success of the organization. These are the factors that, if ignored, will mean the project will probably fail. Include the following considerations:
   1. What factors must be managed successfully for the company to prosper?
   1. Key success factors should reflect the top priorities of the organization in this particular case (eg., quality, productivity, low cost leader, etc.)
   1. These factors are part of the criteria against which you will evaluate solutions (along with basic criteria such as profit)
3. Identification of Alternatives (5%): Identify alternative solutions. Only deal with feasible alternatives. In the next three sections, analyse all alternatives against criteria set out in key success factors and basic requirements (eg., profitability).
4. Quantitative Analysis (40%): Push numbers in an analysis that is relevant to the issue at hand. Differentiate between what is relevant and what is irrelevant.
5. Qualitative Analysis (30%): Be sure to analyze qualitative issues – they need discussion in most cases. In particular, analyze alternatives in light of key success factors – will this alternative solve the problem and fit with our key success factors.
6. Recommendation on Course of Action (5%):  State your recommendation. State briefly the justification for your recommended course of action. Make sure your recommendation flows out of your quantitative and qualitative analyses. Tie your recommendation back to the key success factors. The solution and implementation shout fit with problems and criteria identified above.
7. Circumvention of Potential Problems (5%): If there could be problems with your recommendations, state them. As well, suggest ways to overcome these problems – a contingency plan to address potential difficulties.

There is no set length to the report, but clear, succinct and concise language and organization will be considered favourably in the grade.

Students will submit the final report as a word document through the submission link below.

The Case

You are a Senior Consultant for the professional service firm, BUSI 2083 LLP. Your firm specializes in providing a wide variety of internal business solutions for different clients. It is your final week on the job and a Manager asks you for some help prior to your departure. Eager to leaving a lasting impression, you start reading the background information provided by the Manager.

Lesley Donovan is the controller for the East division of Explorer Ltd. Jason Conner, head of plant engineering, has just left Donovan’s office after presenting three alternatives for submission in the capital expenditure budget for the fiscal year 2014. The budget is due to the CEO in two days and therefore Donovan realizes that time is of the essence.

Conner has outlined the following alternatives to replace an outdated milling machine:

1. build a general purpose milling machine;
2. buy a special purpose numerically controlled milling machine; or
3. buy a general purpose milling machine.

Explorer Ltd. is a well-established company. The company was set up about 30 years ago by two brothers Dan and Kevin Thompson, in Huntsville, Ontario, to produce accessories for the automobile industry. The Central division continues to serve the auto industry, and is the largest division in the company with sales of $35 million annually. Dan’s son is now head of this division. Kevin is still active in the company and is the Chief Executive Officer (CEO). His office is located in Toronto.

The parts division supplies seals to the mining and petrochemical industry from a plant in Toronto. This division is only ten years old and until 2010 was highly profitable. As a result of the downturn in the sector of the economy, sales in 2012 were only $12 million.

The East division, located in Scarborough, is the engineering division. Full-time employees tend to work approximately 2,000 hours in the division. Regular product lines include industrial fans, industrial cooling units, and refrigeration units for industrial users. The division is highly capital-intensive and sales tend to be directly related to general economic conditions.

Each division runs independently and performance is based upon budgeted return on investment. Bonuses are paid if the budget target is achieved. Annually, each division prepares a detailed budget submission to Kevin, outlining expected profit performance and capital expenditure requests. The milling machine proposal is part of the capital expenditure request.

The 2013 pro forma income statement for East division is set out below:

Jason Connor has pointed out to Donovan that the existing machine is not only outdated but maintenance costs are becoming prohibitive. Jason also noted that maintenance costs of new general purpose machines are only $26,000 while special purpose machines can save an additional $14,000 in maintenance. Also there would be a significant savings in insurance as the price for a general purpose machine would drop to $3,000 while a special purpose machine would be 67% higher than the general purpose machine. The machine has no market or salvage value and he is sure that its book value is now zero. The trouble is that he doesn’t know which proposal is best for the company. In addition to the cost and revenue date provided, Connor provided comments on each alternative below:

1. Build a general purpose machine:
   1. This machine can be built by East division. The division is below capacity at present as a major contract has just been completed. The division could thus produce the machine without affecting revenue-producing activity, but it will take six months to complete. The machine is expected to last five years and have no salvage value because removal costs will probably equal selling price.
   1. Connor believes that the division has the technical expertise to undertake the work. In 2012, the division produced a specialized drilling machine that has proven very successful. Connor pointed out that David Williams, chief engineer, loves the design challenge of new machines. Donovan sat down with Connor and produced the following cost estimates:

• Donovan argues that this job should also bear a proportion of administrative costs; she suggests $12,000.
• Buy a special purpose machine:
  The advantage of this special purpose machine is that only one operator is required and output per hour could increase by 25%. In addition, maintenance costs are significantly reduced because microchip circuitry is employed. 
  Connor points out that this machine is state-of-the-art and would probably mean that new work could be taken on. A numerically controlled machine required extensive training of operators. In total, 26 weeks are spent in the supplier’s factory located in Florida. While the training is going on, the supplier provides an operator to work the machine without charge. Expected costs of this training period including hotel, per diem, and travel will cost $3,000 per week, excluding the operator’s labour which is set at $15 per hour. 
  The machine costs $625,000, and the supplier guarantees the salvage value of $25,000 at the end of five years. It is available immediately. It is estimated the machine can generate sales of $243,750 annually at full capacity and require $19,500 in direct materials cost. While the direct material costs are equivalent, the level of sales for the general purpose machine are $48,000 lower than the special purpose machine.
• Buy a general purpose machine:
  The purchase price of this machine is $295,000 and cost levels associated with the machine are expected to be the same as the general purpose machine built by the company because the technology is similar. The salvage value of the machine net of removal costs, is estimated to be $5,000 in five years.  It can be delivered immediately.

General comments

The required rate of return for this investment class has been set at 8% by Kevin Thompson.

Required

Prepare the budget submission to Kevin.

"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "GET10" in your order"