Congratulations you have just been hired by Katniss Company! Your job is to prepare their monthly performance report. The report is presented to management each month. Report includes the following:

1. A budget versus actual analysis for the contribution margin income statement.

The format of the report is a four column report, with Actual data, Budgeted data, the dollar amount of the variance and the variance %. The Variance percentage is calculated by dividing the budget variance by the budgeted dollar amount.

2. An analysis of Return on Sales, Return on Investment, Capital Turnover and Residual Income. These four performance measurements are calculated for the budgeted and actual data. They also include a budget variance for these items (but they do not calculate a variance % for these items.)

Prepare the performance report for Katniss Company. Make your own conclusions about their budgeted versus actual performance.

3. Calculate the Flexible Budget Variance and Volume Variance for Sales.

Hints

1. When you create your formulas for variances, in order to have the variances show minus signs if they are unfavorable:

For things that you want more of as a business owner, because they increase income , subtract the budgeted number from the actual number.

(Sales, Contribution Margin, Operating Income. This rule also applies to ROS, ROI, Capital Turnover and Residual Income because "more is better" with these performance indicators as well.

For things that you want less of as a business owner,because they decrease net income, subtract the actual number from the budgeted number (Variable Costs and Fixed Costs)

2. Format your Variance %'s , ROS and ROI as a percent (find the % button up on the toolbar and click it while your cursor is in that cell). Also, format the number so it shows one decimal place. You can do that by clicking the icon on the toolbar that has a left hand arrow & zeros on it.

3. Format your Residual income to be a dollar amount. One way to do this is through "format" at the top of the screen, then "cells", then "number", then select "currency".

4. Format your Capital Turnover as a number with two decimal places.

Complete your model, then take Excel Quiz A & B to input your answers. Have your model open on your computer when you take these quizzes. Quiz A will be on the base scenario. Quiz B will alter the input data and you'll have to use your model to calculate the new answers. Warning: Some of you are trying to do these calculations by hand and aren't completing an actual excel model. Quiz B requires substantial calculations that cannot be completed by hand in the alloted time. Quiz A is worth 5 points, Quiz B is worth 25 points. If you actually prepare a working excel model you should be able to be successful with both quizzes. Remember there are links to excel tutorials on the Webcourses home page.

Input Section for the Performance Report:
		Katniss Company
		Actual	Master Budget	<---REMEMBER TO USE AN INPUT SECTION
Sales in Units		1000	1100	IN YOUR MODEL AND REFERENCE IT IN YOUR
Unit Selling Price		$10.00	$11.00	FORMULAS
Unit Variable Cost		$5.00	$6.00
Fixed Costs		$2,000	$3,000
Investment		$22,000	$23,000
Rate of Return		10%	10%
Performance Report:		(place your formulas in the grey boxes)
			Katniss Company
		Actual	Master Budget	Master Budget Variance	Variance %
Sales
Variable Costs
Contribution Margin
Fixed Costs
Operating Income
Return on Sales
Return on Investment
Capital Turnover
Residual Income
Flexible Budeting Analysis:
		Actual Sales		Flexible Budget for Sales		Master Budget for Sales
Total Sales
			Flexible Budget Variance		Sales Volume Variance