Excel Assignment 2

BSIS 105

Assignment 5 – Part 2

Fall 2014

This is the second part of the assignment dealing with beginning and intermediate level Excel. In this assignment you will change the profitability and cash budgets you developed in the first part of this assignment. You are provided with a correct answer to the first part of the assignment that you should use as a template for this part of the assignment.

As discussed in part 1 of the assignment, the task is to develop both a profitability budget and a cash budget for a potentially new product. You will use the answer to the first part of the assignment (which is provided) as the starting point for this part of the assignment. The assumptions involving the new product will be different and you will be asked to consider additional factors that will introduce more reality into your analysis.

Revised Assumptions

Many of the assumptions from part 1 of the assignment stay the same in part 2. However, management has decided that the previous assumptions were too simplistic and need to be made more realistic. The assumptions that stay the same are as follows:

Per Unit Raw Materials Production Cost - $450 Per Unit Labor Production Cost - $200

Sales Price - $850

Advertising Cost per Month - $50,000

Additional Sales Costs per Month - $20,000

Management believes that both the amount of initial sales and the monthly rate of growth are both determined by the general economic conditions and that these conditions may change determined by when the product is released for sale. Based on this they predict that the initial sales and the monthly sales growth are as follows:

Economic Conditions	Initial Monthly Sales	Monthly Percentage Growth in Sales
Depression	500	-2.00%
Deep Resession	800	-1.00%
Resession	1000	0.00%
Shallow Resession	1050	1.00%
Slow Growth	1100	1.50%
Growth	1200	2.50%
Fast Growth	1500	4.00%

For the cash flow part of the assignment you will use the same assumptions as before:

· Assume that initially there is no cash available for the project (i.e. beginning cash balance of zero.)

· The present production facility has plenty of floor space to produce the new product, but there is specialized equipment needed to be purchased and installed. The purchase price and installation costs total $650,000. This amount needs to be paid the month before production begins (that is, in month -1).

· Production of the product will be in the month prior to the month in which it is sold. That means that production starts in month 0 and the number of units produced will be the amount sold in the next month (month 1).

· Raw materials for production must be purchased the month prior to the month in which the goods are produced. That is, the raw materials for the first production run must be purchased in month -1 since production starts in month 0.

· Labor to produce the goods is paid the same month the goods are produced.

· Advertising is always prepaid. Advertising for the good will start in month 1, the first month of sales. That means that the advertising for month 1 must be paid in month 0.

· The sales costs are paid the same month they are incurred.

· Sales revenue is collected the month after the sale.

Management has one additional item. The productive capacity of the facility only allows for production of 1,100 units per month. Due to the high cost of building more capacity, management will out-source the production of all units greater than 1,100 units in any month. Out-sourcing involves the direct payment to the vendor (out-sourcing company) for the goods. They charge us $700 per unit. Of course, for these units we will have no labor or materials costs. As with our own production, the units will be produced and paid for the month before they are sold. Note that in order to simplify your work, neither the productive capacity of the plant (1,100 units) nor the cost of out-sourced units ($700) are parameterized.

I am providing a template for you to use that is based on the answer to part 1 of the assignment. As before, there are three tabs on the spreadsheet. The first tab contains the problem parameters and a summary of the calculated income for the year and the ending cash balance. I have also added a table that shows the unit sales and the percentage increases associated with various economic conditions. In addition there is a parameter field that allows you to select the economic condition. I have restricted this field so that you may only enter the values in the table. The second sheet contains the detailed calculations for each month of the first year for the profitability budget. The third tab is the cash budget. As required by part 1 of the assignment, both of these spreadsheets are parameterized so that any changes to the parameters in the first sheet will be automatically calculated in the profitability budget and the cash budget.

Once you have developed your spreadsheet, you need to analyze your results. First you will answer questions concerning the results of your calculations and then you will do some sensitivity analysis. The answers to the questions and the sensitivity analysis should be presented in an MSWord document that presents your results and also provides analysis of the situation.

Now answer the same questions I asked in part 1, but using different economic conditions.

Question 1: Looking only at the profitability budget does producing and selling this product look like a good business decision under each of the seven different economic conditions?

Question 2: Now when you include the cash budget, comment on the soundness of producing and selling this new good under each of the seven economic conditions.

Submit both your Excel workbook and your MSWord document electronically by submitting it in the appropriate assignment in Blackboard. You must also bring a printed copy of your assignment (only the MSWord document) to class on Tuesday, October, 2014. I will be collecting them at the beginning of the class. Remember! Late submissions are not accepted for grading.

3