Need to fill Excel Spreadsheet in 5 hours.
igecunil
BudgetProjectTEMPLATE-1.xlsxSheet1
| Requirement #1 | |||||||||
| Sales Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | 1st Qtr 2019 | 2nd Qtr 2019 | ||
| Unit sales | |||||||||
| Multiply by: Selling Price | |||||||||
| =Total Sales Revenue | |||||||||
| Requirement #2 | |||||||||
| Production Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | 1st Qtr 2019 | 2nd Qtr 2019 | ||
| Unit sales | |||||||||
| + Desired ending inventory | *Be careful, with ending and beginning inventories you can not sum across all of the quarters to get an annual total. Think about the entire year and where beginning and ending inventory amounts come from (Jan. 1st and Dec. 31st). | ||||||||
| -Beginning inventory | *Be careful, with ending and beginning inventories you can not sum across all of the quarters to get an annual total. Think about the entire year and where beginning and ending inventory amounts come from (Jan. 1st and Dec. 31st). | ||||||||
| =Units to produce | |||||||||
| Requirement #3 | |||||||||
| Direct Materials Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | 1st Qtr 2019 | |||
| Units to be produced | |||||||||
| Multiply by: Quantity of DM needed per unit | |||||||||
| =Quantity of DM needed for production | |||||||||
| +Desired ending inventory of DM | *Be careful, with ending and beginning inventories you can not sum across all of the quarters to get an annual total. Think about the entire year and where beginning and ending inventory amounts come from (Jan. 1st and Dec. 31st). | ||||||||
| -Beginning inventory of DM | *Be careful, with ending and beginning inventories you can not sum across all of the quarters to get an annual total. Think about the entire year and where beginning and ending inventory amounts come from (Jan. 1st and Dec. 31st). | ||||||||
| =Quantity of DM to purchase | |||||||||
| Multiply by: Cost per unit | |||||||||
| =Total cost of DM purchases | |||||||||
| Requirement #4 | |||||||||
| Direct Labor Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | ||||
| Units to be produced | |||||||||
| Multiply by: DL hrs per unit | |||||||||
| Multiply by: DL rate | |||||||||
| =Total Direct Labor Budget | |||||||||
| Requirement #5 | |||||||||
| Overhead Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | ||||
| Units to produce | |||||||||
| Multiply by: variable overhead rate | |||||||||
| =Variable overhead | |||||||||
| +Fixed Overhead | |||||||||
| =Total Overhead Budget | |||||||||
| Requirement #6 | |||||||||
| Operating Expenses Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | ||||
| Unit sales | *Remember Op. Exp. are based on units sales!!!! | ||||||||
| Multiply by: variable op. exp. rate | |||||||||
| =Variable op exp. | |||||||||
| + Fixed op. exp | |||||||||
| =Total op. exp. Budget | |||||||||
| Requirement #7 | Budgeted Income Statement for 2018 | Budgeted manufacturing cost per unit | |||||||
| Sales | 2018 SALES TOTAL (cell "G6") | DM per unit | Cost per direct matieral X DM per unit (=C20*C25) | ||||||
| -CGS | Budgeted manufacturing cost per unit (cell "I59") X unit sales (cell "G4") | +DL per unit | Labor rate X DL hours per unit (=C32*C33) | ||||||
| Gross profit | Sales - CGS (=B55+B56) | +VOH per unit | VOH rate based on units produced | ||||||
| -Op. Exp. | (cell "G52") | +FOH per unit | FOH/unit=total fixed overhead for the year divided by the year’s budgeted production in units. (=G42/G39) | ||||||
| Net Income | Gross Profit - Op. Exp. | =Total per unit cost | |||||||
| Requirement #8 | |||||||||
| Cash Budget | |||||||||
| 4th Qtr 2017 | 1st Qtr 2018 | 2nd Qtr 2018 | 3rd Qtr 2018 | 4th Qtr 2018 | 2018 Total | ||||
| Beg. Cash | *The ending cash balance of one quarter becomes the beginning cash balance the following quarter. You can NOT sum across here. The beginning cash balance for 2018 comes from 1st QTR 2018. | ||||||||
| +Sales collected in current QTR | |||||||||
| +Sales collected from prior QTR | 1st Qtr 2018 Sales collected from prior QTR = Accounts Receivable balance as of Dec. 31, 2017 | ||||||||
| -DM Current QTR | |||||||||
| -DM Prior QTR | 1st Qtr 2018 DM Purchases from prior QTR = Accounts Payable balance as of Dec. 31, 2017 | ||||||||
| -DL | |||||||||
| -OH *** | ***Note, make sure the OH total entered here does not include depreciation. That is a non-cash expense and does not cash to decrease. | ||||||||
| -Op. Exp. *** | ***Note, make sure the Op. Exp total entered here does not include depreciation. That is a non-cash expense and does not cash to decrease. | ||||||||
| -Equipment | |||||||||
| Ending Cash | |||||||||
| Requirement #9 | What if 4th QTR Sales were budgeted at 75,000 units what would net income be for 2018? | ||||||||
| NET INCOME if 4th QTR Sales = 75,000 units | |||||||||
| ANSWER: ______________________ | Note, fill in 75,000 in cell "F4" and record the amount of net income found in (cell "B59"). | ||||||||
| Requirement #10 What if 4th QTR Sales were budgeted at 75,000 units what would be the ending cash balance at the end of the 4th quarter? | |||||||||
| Cash Balance if 4th QTR Sales = 75,000 units | |||||||||
| ANSWER: ____________________ | Note, fill in 75,000 in cell "F4" and record the amount of ending cash found in (cell "F73"), then go back and change 4th QTR sales units "F4" back to 90,000. | ||||||||
1
2
3
4
5
6
A
B
Requirement #1
4th Qtr 2017
Unit sales
Multiply by:
Selling Price
=Total Sales
Revenue
Sales Budget
