Sensitivity Analysis Worksheet

Yummi-Lik
Sensitivity Analysis Worksheet

To begin:
Retrieve the Excel file your group created for problem 6-40 and save it to the hard drive. Open it in Excel, then go to Windows button>Save As. Save another copy of the file with a different filename (such as 6-40w). Re-open the original file, then go to the View tab; in the Window block, select Arrange All>tiled. You now should have two copies of the spreadsheet open: the original and a 'working' version. You will make changes to the working version only; having the original open will help you see the effects of your changes.

Instructions: Answer the following questions. Consider each scenario independently; 'undo' your changes before proceeding to the next question. You may want to close the working version without saving and then reopen it if you make multiple changes.

Question 1
Which schedules and financial statements will be affected if you change the sales quantity data? The sales price data?

Question 2
What is the current percentage sales mix in units? What will happen to budgeted net income if the percentages for the two products are switched? What will drive this change?

Question 3
Is inventory of finished goods for each product increasing or decreasing over the period? What is the resulting relation between sales and production for each product (i.e., which one is greater)?

Suppose the company anticipates a drop in demand in July as a result of an ad campaign about sugar and tooth decay aired by the American Association of Pediatric Dentistry during June. In response, Yummi-Lik decides to reduce inventories of both products; now the target ending inventory of finished goods units for June is to be 50% of ending inventory for May. What will be the resulting relationship between sales and production? Which schedules and financial statements will be affected? Make the change and verify your expectations.

Relationship between sales and production:
Affected Schedule Prediction Result
Question 4
Examine the budgeted inputs for the two products. The case states that Yummi-Lik uses activity-based costing for allocating overhead to production. In your opinion, is that an accurate statement? Is Yummi-Lik a good candidate for an activity-based costing system? Explain.

Question 5
Suppose Giant units were made in a separate, more technologically advanced machine. The machine requires (relative to the machine in which Large units are made) fewer hours of direct labor per unit by a more senior, skilled worker but must run smaller batch sizes. Identify the input variables that would change and in which direction.

Which schedules and financial statements would be affected? Indicate your new parameter values, make the appropriate changes, and verify your expectations.

New parameter values:
Affected Schedule Prediction Result