Question

In: Accounting

Using the below information open an excel file and create a Sales Budget, a Cash Receipt...

Using the below information open an excel file and create a Sales Budget, a Cash Receipt Schedule and a Production Budget for Fiwrt for the months of Oct, Nov, and Dec. Make sure to use proper format including dollar signs and headers - this will count in the grade. Upload the excel file as your response.

Fiwrt Corporation manufactures and sells stainless steel coffee mugs. Expected mug sales (in units) are expected to be as follows: September – 32,000, October – 30,000, November – 36,000, December – 34,000, and January 35,000.

Selling Price is expected to be $8 per mug. 40% of sales are on account. All sales on account are collected in the following month. Fiwrt likes to maintain a finished goods inventory equal to 30% of the next month's estimated sales.

Solutions

Expert Solution

Solution:

Sales Budget - Fiwrt
Particulars October November December Total
Sales units 30000 36000 34000 100000
Selling price per unit $8.00 $8.00 $8.00 $8.00
Projected Sales Revenue $240,000.00 $288,000.00 $272,000.00 $800,000.00
Schedule of expected cash collections
Particulars October November December Total
Cash sales (60%) $144,000.00 $172,800.00 $163,200.00 $480,000.00
Collction of sales on account $102,400.00 $96,000.00 $115,200.00 $313,600.00
Expected cash collections $246,400.00 $268,800.00 $278,400.00 $793,600.00
Production Budget - Fiwrt
Particulars October November December Total
Sales units 30000 36000 34000 100000
Add: Desired ending inventory (30% of next month sales) 10800 10200 10500 10500
Less: Opening Inventory 9000 10800 10200 9000
Budgeted Production units 31800 35400 34300 101500

ekkarill92 answered 4 months ago
Next > < Previous

Related Solutions

Prepare a cash budget (using info from the Sales Budget and purchases budget shown below) for...
Prepare a cash budget (using info from the Sales Budget and purchases budget shown below) for January through March and for the first quarter in total. The company maintains a minimum cash balance of $50,000.00, and this was the balance in the cash account on January 1. Past experience shows that 30% of sales are collected in the month of the sale, and 70% in the month following the sale. Labor cost is $15 per unit. Other expenses include $37,000...
Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below...
Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below Calculate the Net Working Capital and the Net Operating Working Capital for both years Perform vertical analysis on both statements, for both years Perform Horizontal analysis on both statements for the most current year What was the Net Operating Profit After Taxes for both years? 2016 Accounts Receivable 400 Sales 5000 Accounts Payable 650 Inventory 1400 COGS 3000 Common Stock 1500 Cash 1600 Oper....
Create a program that: Creates a sales receipt, displays the receipt entries and totals, and saves...
Create a program that: Creates a sales receipt, displays the receipt entries and totals, and saves the receipt entries to a file Prompt the user to enter the Item Name Item Quantity Item Price Display the item name, the quantity, and item price, and the extended price (Item Quantity multiplied by Item Price) after the entry is made Save the item name, quantity, item price, and extended price to a file When you create the file, prompt the user for...
Create a Python program that: Creates a sales receipt, displays the receipt entries and totals, and...
Create a Python program that: Creates a sales receipt, displays the receipt entries and totals, and saves the receipt entries to a file Prompt the user to enter the Item Name Item Quantity Item Price Display the item name, the quantity, and item price, and the extended price (Item Quantity multiplied by Item Price) after the entry is made Save the item name, quantity, item price, and extended price to a file When you create the file, prompt the user...
Create a python program that: Creates a sales receipt, displays the receipt entries and totals, and...
Create a python program that: Creates a sales receipt, displays the receipt entries and totals, and saves the receipt entries to a file Prompt the user to enter the Item Name Item Quantity Item Price Display the item name, the quantity, and item price, and the extended price (Item Quantity multiplied by Item Price) after the entry is made Save the item name, quantity, item price, and extended price to a file When you create the file, prompt the user...
Using the Vehicle Ratings Excel file, create formulas using nested IF, AND, and OR functions to...
Using the Vehicle Ratings Excel file, create formulas using nested IF, AND, and OR functions to implement the three rating schemes described on the spreadsheet. Rating 1 If the vehicle has A/C and a sunroof or it is newer than 2013, then YES, otherwise NO. Rating 2 If the vehicle is Red and does not have high miles, then YES, otherwise if it is a Ford or Chevy, MAYBE, otherwise NO. Rating 3 If the vehicle is older than 2013...
Create an Excel file to show the budgets and calculations for the questions below. Assume no...
Create an Excel file to show the budgets and calculations for the questions below. Assume no beginning or ending inventory. Ignore taxes. Analysis or explanations can be included in the Excel cells or in a textbox. Use contribution margin income statement formatting. HammerTime is preparing their 2019 budget. They want to look at static budgets and flexible budgets to determine which is best for them. They estimate sales/production will be between 2,000,000 and 4,000,000 boxes of nails per month. They...
The data on a loan has been collected in the Microsoft Excel Online file below. Open...
The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Amortization schedule Loan amount to be repaid (PV) $39,000.00 Interest rate (r) 11.00% Length of loan (in years) 3 a. Setting up amortization table Formula Calculation of loan payment #N/A Year Beginning Balance Payment Interest 1 2 3 b. Calculating % of Payment Representing Interest and Principal for Each Year Year Payment...
The data on a loan has been collected in the Microsoft Excel Online file below. Open...
The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. a. Complete an amortization schedule for a $22,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 8% compounded annually. Round all answers to the nearest cent. Beginning Repayment Ending Year Balance Payment Interest of Principal Balance 1 $   $  ...
ACCOUNTING: PREPARE THE BUDGET PROCESS USING EXCEL a) Prepare a sales budget in units and dollars...
ACCOUNTING: PREPARE THE BUDGET PROCESS USING EXCEL a) Prepare a sales budget in units and dollars by quarter and for the year. b) Prepare a production budget in units by quarter and for the year. c) Prepare a materials purchases budget in feet and dollars by quarter and for the year. d) Prepare a direct labor budget in hours and dollars by quarter and for the year. e) Prepare an overhead budget by quarter and for the year. Show depreciation...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT