ACCT 346 Managerial Accounting Homework- DeVry University
Part I
Overview
Introduction
Our exercise simulates the types of decisions all managers will make in their planning and controlling functions. While there are formulas and templates to facilitate the process, ultimately it is the manager's judgement that interprets the data. Effective decision-making often relies on doing the correct analysis in order to support instinct.
In this course project, we will complete these inter-related activities:
I. Develop an operating budget using the contribution margin format.
II. Prepare a cash budget based on projected cash receipts, disbursements, investing and financing activities.
III. Conduct variance analysis between the budget and actual results
IV. Decide whether or not to outsource production
V. Conduct a discounted cash flow analysis in order to select the best project investment.
Background
You own a frozen pizza manufacturing and distribution business. Our premium product, "Buzz all natural pizza" appeals to the adult learners at DeVry University. A marketing survey revealed a time-starved segment that is always in need of energy at the end of the day or week in order to do their schoolwork. Also being poor time-managers this homogenous group requires a more direct form of intervention, in order to reach peak academic performance. In order to meet this need, your pizza has caffeinated tomato sauce.
The Homework
Obtain the course project spreadsheet located in the class files. You are responsible for entering data in each of the worksheets (see tabs at the bottom). Each worksheet represents a deliverable. Data entry is required in the yellow highlighted cells. After you have input into the prescribed formulas, you are to explain the calculated answers.
Each worksheet is due on a different week.
Instructions
There are instructions in each of the five (5) worksheets. This is a summary of the requirements:
I. The Operating Budget
1) Input your forecast product mix, as provided by the Marketing department
2) Input the sales volume, as projected by your Field Sales managers.
3) Input the efficiency rate for your direct labor, as projected by the VP of Manufacturing
4) Input the sales commission, as provided by the VP of Sales and the department's fixed costs
5) Input the administrative fixed costs as provided by the CFO.
6) Calculate and comment on the breakeven point
7) Calculate the sales volume needed to reach a target profit and comment
II. The Cash Budget
1) If needed, copy the sales from the 1st Quarter operating budget
2) Using an aging of the collection period, as provided by the credit manager, forecast cash receipts by month.
3) Calculate the cash disbursements by month
4) Determine the timing of a capital expenditure
5) Determine the timing and amount of financing
III. Performance Evaluation - Variance Analysis
1) Given actual results and the operating budgeted rates, prepare a flexible budget for one month.
2) Explain deviations from plan.
IV. Incremental Analysis - Deciding whether to Outsource
1) Using the operating budget for the quarter, prepare the relevant costs for in-house production
2) Given avoidable costs, calculate whether the outsourcing decision will save costs in total.
V. Capital Investments - discounted cash flows
1) Input the cost of the project
2) Input the positive cash flows due to production cost savings
3) Decide on the "best" year in which to conduct major maintenance
4) Using Excel functions, given a "hurdle rate" calculate the project's Net Present Value (=NPV) and the Internal Rate of Return (=IRR)
5) Calculate the payback period and profitability index.
Part II
Background - The Business Plan
Marketing:
You have a segmentation analysis of the lifestyle variables for the adult learners market. The results support the need to invigorate and "refuel" with comfort food that is also nourishing. What better choice for adults than pizza? What will fill this need and differentiate "BUZZ-TIME PIZZA" is the caffeinated tomato sauce.
Operations:
You and a couple of friends will make, bake and deliver made-to-order pizzas, in two sizes. There is room in a garage for refrigeration, preparation tables, and a wood-fired oven. The sales manager will make all deliveries, in their vehicle.
Financing:
Clearly, this is a long-term investment. The three founders, who will each contribute an equal amount, have agreed not to draw a salary for three years. You have hired a sales manager and a part-time office manager.
Part 1: The first two deliverables involve preparingthe necessary financial budgets in order to satisfy creditors and control business operations.
The sales manager has provided the following sales estimates and price points for the first quarter of 2020. Input these values in the worksheet labelled "Operating Budget
January February March
Number of pizzas 40,000 42,500 45,000
Sales price for large $10.50
Sales price for medium $ 8.50
There is much debate as to the probable sales mix between the two sizes. Input a product mix of your choice (make sure these two add-up to 100%).
Direct labor consists of your chef, Pepe, who has agreed to a low wage as long as cigarette and bathroom breaks are included. DO NOT CHANGE THIS LABOR RATE
However, you must calculate the Chef's efficiency rate. Input how many minutes it will take to make one pizza from order to box.
Ingredient and material costs, based on quotes after an extensive request for proposal (RFP), are good for two years.DO NOT CHANGE THE DIRECT MATERIAL RATES
The only variable selling cost is a sales commission based on a percent of sales revenue. Input the percent you wish to pay your sale manager.
Selling expenses include a fixed component for the sales manager's salary and car allowance for any company business use of their personal auto. Input the monthly salary you wish to pay for compensation, including the car allowance
Input the monthly salary for your office manager.
Calculate the breakeven sales as well as the sales needed to make $200,000 in net income.
You are to look over this financial plan and make adjustments in the data you have input, in order to obtain the most reasonable and not excessively optimistic results.
The Cash Budget
Once you are satisfied with your operating budget, you must prepare a cash budget based on estimated cash flows from collections on customer receivables and cash payments.
Ensure that the sales revenues for January - March is the same as that on the operating budget.
Using the aging of cash collections, calculate total cash receipts for January - March.
For each month's cash disbursements, you are to assume that half of the direct material payments are from the current month's purchases and half are from the prior month's purchases.
All selling and administrative expenses are paid in the month incurred.
Calculate the forecast direct material cash payments by month.
You are to decide in which month to investa significant amount ofcash in a facility expansion. You are also to decide on the timing and amount of financing that will cover any month in which you do not meet the compensating cash balance requirement.
At the end of the first quarter, management desires to have any new debt for this expansion paid-off.
Part 2: Identify and explain variations from the budget and make a decision for continuous improvement.
Performance Evaluation
The data for the February master budget columns should come over from the operating budget worksheet.
Verify that the Master budgetNet Income is the same as that reported in sheet #1.
Complete a flexible budget for February, showing what net income should have been using the operating budget revenue rate, variable expenses' rates and fixed costs.
Actual operating results for the month are provided. DO NOT CHANGE THIS DATA
Calculate the variances between the flexible budget and actual results. As being either F for favorable or (U) for unfavorable.
Determine how much of the Net income variance was due to volume and how much was rate-related.
How would you evaluate the actual results? What steps would you take to further investigate and possibly adjust your budget for the rest of the year?
Incremental Analysis - Do we outsource?
Given the relevant costs from the operating budget, prepare a worksheet comparing the relevant data to a vendor's price quote for doing the production currently done in-house.
Provide an opinion as to whether this business deal is acceptable. Are there any non-financial considerations?
Part 3: Project Justification
It is now time to prepare a capital budget. The idea is to prepare a list of acceptable projects in which to spend in the first quarter in order to increase the value of operations. Recall that the cash budget included funds for plant expansion.
The estimatedfuture cash flows arising from sales, net of operating costs are given. Thehurdle rate represents the interest rate to borrow these funds plus a 2% risk factor. Use this percent in order to discount the cash flows.
The project cost and the salvage value, as well, have already been input into the worksheets.
Input these cash flows:
Sales revenues: Years 1 - 5 $135,000 per year; Years 6-10 $200,000 per year
Operating costs: Years 1-5 $ 95,000 per year; Years 6-10 $ 135,000 per year
Decide in which year to spend the $85,000 of major maintenance
Complete the five measures at the bottom of the worksheet.
Format your homework according to the following formatting requirements:
(1) The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
(2) The response also includes a cover page containing the title of the homework, the student's name, the course title, and the date. The cover page is not included in the required page length.
(3) Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Download:- Project-Worksheet.rar