Flexible Budget Performance Report Project Kelseys Frozen Confectionaries buys and distributes single serve ice cream treats, popsicles, etc , such as those sold at convenience stores, sporting events and amusement parks Download the file containing Kelseys budget assumptions and actual figures for June The file also contains a draft for both a 1) master budget performance report, and 2) a flexible budget performance report DIRECTIONS for Master Budget Performance Report Use the budget assumptions, along with Excel formulas, to populate the Master Budget column Note Your formulas must work such that if ANY of the budget assumptions change, the new assumptions ripple through the entire budget Part of your grade will be based on whether you correctly formulated the cells Do NOT TYPE A NUMBER IN ANY CELL Use a formula to calculate the variance in cell H7 (Actual Budget) Leave as positive or negative, rather than absolute values Use a formula to calculate the Variance percentage NOTE The percentage is the variance as a percent of the Master Budget Round to 1 decimal place Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc ,) Use the If statement function to show the variances as U or F The If statement can be found under Formulas, Logical Example IF(H7 0, F , U ) This formula means If cell H7 0 or H7 0, then mark as F If not greater than or equal to 0, mark as U Be careful with revenues and expense variances since youll want them to be opposite of one another Any variance of 0 should be shown as F since it meets budget expectations Check your answers using the following check figures Budgeted CM $301,000 Budgeted Op Inc $211,000 Variance for Commissions $8,685 Variance percentage for Commissions 17 4 , Unfavorable Answer the following questions What is the master budget variance for Shipping exp $ , , U or F What does it mean What is the master budget variance for Lease Exp $ , , U or F What does it mean Use management by exception to determine which variances to investigate HINT In column K, use the If function, along with Absolute value function to find the variances that are larger than the decision rule If it meets the criteria put Yes in the column, if not put No Now check to see if everything ripples through the budget if you change an assumption Try changing the assumed sales volume to 20,500 and Shipping expense to $2 10 per case You should have a new total variance for operating income of $21,287, 9 8 variance, F Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2 00 DIRECTIONS for Flexible Budget Performance Report Fill in all the information you can from the Master Performance Budget Report Do NOT TYPE A NUMBER OR ACCOUNT NAMES IN ANY CELL Formulate the Master Budget figures by referencing (e g , B7) the appropriate cells in the first performance report By doing so, if any assumptions change, they will ripple through BOTH performance reports Build the Flexible Budget by using the ACTUAL SALES VOLUME achieved along with the original budget assumptions This is very similar to building a master budget, except you are using actual sales volume Assume the actual sales volume achieved is in the same relevant range as the original budget assumptions Try using the accounting or currency settings to get dollar signs, commas, and brackets on negative numbers Add underlines and double underlines as needed Head the appropriate column for the Volume Variance and then use Excel formulas to populate the cells in that column (e g , H33 J33) Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that column (e g F33 H33) Format the report appropriately (Dollar signs, underscores, double underscores, etc ,) Use the following check figures to check your formulas so far Shipping Expense $44,213 $1293 $42,920 $2,920 $40,000 Lease on Distn center $15,500 $ (1,500) $17,000 $ $17,000 To save time, we wont add the U and F designations to the flexible budget performance report However, youll need to mentally figure out which way they go The interpretation of the volume variance is a little problematic because it compares two budgets, rather than budget vs actual HINT Think of the MASTER BUDGET as the companys target when determining whether the volume variance should be designated as a U or F Answer the following questions What is the Flex budget variance for operating income $ F or U What is the Volume variance for operating income $ F or U Does the combination of the flex budget variance and volume variance sum to the master budget variance for operating income Y or N Should it always sum to the master budget variance Y or N How much of the master budget variance for Sales revenue was caused by an unanticipated increase in volume F or U How much of the master budget variance for Commissions Exp was caused by an unanticipated increase in volume F or U How much of the master budget variance for Advertising Expenses was caused by an unanticipated increase in volume F or U How much of the master budget variance for CGS was caused by some factor other than volume (Hint this is NOT a manufacturing business) $ F or U What could account for this variance How much of the master budget variance for Sales Revenue was caused by some factor other than volume $ F or U What could account for this variance How much of the master budget variance for Advertising Exp was caused by some factor other than volume $ F or U What could account for this variance Auditors always look at the variances between actual and budget or actual and last year Say you were auditing this client and they gave you the following story (below) Is the story consistent with the overall pattern of variances Which elements of the story are consistent with the explanations Which variances are inconsistent, or left unexplained Be sure to analyze every line item on the budget (SEE NEXT PAGE ) Client Story In order to motivate our sales force to increase sales, we decided to increase our commissions and salaries and increase marketing At the same time, our supplier increased its prices, and we felt we could pass that cost increase on to our customers in the form of price increase However, with the additional pressure to make sales, coupled with the increased sales price, we had to loosen credit terms on sales We also had to lease a little more distribution space and acquire another truck to handle the volume increase Our shipping expense relates to gasoline on deliveries Luckily, gas prices went down from what we originally expected this year Budget assumptions Kelsey's Frozen Confectionaries laster Budget Performance Repo For the month ended June 30 Volume Sales Revenue 20,000 cases $ 50 00 per case MASTER BUDGET VARIANCE VAR Z For U Investigate Percent sold on credit 90 percent of sales ACTUAL 21,460 Volume (in cases) Variable Expenses Cost of Goods Sold Sales Commission Shipping Expense Bad Debt Expense $ $ $ 30 00 per case 2 50 per case 2 00 per case 1 00 percent of credit sales Fixed Monthly Expenses Salaries $ 40,000 Lease on Distribution center $ 17,000 Depreciation on fleet Equip $ 12,000 Advertising $ 10,000 Office rent, phone, internet $ 11,000 Sales Revenue $ 1,131,020 Less Variable Expenses Cost of Goods Sold $ 682,880 Sales Commissions $ 58,685 Shipping Expense $ 44,213 Bad debt expense $ 16,930 Contribution Margin $ 328,312 Less Fixed Expenses Salaries 43,000 Lease on Distribution cente $ 15,500 Depreciation on fleetlequip $ 12,000 Advertising $ 7,750 Office rent, phone, net 12,300 Operating Income $ 237,762 Management by exception 15 Investigation rule HINTS on EXCEL 1) Always check the logic of your results after entering formulas to make sure the spreadsheet is doing what you really want it to do Kelsey's Frozen Confectionaries exible Budget Performance Repo For the month ended June 30 2 Make sure to check if you make any changes to the budget assumptions, the numbers flow through correctly 3 Make sure to use spell check No hardcoded numbers 4 Make sure the reports print out correctly FLEXIBL E MAST ER ACTUAL

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 19, 2024

Flexible Budget Performance Report Project Kelseys Frozen Confectionaries buys and distributes single-serve ice cream treats, popsicles, etc., such as those sold at convenience stores, sporting

Flexible Budget Performance Report Project

Kelseys Frozen Confectionaries buys and distributes single-serve ice cream treats, popsicles, etc., such as those sold at convenience stores, sporting events and amusement parks. Download the file containing Kelseys budget assumptions and actual figures for June. The file also contains a draft for both a 1) master budget performance report, and 2) a flexible budget performance report.

DIRECTIONS for Master Budget Performance Report:

Use the budget assumptions, along with Excel formulas, to populate the Master Budget column. Note: Your formulas must work such that if ANY of the budget assumptions change, the new assumptions ripple through the entire budget. Part of your grade will be based on whether you correctly formulated the cells. Do NOT TYPE A NUMBER IN ANY CELL!!!

Use a formula to calculate the variance in cell H7: (Actual Budget). Leave as positive or negative, rather than absolute values.

Use a formula to calculate the Variance percentage. NOTE: The percentage is the variance as a percent of the Master Budget. Round to 1 decimal place.

Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc.,).

Use the If statement function to show the variances as U or F. The If statement can be found under Formulas, Logical. Example: =IF(H7>=0,"F","U"). This formula means If cell H7>0 or H7=0, then mark as F. If not greater than or equal to 0, mark as U. Be careful with revenues and expense variances since youll want them to be opposite of one another. Any variance of 0 should be shown as F since it meets budget expectations.

Check your answers using the following check figures: Budgeted CM= $301,000; Budgeted Op Inc. = $211,000; Variance for Commissions = $8,685; Variance percentage for Commissions = 17.4 %, Unfavorable.

Answer the following questions:

What is the master budget variance for Shipping exp.? $_____________, ___%, U or F? What does it mean?

What is the master budget variance for Lease Exp? $______________, _____%, U or F ?What does it mean?

Use management by exception to determine which variances to investigate. HINT: In column K, use the If function, along with Absolute value function to find the variances that are larger than the decision rule. If it meets the criteria put Yes in the column, if not put No.

Now check to see if everything ripples through the budget if you change an assumption. Try changing the assumed sales volume to 20,500 and Shipping expense to $2.10 per case. You should have a new total variance for operating income of $21,287, 9.8% variance, F.

Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2.00.

DIRECTIONS for Flexible Budget Performance Report:

Fill in all the information you can from the Master Performance Budget Report. Do NOT TYPE A NUMBER OR ACCOUNT NAMES IN ANY CELL!!!

Formulate the Master Budget figures by referencing (e.g.,=B7) the appropriate cells in the first performance report. By doing so, if any assumptions change, they will ripple through BOTH performance reports.

Build the Flexible Budget by using the ACTUAL SALES VOLUME achieved along with the original budget assumptions. This is very similar to building a master budget, except you are using actual sales volume. Assume the actual sales volume achieved is in the same relevant range as the original budget assumptions. Try using the accounting or currency settings to get dollar signs, commas, and brackets on negative numbers. Add underlines and double underlines as needed.

Head the appropriate column for the Volume Variance and then use Excel formulas to populate the cells in that column (e.g., +H33-J33).

Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that column (e.g. +F33-H33).

Format the report appropriately (Dollar signs, underscores, double underscores, etc.,) Use the following check figures to check your formulas so far:

Shipping Expense

$44,213

$1293

$42,920

$2,920

$40,000

Lease on Distn center

$15,500

$ (1,500)

$17,000

$ -

$17,000

To save time, we wont add the U and F designations to the flexible budget performance report. However, youll need to mentally figure out which way they go. The interpretation of the volume variance is a little problematic because it compares two budgets, rather than budget vs. actual. HINT: Think of the MASTER BUDGET as the companys target when determining whether the volume variance should be designated as a U or F.

Answer the following questions:

What is the Flex budget variance for operating income? $_____________ F or U?

What is the Volume variance for operating income? $____________F or U?

Does the combination of the flex budget variance and volume variance sum to the master budget variance for operating income? Y or N? Should it always sum to the master budget variance? Y or N?

How much of the master budget variance for Sales revenue was caused by an unanticipated increase in volume?__________________ F or U?

How much of the master budget variance for Commissions Exp. was caused by an unanticipated increase in volume?__________________ F or U?

How much of the master budget variance for Advertising Expenses was caused by an unanticipated increase in volume?__________________ F or U?

How much of the master budget variance for CGS was caused by some factor other than volume? (Hint: this is NOT a manufacturing business). $__________ F or U? What could account for this variance?

How much of the master budget variance for Sales Revenue was caused by some factor other than volume? $__________ F or U? What could account for this variance?

How much of the master budget variance for Advertising Exp. was caused by some factor other than volume? $__________ F or U? What could account for this variance?

Auditors always look at the variances between actual and budget or actual and last year. Say you were auditing this client and they gave you the following story (below). Is the story consistent with the overall pattern of variances? Which elements of the story are consistent with the explanations? Which variances are inconsistent, or left unexplained? Be sure to analyze every line item on the budget. (SEE NEXT PAGE.)

Client Story: In order to motivate our sales force to increase sales, we decided to increase our commissions and salaries and increase marketing. At the same time, our supplier increased its prices, and we felt we could pass that cost increase on to our customers in the form of price increase. However, with the additional pressure to make sales, coupled with the increased sales price, we had to loosen credit terms on sales. We also had to lease a little more distribution space and acquire another truck to handle the volume increase. Our shipping expense relates to gasoline on deliveries. Luckily, gas prices went down from what we originally expected this year.

Budget assumptions: Kelsey's Frozen Confectionaries laster Budget Performance Repo For the month ended June 30 Volume Sales Revenue 20,000 cases $ 50.00 per case MASTER BUDGET VARIANCE VAR. Z. For U Investigate? Percent sold on credit 90% percent of sales ACTUAL 21,460 Volume (in cases) Variable Expenses: Cost of Goods Sold Sales Commission Shipping Expense Bad Debt Expense $ $ $ 30.00 per case 2.50 per case 2.00 per case 1.00% percent of credit sales Fixed Monthly Expenses: Salaries $ 40,000 Lease on Distribution center $ 17,000 Depreciation on fleet & Equip $ 12,000 Advertising $ 10,000 Office rent, phone, internet $ 11,000 Sales Revenue $ 1,131,020 Less Variable Expenses: Cost of Goods Sold $ 682,880 Sales Commissions $ 58,685 Shipping Expense $ 44,213 Bad debt expense $ 16,930 Contribution Margin $ 328,312 Less Fixed Expenses: Salaries 43,000 Lease on Distribution cente $ 15,500 Depreciation on fleetlequip $ 12,000 Advertising $ 7,750 Office rent, phone, net 12,300 Operating Income $ 237,762 Management by exception 15%. Investigation rule HINTS on EXCEL: 1). Always check the logic of your results after entering formulas to make sure the spreadsheet is doing what you really want it to do. Kelsey's Frozen Confectionaries exible Budget Performance Repo For the month ended June 30 2. Make sure to check if you make any changes to the budget assumptions, the numbers flow through correctly. 3. Make sure to use spell check. No hardcoded numbers. 4. Make sure the reports print out correctly. FLEXIBL E MAST ER ACTUAL