Collin County Community College District Flexible Budget Data Worksheet The requirement are attached below! Let me know if you have any question! …… Fl

Collin County Community College District Flexible Budget Data Worksheet The requirement are attached below! Let me know if you have any question! …… Flexible Budget Performance Report Project
You and your partner will each work on this project on your own laptops, using each other for resources while completing
In the end, you will Turn in ONE project per team. If you or your partner feels you did not share equally in the work, emai
possible grade adjustment. Otherwise, you will both receive the same grade. Upload your completed project to Canvas u
Budget_Last Name ID#_Last name ID# “ as the file name.
Kelsey’s Frozen Confectionaries buys and distributes single-serve ice cream treats to convenience stores, ballparks, and am
In this project, you will create 1) a master budget performance report, and 2) a flexible budget performance report for Ke
Confectionaries. Your performance reports should be developed in such a way that any changes to the original assumptio
ripple through the entire spreadsheet. After developing the performance reports, you will answer questions about the va
determine whether the variances are consistent with management’s explanation about operational changes that took plac
period.
Part 1) DIRECTIONS for Master Budget Performance Report:
1) Use the budget assumptions, along with Excel formulas, to populate the Master Budget column. Note: Your formulas
that if ANY of the budget assumptions change, the new assumptions ripple through the entire budget. Part of your grade
whether you correctly formulate the cells. Do NOT TYPE A NUMBER IN ANY CELL!!!
2) Use a formula to calculate the “variance” in cell H7: (Actual – Budget). Copy and paste (or use the fill handle to drag)
the rest of the cells in the column. Leave as positive or negative, rather than absolute values.
3) Use a formula to calculate the “Variance percentage”. NOTE: The percentage is the variance as a percent of the Mast
and paste (or drag) the formula to the rest of the cells in the column
4) Format cells appropriately. Attention to detail makes a report look more professional. (For example, percentages sho
signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places
amounts, etc.,).
5) Use the “If” statement function to show the variances as U or F. The “If” statement can be found under “Formulas, Log
=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 a
with revenues and expense variances since they should be opposite of one another. ALSO- The formula you use should m
variance of “0” as an “F” since a zero variance means that budget expectations have been met. After using the functio
to make sure it is going in the direction you believe it should go.
6) Check your answers using the following check figures: Budgeted CM= $301,000; Budgeted Op Inc. = $211,000; Varianc
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”, a
“Absolute value function” to find the variances that are larger than the decision rule. Also, since the decision rule is in
Excel to always compare to B21, you need to make it an absolute reference: $B$21.
For example, =IF(ABS(I7)>($B$21),”yes”,”no”). After using the function, check each line to see whether your formula w
8) Use the Conditional Formatting feature to automatically highlight any variance that says “yes” to investigate. First, sel
answers in the “investigate?” column with your cursor. Next select “Conditional formatting” from the home tab on the
drop-down choices select “highlight cells rules” then “Text that contains….”. A dialogue box will open. Type in ”
want. Leave the default highlight color, or choose a different color from the options listed. Then click OK. Notice how ev
drop-down choices select “highlight cells rules” then “Text that contains….”. A dialogue box will open. Type in ”
want. Leave the default highlight color, or choose a different color from the options listed. Then click OK. Notice how ev
containing “yes” is now highlighted!
9) Center the U/F and Yes/No in the middle of the columns for easier readability.
10) Now check to see if everything ripples through the budget if you change an assumption. Try changing the assumed s
20,500 and Shipping expense to $2.10 per case. You should have a new total variance for operating income of $21,287, 9
No.
11) Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2.00
Part 2) DIRECTIONS for Flexible Budget Performance Report:
1) Copy and Paste all of the line items (account names) from the master budget performance report into the Flexible Budg
report.
2) Copy and Paste all of the actual figures to the actual 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
so, if any assumptions change, they will ripple through BOTH performance reports.
4) Build the Flexible Budget by using the ACTUAL SALES VOLUME achieved along with the original budget assumptions. Thi
to building a master budget, except you are using actual sales volume. Assume the actual sales volume achieved is in the s
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 u
double underlines as needed. Remove any decimals.
6) Head the appropriate column for the Volume Variance and then use Excel formulas to populate the cells in that column
J33). Once you get the first cell done, copy and paste (or drag) the formula to the other cells.
7) Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that
+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
or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.,).
10) To save time, we won’t add the U and F designations to the flexible budget performance report. However, you’ll need
figure out which way they go. The interpretation of the volume variance is a little problematic because it compares two bu
than budget vs. actual. HINT: Think of the MASTER BUDGET as the company’s target when determining whether the volu
should be designated as a U or F.
11) Complete worksheet: Analysis- page 1.
12) Complete worksheet: Analysis- page 2.
13) Check report against grading rubric before submitting your project on Canvas.
13) Check report against grading rubric before submitting your project on Canvas.
esources while completing the assignment.
e equally in the work, email me for a
mpleted project to Canvas using “Flex
ce stores, ballparks, and amusement parks.
performance report for Kelsey’s Frozen
es to the original assumptions will correctly
wer questions about the variances and
onal changes that took place during the
umn. Note: Your formulas must work such
budget. Part of your grade will be based on
use the fill handle to drag) the formula to
ce as a percent of the Master Budget. Copy
r example, percentages shown as %, dollar
priate, zero decimal places for dollar
ound under “Formulas, Logical”. Example:
r than or equal to 0, mark as “U”. Be careful
he formula you use should mark any
using the function, check each line
Op Inc. = $211,000; Variance for
n K, use the “If function”, along with
ce the decision rule is in B21 and you want
e whether your formula worked properly.
es” to investigate. First, select all of the
from the home tab on the ribbon. From the
will open. Type in “yes” as the text you
en click OK. Notice how every cell
will open. Type in “yes” as the text you
en click OK. Notice how every cell
Try changing the assumed sales volume to
rating income of $21,287, 9.8% variance, F,
ion back to $2.00 before continuing.
port into the Flexible Budget Performance
nce report.
et performance report (e.g.=B7). By doing
al budget assumptions. This is very similar
volume achieved is in the same relevant
n negative numbers. Add underlines and
ate the cells in that column (e.g., +H33-
o populate the cells in that column (e.g.
example, dollar signs using the accounting
or dollar amounts, etc.,).
port. However, you’ll need to mentally
because it compares two budgets, rather
termining whether the volume variance
Budget assumptions:
Volume
Sales Revenue
$
Percent sold on credit
Variable Expenses:
CGS
Sales Commission
Shipping Expense
Bad Debt Expense
Fixed Monthly Expenses:
Salaries
Lease on Distribution center
Depreciation on fleet & Equip
Advertising
Office rent, phone, internet
Management by exception
20,000 cases
50.00 per case
90% percent of sales
$
$
$
$
$
$
$
$
30.00
2.50
2.00
1.00%
per case
per case
per case
percent of credit sales
40,000
17,000
12,000
10,000
11,000
15% Investigation rule
HINTS on EXCEL:
1) If you get a series of hashmarks (######) it just means your column
width is not wide enough to accomodate the number. Increase column
width and/or delete extra decimal places.
2. To add numbers, put your cursor on the cell where you want the
answer to be. Then keystroke the addition sign (+) and then click on
the cell you want add, then keystroke the addition sign again (+) and
click on the second cell you want to add. Continue in the same fashion
for multiple cells. Use the subtraction sign (-) for subtracting cells. If
you have an array of cells to add, you can use the AutoSum button.
3. To multiply, use the * symbol. To divide, use the / symbol.
4. Make use of copy/paste commands (or the fill handle) when you
want similar forumulas in different cells.
5. 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
Master Budget Performance Report
For the month ended June 30
ACTUAL
21,460
Volume (in cases)
Sales Revenue
Less Variable Expenses:
Cost of Goods Sold
Sales Commissions
Shipping Expense
Bad debt expense
Contribution Margin
Less Fixed Expenses:
Salaries
Lease on Distn center
Deprec. on fleet/equip
Advertising
Office rent, phone, net
Operating Income
$
1,131,020
$
$
$
$
$
682,880
58,685
44,213
16,930
328,312
$
$
$
$
$
$
43,000
15,500
12,000
7,750
12,300
237,762
MASTER
BUDGET
VARIANCE
Kelsey’s Frozen Confectionaries
Fexible Budget Performance Report
For the month ended June 30
ACTUAL
FLEXIBLE
BUDGET
VAR. %
F or U
MASTER BUDGET
Investigate?
NOTE: The most common mistake
in this project involves the F/U
formulas (Step 5 of the directions).
Remember, revenues and
expenses go in opposite directions,
so your formula needs to be
adjusted for that fact. Also, be sure
any variance of $0 would be would
end up being shown as a favorable
variance. . Finally, think about
which direction CM and Operating
income should go: the same
direction as revenue, or the same
direction as expenses?
Answer the following questions (dollar amount; F or U?; Reason, when asked)
What is the flex budget variance for operating income?
What is the volume variance for operating income?
What do the flex budget variance and volume variance for operating income sum to?
Does this equal the total master budget variance Yes or No?
Should it always sum (or net) to the total master budget variance, even if one is U and one is F? Yes or No?
How much of the master budget variance for Sales was caused by an UNANTICIPATED increase in volume?
How much of the master budget variance for Commission Expense was caused by an UNANTICIPATED increase in volume?
How much of the master budget variance for Advertising Expense was caused by an UNANTICIPATED increase in volume?
How much of the master budget variance for CGS was caused by some factor OTHER THAN volume?
What could account for this variance?
How much of the master budget variance for Sales Revenue was caused by some factor OTHER THAN volume?
What could account for this variance?
How much of the master budget variance for Advertising Expense was caused by some factor OTHER THAN volume?
What could account for this variance?
Dollar amount
increase in volume?
increase in volume?
THAN volume?
F or U
Yes or No
Your answers to each
question should be
shown in the shaded
cells.
Possible reason
Managers and auditors always analyze the variances between actual and budget. Say you were auditing this clie
following story (below). Is the story consistent with the overall pattern of variances? Which elements of the sto
explanations? Which variances are inconsistent, or left unexplained? Be sure to analyze every line item on the
Client Story: In order to motivate our sales force to increase sales, we decided to increase our commissions an
marketing. At the same time, our supplier increased its prices, and we felt we could pass that cost increase on
of price increase. However, with the additional pressure to make sales, coupled with the increased sales price
on sales (extend more credit to more customers). We also had to lease a little more distribution space and ac
the volume increase. Our shipping expense relates to gasoline used for deliveries. Luckily, gas prices went do
expected this year.
In the table below, classify each account on the budget according to whether the variances in the performance report ar
unexplained by the client’s story. Place an “X” in the appropriate column.
Income Statement line item
All sales volume variances
Flexible budget variances:
Sales Revenue
Cost of Goods sold
Commission expense
Shipping Expenses
Bad debt expense
Salaries expense
Lease of distribution center
Depreciation of fleet & Equip.
Advertising expense
Ofice rent, phone, internet
Consistent
Inconsistent
Unexplained
were auditing this client and they gave you the
ch elements of the story are consistent with the
very line item on the budget.
e our commissions and salaries and increase
s that cost increase on to our customers in the form
increased sales price, we had to loosen credit terms
ribution space and acquire another truck to handle
y, gas prices went down from what we originally
e performance report are consistent, inconsistent, or left
Grading rubric
Master budget performance Report
Correct numbers using only formulas
Correct percentages using only formulas
Correct U/F using only formulas
Correct Yes/No using only formulas
Correct conditional formatting automatically highlights all “yes” answers
Clean formatting ($, %, underlines/double underlines only where appropriate, no
decimals, etc)
Points
5
5
5
5
2
5
Flexible budget performance report
Correct numbers using only formulas
Correct titles
Clean formatting
10
4
5
Analysis- page 1
23
Analysis- page 2
Total Points
11
80

Purchase answer to see full
attachment