Budget Assignment -
Instructions: Please read these instructions carefully and fully. Might be best to print this out and have it nearby as you complete the exercise.
Complete the 2018 budget as requested using the attached excel model template. You must create the following budgets:
- Sales Budget - Quarterly
- Production Budget - Quarterly
- Materials Budget - Quarterly
- Labor Budget - Quarterly
- Manufacturing Overhead Budget - Quarterly
- Administrative and Selling Budget - Quarterly
- Income Statement - Annual
All grey shaded cells must be formulas, except for the assumptions as given below. Assumptions should be input in blue shaded cell, mostly in column C in the appropriate row. If you hard code numbers including totals you will not get credit. I have already formatted the cells so you should only have to input numbers, no $ signs, no commas, no "/hour" or "/unit" etc. similar to the exercise we did in class.
The grading points are included in red in column J for each section. Total points possible are 20 and will replace the last two quizzes we had assigned.
If you would like to leave notes or comments, use Column I, next to the applicable row.
Synopsis
Camilla has just recently inherited her grandfather's Ferrari factory. She has no accounting background, and at the young age of only 24 ½ , she has requested your expert help in creating a budget for next year (2018). The following information was obtained through an interview with her and her top management.
Sales
- Expected Sales (units): Management expect to sell 100 cars the first quarter with a 10% increase each succeeding quarter. Hint: since you can't sell partial cars, use the "roundup" function in excel to round to next full number of units to sell in subsequent quarters. The function wizard in excel can help you write the formula. =roundup((A*B),0)
- Expected Sales price = $325,000
Production
- Since Ferrari cars are generally pre-ordered, each quarter the company manufactures the number of cars expected to be sold in the following quarter. Ferrari does not maintain any inventory. Production for the fourth quarter will be equal to expected sales in the first quarter 2019 or 140 units.
Materials
- Ferrari maintains an ending inventory of raw materials equal to 10% of the next quarter's production requirements.
- The manufacture of each car requires 2000 pounds of raw materials, and the expected cost per pound is $50/pound.
- Assume that the desired ending direct materials amount is 28,000 pounds for the fourth quarter of 2018.
Labor
- Direct labor hours are determined from the production budget. At Ferrari, much is done by robots, however the final touches require twenty hours of direct labor to produce each car. The anticipated hourly wage rate is $25.
Manufacturing overhead
Ferrari expects variable costs to fluctuate with production volume on the basis of the following rates per direct labor hour:
- indirect materials $2,500
- indirect labor $1,000
- utilities $500
- maintenance $250.
Ferrari expects Fixed Manufacturing Costs to be as follows (quarterly):
- Supervisory salaries $50,000
- Depreciation $21,250
- Property taxes $40,000
- Maintenance $15,000
Selling and Administrative
- Variable expense rates per unit of sales are sales commissions $5,000 and freight-out $2,500.
Ferrari's fixed selling and administrative costs are as follows (quarterly):
- Camilla's Salary and Bonus $2,500,000
- Advertising $1,000,000
- Sales salaries $500,000
- Office salaries $600,000
- Depreciation $1,000,000
- Property Taxes and Insurance $1,500,000
Other
- Interest Expense for 2018 $100,000
- Taxes are computed at 40% of pretax income
Hints: The purpose of this exercise is to get you engaged in preparing a budget, while learning some much needed excel skills. As accountants, Excel has become a primary tool for much of our work, despite the various industry systems we rely on, we use excel heavily in reconciliation work, budgeting, and other functions. To that end, I am providing the following select answers to help you validate your work before you submit it. If your numbers don't match the below items, double check your formulas and your work.
- Total Sales $151,125,000
- Total Manufacturing overhead $43,430,000
- Cost of Goods sold $186,500 per car
- Total Selling and Administrative $31,887,500
- Net Income $19,449,000
Hint on Cost of goods sold: To compute the cost of goods sold per unit, add the cost per unit for materials, labor and manufacturing. Reference the completed in class exercise for further help.
Attachment:- Assignment Files.rar