You will examine the variances for the budget you completed in Module 3. Working off the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
You will examine the variances for the budget you completed in Module 3. Working off the budget you prepared for Netflix for 20XX, you will evaluate the variance for the 'actual' activity that occurred each month. You will use the attached worksheet to provide the dollar and percentage difference of budget vs. actual. Remember, your budget total should have been $480,270. However, the actual budget came in at $449,816. The following are the assumptions for the Budget from Module 3. Wages • There are 5 members in the department: One Director that gets paid $130,000/year. One Supervisor that gets paid $95,000/year. Three Junior Staff members that get paid $65,000/year each. Miscellaneous • The company always allocates $50 per month for miscellaneous expenses from January - October and then $40 per month for November - December. Travel • There are three conferences in 20XX and you will be sending 2 employees to each conference. • The first two conferences cost $3,000 per person and the third conference is $5,000 per person. • The conferences will be held in February, May, and September Supplies • Each person in the department spends $24 per month each for supplies Training • The entire department will be attending a training seminar in April and the cost is $5,000 per person. Meals During the training session for the entire department, each person will receive $250 per day for meals and the training session is for three days. Meals Meals will be paid for travel to the conferences mentioned above. Each person will receive $250 per day and each conference is five days. TASK: Perform a variance review of the yearly budget created for Netflix. You will compare the budgeted dollar amounts with the actual dollars spent throughout the year. Analyze the months and evaluate why each month or category was underbudget or overbudget. Utilize the Excel spreadsheet to complete your evaluation/review of the budget. January has already been done for you. You must fill out the variances for EVERY month. $ Variance Analysis Calculation: % Variance Analysis Calculation: Actual minus Budget $$ Variance / Budget Variance Analysis January February March April Budget Actual $$ Var % Var Budget Actual $$ Var % Var Budget Actual $$ Var % Var Budget Actual $$ Var % Var Wages 35,000 35,000 $ 0% 35,000 35,000 35,000 35,000 35,000 29,583 Travel 0 0 $ 6000 0 6000 0 0 Meals 0 0 $ 2500 0 1500 Training 0 0 0 0 3750 25000 0 20000 Supplies 120 100 Miscellaneous 50 OS $ 35,170 $ 35,100 $ (20.00) (50.00) (70) -17% -100% 120 50 120 100 100 50 120 50 0 0 -0.2% $ 43,670 $ 35,100 $ 0.0% $ 35,170 $ 42,600 $ 0.0% $ 63,920 $ 49,583 $ Meals Training Supplies Miscellaneous May Budget Actual $$ Var Wages 35,000 29,583 Travel 6000 0 2500 0 0 0 120 50 50 50 % Var $ 43,670 $ 29,683 $ 0.0% $ 35,170 $ 35,100 Wages Travel Budget Actual 35,000 35,000 0 0 0 0 0 0 120 50 500 0 0.0% $ 35,170 $ 35,500 $ September November Budget Actual $ Var % Var Budget Actual Budget Actual $ Var % Var 35,000 35,000 35,000 35,000 35,000 35,000 10,000 10000 0 0 0 0 Meals 2500 2000 0 0 0 0 Training 0 0 0 0 0 0 Supplies 120 120 0 120 0 Miscellaneous 50 50 50 40 0 $ 47,670 $ 47,050 $ 0.0% $ 35,170 $ 35,000 $ 0.0% $ 35,160 $ 35,000 $ June Budget Actual $$ Var % Var 35,000 35,000 0 0 0 0 0 0 120 50 100 0 July $ Var % Var August Budget Actual $ Var % Var 35,000 35,000 0 0 0 0 0 0 120 0 50 0 0.0% $ 35,170 $ 35,000 $ October $ Var % Var December Budget 35,000 Actual $ Var % Var 35,000 0 0 0 0 0 0 120 50 40 50 0.0% $ 35,160 $ 35,100 $ Total - Budget Total - Actual $ Var % Var Wages $ 420,000 $ 409,166 Travel S 22,000 $ 16,000 Meals S 11,250 S 3,500 Training S 25,000 $ 20,000 Supplies S 1,440 $ 900 Miscellaneous $ 580 $ 250 $ 480,270 $ 449,816 $ 0.0% 0.0% 0.0% 0.0% Note: Microsoft Excel shows the #DIV/0! error when a number is divided by zero (0). It happens when you enter a simple formula like =5/0, or when a formula refers to a cell that has 0 or is blank. You can remove all #DIV/0! errors and leave the cell blank. This indicates that no variance has occured. Name of Department: Budget 20XX Budget Allocated to Department: Cybersecurity $ 480,270 Netflix Yearly Budget Budget Preparer: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Wages 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 $ 420,000 Travel 6,000 6,000 10,000 $ 22,000 Meals 2,500 3,750 2,500 2,500 $ 11,250 Training 25,000 $ 25,000 Supplies Miscellaneous 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 40 120 $ 1,440 $ 35,170 $ 43,670 $ 35,170 $ 63,920 $ 43,670 $ 35,170 $ 35,170 $ 35,170 $ 47,670 $ 35,170 $ 35,160 $ 40 $ 35,160 $ 580 480,270 5 Assumptions Wages Each department has 1 Director, 1 Supervisor, and 3 junior people Travel There are 3 conferences in 20XX and you want to send 2 people. The cost for each conference the first 2 conferences is $3,000 per person and the final conference is $5,000 per person. The conferences will be held in February, May, and September Meals Meals will be paid for the travel to the 3 conferences and each person will receive $250 per day and the conference is 5 days. Training The entire department will be attending a training seminar in April and the cost per employee is $5,000. Meals In addition to the training, meals will have to be paid for the training seminar in April. Each person will receive $250 per day and the training is 3 days. Supplies Each person in the department spends $24/month in supplies Misc. The company always allocates $50 per month for miscellaneous expenses from Jan - Oct and then $40 per month in Nov - Dec : Salaries Monthly Expense Director $ 130,000 $ 130,000 Supervisor $ 95,000 $ 95,000 Junior $ 65,000 ea. $ 195,000 Total $ 420,000 $ 35,000 You will examine the variances for the budget you completed in Module 3. Working off the budget you prepared for Netflix for 20XX, you will evaluate the variance for the 'actual' activity that occurred each month. You will use the attached worksheet to provide the dollar and percentage difference of budget vs. actual. Remember, your budget total should have been $480,270. However, the actual budget came in at $449,816. The following are the assumptions for the Budget from Module 3. Wages • There are 5 members in the department: One Director that gets paid $130,000/year. One Supervisor that gets paid $95,000/year. Three Junior Staff members that get paid $65,000/year each. Miscellaneous • The company always allocates $50 per month for miscellaneous expenses from January - October and then $40 per month for November - December. Travel • There are three conferences in 20XX and you will be sending 2 employees to each conference. • The first two conferences cost $3,000 per person and the third conference is $5,000 per person. • The conferences will be held in February, May, and September Supplies • Each person in the department spends $24 per month each for supplies Training • The entire department will be attending a training seminar in April and the cost is $5,000 per person. Meals During the training session for the entire department, each person will receive $250 per day for meals and the training session is for three days. Meals Meals will be paid for travel to the conferences mentioned above. Each person will receive $250 per day and each conference is five days. TASK: Perform a variance review of the yearly budget created for Netflix. You will compare the budgeted dollar amounts with the actual dollars spent throughout the year. Analyze the months and evaluate why each month or category was underbudget or overbudget. Utilize the Excel spreadsheet to complete your evaluation/review of the budget. January has already been done for you. You must fill out the variances for EVERY month. $ Variance Analysis Calculation: % Variance Analysis Calculation: Actual minus Budget $$ Variance / Budget Variance Analysis January February March April Budget Actual $$ Var % Var Budget Actual $$ Var % Var Budget Actual $$ Var % Var Budget Actual $$ Var % Var Wages 35,000 35,000 $ 0% 35,000 35,000 35,000 35,000 35,000 29,583 Travel 0 0 $ 6000 0 6000 0 0 Meals 0 0 $ 2500 0 1500 Training 0 0 0 0 3750 25000 0 20000 Supplies 120 100 Miscellaneous 50 OS $ 35,170 $ 35,100 $ (20.00) (50.00) (70) -17% -100% 120 50 120 100 100 50 120 50 0 0 -0.2% $ 43,670 $ 35,100 $ 0.0% $ 35,170 $ 42,600 $ 0.0% $ 63,920 $ 49,583 $ Meals Training Supplies Miscellaneous May Budget Actual $$ Var Wages 35,000 29,583 Travel 6000 0 2500 0 0 0 120 50 50 50 % Var $ 43,670 $ 29,683 $ 0.0% $ 35,170 $ 35,100 Wages Travel Budget Actual 35,000 35,000 0 0 0 0 0 0 120 50 500 0 0.0% $ 35,170 $ 35,500 $ September November Budget Actual $ Var % Var Budget Actual Budget Actual $ Var % Var 35,000 35,000 35,000 35,000 35,000 35,000 10,000 10000 0 0 0 0 Meals 2500 2000 0 0 0 0 Training 0 0 0 0 0 0 Supplies 120 120 0 120 0 Miscellaneous 50 50 50 40 0 $ 47,670 $ 47,050 $ 0.0% $ 35,170 $ 35,000 $ 0.0% $ 35,160 $ 35,000 $ June Budget Actual $$ Var % Var 35,000 35,000 0 0 0 0 0 0 120 50 100 0 July $ Var % Var August Budget Actual $ Var % Var 35,000 35,000 0 0 0 0 0 0 120 0 50 0 0.0% $ 35,170 $ 35,000 $ October $ Var % Var December Budget 35,000 Actual $ Var % Var 35,000 0 0 0 0 0 0 120 50 40 50 0.0% $ 35,160 $ 35,100 $ Total - Budget Total - Actual $ Var % Var Wages $ 420,000 $ 409,166 Travel S 22,000 $ 16,000 Meals S 11,250 S 3,500 Training S 25,000 $ 20,000 Supplies S 1,440 $ 900 Miscellaneous $ 580 $ 250 $ 480,270 $ 449,816 $ 0.0% 0.0% 0.0% 0.0% Note: Microsoft Excel shows the #DIV/0! error when a number is divided by zero (0). It happens when you enter a simple formula like =5/0, or when a formula refers to a cell that has 0 or is blank. You can remove all #DIV/0! errors and leave the cell blank. This indicates that no variance has occured. Name of Department: Budget 20XX Budget Allocated to Department: Cybersecurity $ 480,270 Netflix Yearly Budget Budget Preparer: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Wages 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 $ 420,000 Travel 6,000 6,000 10,000 $ 22,000 Meals 2,500 3,750 2,500 2,500 $ 11,250 Training 25,000 $ 25,000 Supplies Miscellaneous 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 50 120 40 120 $ 1,440 $ 35,170 $ 43,670 $ 35,170 $ 63,920 $ 43,670 $ 35,170 $ 35,170 $ 35,170 $ 47,670 $ 35,170 $ 35,160 $ 40 $ 35,160 $ 580 480,270 5 Assumptions Wages Each department has 1 Director, 1 Supervisor, and 3 junior people Travel There are 3 conferences in 20XX and you want to send 2 people. The cost for each conference the first 2 conferences is $3,000 per person and the final conference is $5,000 per person. The conferences will be held in February, May, and September Meals Meals will be paid for the travel to the 3 conferences and each person will receive $250 per day and the conference is 5 days. Training The entire department will be attending a training seminar in April and the cost per employee is $5,000. Meals In addition to the training, meals will have to be paid for the training seminar in April. Each person will receive $250 per day and the training is 3 days. Supplies Each person in the department spends $24/month in supplies Misc. The company always allocates $50 per month for miscellaneous expenses from Jan - Oct and then $40 per month in Nov - Dec : Salaries Monthly Expense Director $ 130,000 $ 130,000 Supervisor $ 95,000 $ 95,000 Junior $ 65,000 ea. $ 195,000 Total $ 420,000 $ 35,000
Expert Answer:
Related Book For
Financial Analysis with Microsoft Excel
ISBN: 978-1285432274
7th edition
Authors: Timothy R. Mayes, Todd M. Shank
Posted Date:
Students also viewed these accounting questions
-
You will be creating a budget for one of the departments on Netflixs income statement under Technology and Development for 2021 The Actual for Technology and Development expenses were 1,829,600. This...
-
You were recently hired to improve the financial condition of Idaho Springs Hardware, a small chain of three hardware stores in Colorado. On your first day the owner, Chuck Vitaska, told you that the...
-
Given the following points: 2,4,10,12,3,20,30,11,25. Assume k = 3, and that we randomly pick the initial means 1=2, 2=4 and 3=6. Show the clusters obtained using K-means algorithm after one...
-
The consolidated statement of stockholders' equity for Jackson Electronics, Inc., a manufacturer of a broad line of electrical components, is presented below. This statement of stockholders' equity...
-
In Exercises 312, show that the set is infinite by placing it in a one-to-one correspondence with a proper subset of itself. Be sure to show the pairing of the general terms in the sets. 1111 2, 3,...
-
Meghann Patrick is a former employee of Altria Group Distribution Company. After her employment was terminated, Patrick sued Altria and a supervisor at Altria, alleging employment-related claims...
-
Sherpers Boards sells a snowboard, Xpert that is popular with snowboard enthusiasts. Information relating to Sherpers purchases of Xpert snowboards during September is shown below. During the same...
-
Suppose one side of your home was damaged in a storm. To fix it you need to put new siding on the damaged exterior wall. The damaged wall measures 30.2 feet in length and 22.7 feet in width. The...
-
Tesco PLC, is a British multinational groceries and general merchandise retailer with headquarters in Welwyn Garden City, Hertfordshire, England and United Kingdom. It is the third-largest retailer...
-
Sophia operates her own accounting practice and is looking to hire two entrylevel accountants. A high-productivity worker will generate $90,000 in revenue per year and a low-productivity worker will...
-
Real GDP per person falls during a devastating recession, which in turn causes consumption to fall by 5%. Would giving everyone 5% more income during the recession lead consumption to go up 5%? Why...
-
When you rent an apartment or house, most landlords will require that you pay a refundable security deposit. Why do you think landlords do this? Relate their actions to possible problems caused by...
-
What do economists mean when they say that business cycles are not cycles?
-
For each of the following, identify which inputs into the production function changed and their effects on economic growth. a. The government passes a new program that encourages more employers to...
-
A piston-cylinder system contains a gas that expands under a constant pressure of 57 kPa. If the piston is displaced 0.3 m during the process, and the piston diameter is 0.6 m, what is the work done...
-
Write a paper detailing a geographic information system (GIS) of your own design that would utilize data in an original manner.
-
TRM Consulting Services currently has the following capital structure: New debt would mature on June 30, 2040, have a coupon rate of 8%, and would be sold for their par value of $1,000. The bonds pay...
-
The Salida Salt Company is considering making a bid to supply the highway department with rock salt to drop on roads in the county during the winter. The contract will guarantee a minimum of 25,000...
-
The Miracle Clean Company has some new products that it expects to lead to high growth in the near future. It has given analysts the following forecasts for the next three years: The firm's debt has...
-
As a security analyst covering the French market, youve identified the following factors and factor sensitivities for Elf Acquitaine (Elf): Elfs functional currency is the euro. Factors and factor...
-
Using the data shown in Figure 2.11 which anatomical site of the corn on the foot was the least frequently reported patients in the corn plaster group? The least frequently reported anatomical site...
-
The regional directors of a major investment bank are discussing investment strategies for their respective countries. a. As director of North American investments, describe to your foreign...
Study smarter with the SolutionInn App