Acct 202 Project Flexible Budget

Assignment Details

Upload your completed “Acct 202 Project Flexible Budget” project to Western Online using “Flex Budget_Last Name“ as the file name.

Flexible Budget Performance Report Project 

Client Background

Kelsey’s Frozen Confectionaries buys and distributes single-serve ice cream treats to convenience stores, ballparks, and amusement parks. In this business simulation, you will create 1) a master budget performance report, and 2) a flexible budget performance report for Kelsey’s Frozen Confectionaries. Your performance reports should be developed in such a way that any changes to the original assumptions will correctly ripple through the entire spreadsheet. After developing the performance reports, you will answer questions about the variances and determine whether the variances are consistent with management’s explanation about operational changes that took place during the period.

 

Part 1) DIRECTIONS for Master Budget Performance Report:

1) Use the budget assumptions and Excel formulas to populate the Master Budget column. See hints on the worksheet if you don’t know how to create formulas. 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 correctly formulating the cells. Do NOT TYPE A NUMBER IN ANY CELL IN THE BUDGET!!!

2) Use a formula to calculate the “variance” in cell H7: (Actual – Budget). Use the fill handle to drag the formula to the rest of the cells in the column. Leave as positive or negative numbers, rather than absolute values.

3) Use a formula to calculate the “Variance percentage”. NOTE: The percentage is the variance as a percent of the Master Budget. Drag and drop the fill handle to copy the formula to the rest of the cells in the column.

4) Format cells appropriately using the icons on the home tab of the ribbon. Attention to detail makes a report look more professional. For example, use the clear icon to clear rows where no information should be shown, show percentages as %, use accounting or currency to format dollar amounts, add underlines and double underlines where appropriate, use zero decimal places for dollar amounts since they are all whole numbers on a budget, etc.).

5) Use the “IF” function to label the variances as U (=unfavorable) or F (= favorable). To use the “IF” function, first put your cursor in the cell where you want the answer (for example, J7). Then click on the “Formulas” tab on the ribbon, then “Logical,” then choose IF from the drop-down menu. A dialog box will open. Input your logic test (example: H7>=0), then the label you want if the logic test is true (example: F) and the label you want if the logic test is false (example: U). Then click OK. Copy and paste the formula to the appropriate cells. Repeat the use of IF function for the expense account variances, but be careful since revenues and expenses are opposite. ALSO- The formula you use should mark any variance of “0” as an “F” since a zero variance means that budget expectations have been met. After using the function, check EACH line to make sure each “U” or “F”  is correct. This is the place where students tend to make the most mistakes.

6) 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.

7) Use management by exception to determine which variances to investigate. HINT: In column K, use the “IF” function, along with “Absolute value function” (ABS) to find the variances that are larger…in either direction… than the decision rule located in cell B21. Remember to lock your formula to cell B21 using an absolute reference to the cell ($B$21)

For example, =IF(ABS(I7)>($B$21),”yes”,”no”). After using the function, check each line to see whether your formula worked properly. 

8) Use the Conditional Formatting feature to automatically highlight any variance that says “yes” to investigate. First, select all of the answers in the “investigate?” column with your cursor. Next, select “Conditional formatting” from the home tab on the ribbon. Select “highlight cells rules” from the drop-down choices” then “Text that contains….”. A dialogue box will open. Type in “yes” as the text you want. Leave the default highlight color, or choose a different color from the options listed. Then click OK. Notice how every cell containing “yes” is now highlighted.

9) Center the U/F and Yes/No in the middle of the columns for easier readability using the formatting icons on the Home tab. Also, clear out any rows that should be blank.

10) Now check 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, No.

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

 

Part 2) DIRECTIONS for Flexible Budget Performance Report:

1) Copy and Paste all line items (account names) from the master budget performance report into the Flexible Budget Performance report.

2) Copy and Paste all the figures to the column of the Flexible Budget Performance report.

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

4) Build the Flexible Budget using the ACTUAL SALES VOLUME achieved along with the original budget assumptions. This is similar to building a master budget, except you use actual sales volume. Assume the sales volume achieved is in the same relevant range as the original budget assumptions.

5) Try using the “accounting” or “currency” settings to get dollar signs, commas, and brackets on negative numbers. Add underlines and double underlines as needed. Remove any decimals.

6) Head to the appropriate column for the Volume Variance and then use Excel formulas to populate the cells in that column (e.g., +H33-J33). Once you finish the first cell, copy and paste (or drag) the formula to the other cells.

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

8) Use the following check figures to check your formulas so far:

Shipping Expense: $44,213 $1,293 $42,920 $2,920 $40,000

9) Format cells appropriately. Attention to detail makes a report look more professional.  (For example, dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.).

10) we won’t add the U and F designations to the flexible budget performance report to save time. However, you’ll need to figure out which way they go mentally. The interpretation of the volume variance is a little problematic because it compares two budgets rather than budget vs. actual. HINT: Consider the MASTER BUDGET as the company’s target when determining whether the volume variance should be designated as a U or F.