1. Use the budget assumptions, along with Excel formulas, to populate the Master Budget column. Note:...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
1. 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!!! 2. Use a formula to calculate the "variance" in cell H7: (Actual - Budget). Copy and paste the formula to 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 Master Budget. Copy and paste the formula to the rest of the cells in the column. All variance percentages should show 1 place after the decimal point. 4. Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc.). 5. 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 you'll want them to be opposite of one another. Any variance of "0" should be shown as "F" since it meets budget expectations. Budget assumptions: Volume 20,000 cases Sales Revenue 50.00 per case Percent sold on credit 90% percent of sales Variable Expenses: CGS 30.00 per case Sales Commission 2.50 per case Shipping Expense Bad Debt Expense 2.00 per case 1.00% percent of credit sales Fixed Monthly Expenses: Salaries Lease on Distribution center Depreciation on fleet & Equip $ 12,000 Advertising $ 40,000 2$ 17,000 $ 10,000 $ 11,000 Office rent, phone, internet Management by exception 15% Investigation rule %24 の の 9の Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 MASTER ACTUAL BUDGET VARIANCE VAR. % F or U Volume (in cases) 21,460 Sales Revenue Less Variable Expenses: $ 1,131,020 Cost of Goods Sold 2$ 682,880 Sales Commissions 58,685 Shipping Expense $ 44,213 Bad debt expense Contribution Margin Less Fixed Expenses: 16,930 328,312 Salaries 2$ 43,000 Lease on Distn center 2$ 15,500 Deprec. on fleet/equip $ 12,000 Advertising 7,750 Office rent, phone, net $ Operating Income 12,300 237,762 %24 Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 30 FLEXIBLE MASTER ACTUAL BUDGET BUDGET 1. 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!!! 2. Use a formula to calculate the "variance" in cell H7: (Actual - Budget). Copy and paste the formula to 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 Master Budget. Copy and paste the formula to the rest of the cells in the column. All variance percentages should show 1 place after the decimal point. 4. Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc.). 5. 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 you'll want them to be opposite of one another. Any variance of "0" should be shown as "F" since it meets budget expectations. Budget assumptions: Volume 20,000 cases Sales Revenue 50.00 per case Percent sold on credit 90% percent of sales Variable Expenses: CGS 30.00 per case Sales Commission 2.50 per case Shipping Expense Bad Debt Expense 2.00 per case 1.00% percent of credit sales Fixed Monthly Expenses: Salaries Lease on Distribution center Depreciation on fleet & Equip $ 12,000 Advertising $ 40,000 2$ 17,000 $ 10,000 $ 11,000 Office rent, phone, internet Management by exception 15% Investigation rule %24 の の 9の Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 MASTER ACTUAL BUDGET VARIANCE VAR. % F or U Volume (in cases) 21,460 Sales Revenue Less Variable Expenses: $ 1,131,020 Cost of Goods Sold 2$ 682,880 Sales Commissions 58,685 Shipping Expense $ 44,213 Bad debt expense Contribution Margin Less Fixed Expenses: 16,930 328,312 Salaries 2$ 43,000 Lease on Distn center 2$ 15,500 Deprec. on fleet/equip $ 12,000 Advertising 7,750 Office rent, phone, net $ Operating Income 12,300 237,762 %24 Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 30 FLEXIBLE MASTER ACTUAL BUDGET BUDGET
Expert Answer:
Answer rating: 100% (QA)
Master Budget Performance report for the month ended June 30 Actual Master Budget Master Budget Work... View the full answer
Related Book For
Advanced Financial Accounting
ISBN: 978-0078025624
10th edition
Authors: Theodore E. Christensen, David M. Cottrell, Richard E. Baker
Posted Date:
Students also viewed these accounting questions
-
The trial balance of Clayton Cleaners for the month ended September 30 is as follows: Data for the adjustments are as follows: a. Expired or used- up insurance, $ 800. b. Depreciation expense on...
-
The diffraction of radiation can be used to determine the spacing between atoms. In practice, the wavelength of the radiation used should be restricted to a value which is of the same order of...
-
Financial information related to Westwood Company for the month ended June 30, 2012, is as follows: Net income for June ........ $230,000 Dividends during June ........ 45,000 Retained earnings, June...
-
The North American and European continents are moving apart at a rate of about 3 cm/y. At this rate how long will it take them to drift 500 km farther apart than they are at present?
-
Researchers have found that people are less willing to express "traditionally" prejudiced beliefs than in the past, but their behavior does not agree with espoused beliefs. How can such disparities...
-
Predict how the Fed would likely respond if the output gap became more positive, so that output moved from being 0.1% above potential output to being 3% above potential output, and inflation rose...
-
Six test scores are shown below. The first 4 test scores are 15% of the final grade, and the last two test scores are 20% of the final grade. Find the weighted mean of the test scores. 80 70 84 93 89...
-
On July 1, 2006, Sweetman Company signed a contract to lease space in a building for 15 years. The lease contract calls for annual (prepaid) rental payments of $70,000 on each July 1 throughout the...
-
INFORMATION As a management accountant, you have recently been recruited to be part of the strategic management accounting team of GHK Accountants. GHK Accountants has been contracted to help...
-
1. Identify where (in its value system) and how IKEA have achieved cost leadership. 2. Identify how IKEA have achieved differentiation from their competitors. 3. Explain how IKEA tries to ensure that...
-
What are influence costs? Why do firms exist? Define decision management and decision control, and give an example of each.
-
A useful device in thinking about any implementation programme is the change curve which represents a journey on which employees need to be taken if a change is to lead to actions and be successful....
-
Use the following information to answer question 3 and 4 Suppose that the current spot exchange rate is 0.80/$ and the bank quoted forward exchange rate is 0.7813/$. The one-year interest rate is...
-
You are given the following information about stock X, stock Y, and the market: The required return and volatility for the market portfolio are 0.08 and 0.25, respectively. The required return and...
-
With the competition for attention online at an all-time high, the struggle to keep followers engaged with your company's social channels is real and ongoing. In addition to the smart use of visuals...
-
Sevcan Bakery decided to buy a new oven for $12,000. Using this new technology oven, the owner expects to make extra $3,000 for the first year, $4,000 for the second year and $5,000 for the third and...
-
Assume an investment has cash flows of -$19,850, $10,875, $9,250, and $6250 for Years 0 to 3, respectively. What is the NPV if the required return is 12.9 percent? Should the project be accepted or...
-
1. True or False. Pitfalls to consider in a statistical test include nonrandom samples, small sample size, and lack of causal links. 2. Because 25 percent of the students in my morning statistics...
-
Accounting for Prepayments and Capital Assets Required Prepare journal entries for Iron Citys general fund for the following, including any adjusting and closing entries on December 31, 20X1: a....
-
Byte Computer Corporation acquired 75 percent of Nofail Software Companys stock on January 2, 20X3, by issuing bonds with a par value of $50,000 and a fair value of $67,500 in exchange for the...
-
Block Corporation was created on January 1, 20X0, to develop computer software. On January 1, 20X5, Foster Company acquired 90 percent of Block's common stock at its underlying book value. At that...
-
a. In Experiment 4A, set the sample frequency to 50 msec and measure the phase margin. b. Compare this with the phase margin with a 1-msec sample time (same as Question 1c). (DATA FROM QUETION 1C) c....
-
a. Using the DSA in Experiment 4A to generate open-loop Bode plots, what is the phase margin when the sample time is set to its default (0.00025 sec)? b. Repeat with a sample time of 0.0005 sec. c....
-
Using Experiment 3A, follow the zone-based tuning procedure of Section 3.5 using a square wave command with the following criteria: For KP, allow no overshoot. For KI, allow about 10% overshoot. a....
Study smarter with the SolutionInn App