tlc0036 P6
xlsx
keyboard_arrow_up
School
West Virginia University *
*We aren’t endorsed by this school
Course
350
Subject
Industrial Engineering
Date
Feb 20, 2024
Type
xlsx
Pages
7
Uploaded by taylorcollins22
WVU Username:
tlc0036
Your WVU Username is your login name (Mix ID)
You must enter your correct WVU Username to avoid a 0 on the project.
Save this file in EXCEL FORMAT on your PC renaming it to be your WVU Username.
Do not change rows or colums on this spreadsheet. Input areas are in yellow.
Formulas and cell references are REQUIRED for Requirements 1-4 and advised for 5. Always use the SUM function Whenever multiplying, you must use the ROUND function; example: May Sales in units, cell D16, type =ROUND(=C
You will prepare a static master budget, pro-forma financial statements and a flexible budget/variance analysis for
On April 1, the business begins by issuing stock for $100,000 and purchasing $60,000 of equipment (steamer carts)
Requirement 1: Complete the Sales Budget for April - June and the quarter in total.
Each month, Sales are expected to increase by
10%
Sales Budget
April
May
June
Total
Sales in units
22,400 24,640 27,104 74,144 Price per unit
$ 5 $ 5 $ 5 $ 5 Total sales dollars
$ 112,000 $ 123,200 $ 135,520 $ 370,720 Requirement 2: Complete the Cash Collections Budget for April - June and the quarter in total.
The company expects to collect 90% of the sales in the month of the sale and the remainder in the following mont
Cash Collections Budget
April
May
June
Total
April sales collections
$ 100,800 $ 11,200 $ - $ 112,000 May sales collections
- 110,880 12,320 $ 123,200 June sales collections
- - 121,968 $ 121,968 Total cash collections
$ 100,800 $ 122,080 $ 134,288 $ 357,168 Accounts Receivable $ 11,200 $ 12,320 $ 13,552 Requirment 3: Complete the Purchase Budget for April - June and the quarter in total. The desired ending inventory should equal 10% of the current month's sales.
Purchase Budget
April
May
June
Sales in units
22,400 24,640 27,104 Desired ending inventory
2,240 2,464 2,710 Total required
24,640 27,104 29,814 Beginning inventory
- 2,240 2,464 Total units to purchase
24,640 27,104 29,814 Cost per unit
$ 2.00 $ 2.00 $ 2.00 Purchases in dollars
$ 49,280 $ 54,208 $ 59,629 Requirement 4: Complete the Cash Payments Budget for April - June and the quarter in total.
The company expects to pay 60% of the purchases in the month of the purchase and the remainder in the followin
Payments of Purchases
April
May
June
Total
April payments
$ 29,568 $ - $ - $ 29,568 May payments
$ 19,712 $ 32,525 $ - $ 52,237 June payments
$ - $ 21,683 $ 35,777 $ 57,460 Module 6 Project - Budgeting (50 points)
Total payments of purchases
$ 49,280 $ 54,208 $ 35,777 $ 139,265 Accounts Payable
$ 19,712 $ 21,683 $ 23,852 Requirement 5: Complete the Pro-formas Contribution Margin Income Statement and Flexible Budget below for
In addition to the information above, the company incurs:
Salary costs of $3,000 per month and will be paid at the end of each.
Advertising costs of $500 per month and will be paid at the beginning of each month.
Depreciation of the equipment at a rate of $1,000 per month.
Enter costs as negative amounts and use the SUM function for subtotals.
Flexible Budget for the Quarter End
Budget
Master
Per Unit
Flexible
Actuals
Units:
74,144 81,558 81,558 Sales
$ 370,720 $ 5 $ 407,790 $ 400,378 Variable Costs:
Cost of Goods Sold
153,709 2 169,079 166,006 Contribution Margin
$ 217,011 $ 3 $ 238,711 Fixed Costs:
Salary Expense
(9,000)
(9,000)
Advertising Expense
(1,500)
(1,500)
Depreciation Expense
(3,000)
(3,000)
Total Fixed Costs
$ (13,500)
$ (13,500)
Operating Income
$ 203,511 $ 225,211 What is the variance for Advertising Expense given its actual is:
(1,620)
Cost of Goods Sold
Salary Expense
Advertising Expense
Depreciation Expense
Favorable
Unfavorable
Price was higher than budgeted
Price was lower than budgeted
Cost was higher than budgeted
Cost was lower than budgeted
116
108 l
tl
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
when adding cells.
C16*1.1,0)
r a business that sells hotdogs.
) with useful lives of 5 years.
Cell Links/Formulas:
April
May
June
=ROUND(C16*1.1,0)
=ROUND(D16*1.1,0)
=C16*C17
=D16*D17
=E16*E17
th.
April
May
June
=C18*90%
=C18*10%
=0
=0
=D18*90%
=D18*10%
=0
=0
=E18*90%
=C23
=SUM(D23:D24)
=SUM(E24:E25)
=10%*C18
=10%*D18
=10%*E18
Total
April
May
June
74,144 =C16
=D16
=E16
2,710 =(C16*10%)
=D16*10%
=E16*10%
76,854 =SUM(D32:D33)
=SUM(E32:E33)
=SUM(F32:F33)
- 76,854 =D34
=E34
=F34
$ 2.00 $ 153,709 =D36*D37
=E36*E37
=F36*F37
ng month.
April
May
June
=D38*60%
=0
=0
=D38*40%
=E38*60%
=0
=0
=E38*40%
=F38*60%
=SUM(C43:C45)
=SUM(D43:D45)
=SUM(E43:E45)
=D38*40%
=E38*40%
=F38*40%
r the quarter ended June 30.
ded June 30
Favorable or
Variance
Unfavorable
Possible Explanation
$ 7,412 Favorable
Price was higher than budgeted
(3,073) Unfavorable
Price was lower than budgeted
(120) Unfavorable
Cost was higher than budgeted
Total
=SUM(C16:E16)
=SUM(C18:E18)
Total
=SUM(C23:E23)
=SUM(C24:E24)
=SUM(C25:E25)
=SUM(F23:F25)
Total
=SUM(G32:G33)
=G34
=G36*G37
Total
=SUM(C43:E43)
=SUM(C44:E44)
=SUM(C45:E45)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
=SUM(F43:F45)
Cell Links/Formulas:
Master
Per Unit
Flexible
Variance
=F18
=C59/C58
=E58*D59
=E59-F59
=G38
=G38/C58
=D61*E58
=F61-E61
=C59-C61
=D59-D61
=D62*E58
n/a