Scenario
Tom and Sally were trying to get a handle on their budget. They heard that MS Excel could help them with that. Tom is going to school and has cut back on his hours. Sally has finished her associate’s degree and is working full time. They want to look at the last three months of their income and expenses to see where they can make changes.
Part I – The Spreadsheet
First, in an Excel Spreadsheet, create a budget to show Tom and Sally’s income, expenses, and money left over each month (Net). Create formulas in your spreadsheet for your calculations using the following data:
- Tom brought home $1,000 a month for January, February, and March.
- Sally brought home $1,900, $2,000, and $1,975 respectively for those three months.
- They paid $1,000 for rent and utilities each month.
- They paid $88 for insurance each month.
- They paid $60 for cell phones and $60 for Internet/TV each month.
- They spent $600, $750 (due to a big birthday bash), and $500 on food respectively.
- Their car payment and gas came to $225, $250, and $300 respectively.
- Entertainment and gifts were $75, $100, and $45 respectively.
- They paid a little extra on their credit card $150, $125, and $100 respectively.
- They allocated $200 each month for personal grooming and health care.
- They put $100 each month into savings.
- Miscellaneous Expenses came to $200, $100, and $400 respectively.
A sample budget layout is included below as an example of one way to lay out a budget.
Part II – The Chart
Once you have worked out the budget spreadsheet, use the tabs at the bottom of the page to open another sheet. Here you will create a chart similar to the example below using the tools you learned about in the lectures and the textbook. On the new sheet:
- Create a column chart to visually represent their monthly expenses. If you have a challenge getting the chart on this sheet, research how to move your chart using your reading or Help (F1). You might also try right clicking on the chart.
- Add a descriptive title to the chart.
Part III – Changing Values
- Copy everything from Sheet 1 (the budget calculations) and paste it into a new worksheet.
- Change values for Sally’s March income to $200 more. Let’s say that they didn’t use as much heat in March and their utilities were $75 less (you could use a formula to figure out $75 less but you don’t have to). See how the totals change automatically if you used formulas correctly. If the values did not change, check your formulas and try them again. Seek help if you can’t figure this out.
- Rename each of your three spreadsheets with descriptive names.
Identify the strategic issues surrounding the problem This is the section where you clearly state the problem or issue under review. This statement of problem or issue will direct your problem solving. The problem statement doesn’t need to be excessively long. Identify the alternative actions This is where you determine the alternatives for the problem you have identified (in step 1) will be solved. For Dolci there are two methods of identifying profit-center profitability. It may be a good idea to state that two analyses will be performed to study the cost allocation and profitability of each product line.
Obtain information and conduct analyses of the alternatives For each case, this is where you will SHOW the computations or utilization of the decision tool. Questions at the end of the case are provided to give you a clear direction of the content required in this area. These analyses performed here will provide the direction for the next section. The analysis can be attached as an exhibit, much like the exhibits in Dolci.
Based upon strategy and analysis, choose and implement the desired alternative Now that you have done the number crunching in Step 3, articulate the desired alternative and why. Make sure that your “solution” in this step solves the problem that you have identified in the first step. Often, students come up with a solution, but it does not solve the problem that was identified. Well, then something has to give here…. either the problem statement needs to be re-written or the recommendation based on analysis is off.
This is where the problem and its solution should clearly support the strategy of the company. This case write-up is something that would be used by the executive management in determining strategic direction for the entity. Therefore, it is NOT enough to state that one analysis is better than the other. What you need to do is recommend the ACTIONS that should result from the analysis. Provide an ongoing evaluation of the effectiveness of the implementation Since a time-series analysis is a longer-term time frame, our course does not provide that data. You may wish to comment about continued review of the implementation decision.
Required: Your case should be written up using the five steps of Strategic decision making as shown on page 17.
The following questions should be incorporated into the appropriate area of the response.
1) What is the competitive environment faced by AA as it considers launching the Correre?
2) How has AA reacted to the changing world for luxury automobiles?
3) How does the Correre project link with AA’s strategy in terms of market coverage?
4) Develop an Importance Index for Each Component.
5) Develop a value index.
6) How do suppliers’ factor into the target costing process? Why are they so important to the success of the AA Correre?
7) From the value index, make appropriate recommendations in keeping with the strategy of AA
Instructions:
- Answer Question All the questions
- Show all your workings
QUESTION ONE
- In a bank, 20 customers on the average are served by a casher in an hour. If the service time has exponential distributions, what is the probability that;
- It will take more than 10 minutes to serve a customer?
- A customer shall be free within 4 minutes?
- Briefly explain what you understand by the following terms:
- Arrival pattern
- Service discipline
- Service channel
- Service distribution
- ABC Manufacturing Company purchases 9,000 parts of a machine for its annual requirement, ordering one month’s usage at a time. Each part costs Kshs. 20. The ordering cost per order is Kshs. 15 and the carrying charges are 15% of the average inventory per year. You have been asked to suggest a more economical purchasing policy for the company. What advice would you offer and how much would it save the company per year?
- The following data for the activities of a project are available:
| ACTIVITY | IMMEDIATE PREDECESSOR | DURATION (DAYS) |
| A | - | 6 |
| B | - | 4 |
| C | - | 7 |
| D | A,B,C | 5 |
| E | A,B,C | 6 |
| F | D | 9 |
| G | E | 4 |
| H | F,G | 8 |
| I | F,G | 6 |
| J | H | 7 |
Required:
- Draw the network, number the nodes, find the project completion time and calculate total, free and independent floats for the activities
- When the activity duration of G is doubled, identify the critical path
- Define the following terms as used in game theory:
- Dominance
- Saddle point
- Mixed strategy
- Value of the game
- Consider the two-person zero sum game between players A and B given the following pay-off table:
| Player B Strategies | |||||
| 1 | 2 | 3 | 4 | ||
| Player A Strategies | 1 | 2 | 2 | 3 | -1 |
| 2 | 4 | 3 | 2 | 6 |
Required:
- Using the maximin and minimax values, is it possible to determine the value of the game? Give reasons.
- Use graphical methods to determine optimal mixed strategy for player A and determine the value of the game.
QUESTION TWO
- Discuss FIVE limitations in a queuing model
- The milk plant at a city distributes its products by trucks, loaded at the loading dock. It has its own fleet of trucks plus trucks of a private transport company. This transport company has complained that sometime its trucks have to wait in line and thus the company loses money paid for a truck and driver that is only waiting. The company has asked the milk plant management either to go in for a second loading dock or discount prices equivalent to the waiting time. The following data are available:
Average arrival rate (all trucks) = 3 per hour
Average service rate = 4 per hour
The transport company has provided 40% of the total number of trucks. Assuming that these rates are random according to Poisson distribution, determine;
- The probability that a truck has to wait
- The waiting time of a truck that waits
- The expected waiting time of company trucks per day
QUESTION THREE
- Define Inventory. List any two advantages and two disadvantages of having inventories
- A particular item has a demand of 9,000 units per year. The cost of one procurement is Kshs. 100 and the holding cost per unit is Kshs. 2.40 per year. The replacement is instantaneous and no shortages are allowed. Determine:
- The economic lot size
- The number of orders per year
- The time between orders
- The total cost per year if the cost of one unit is Kshs. 1.00